How to Select a MySQL Database to Use with Pdo in PHP

How do I select a MySQL database to use with PDO in PHP?

Typically you would specify the database in the DSN when you connect. But if you're creating a new database, obviously you can't specify that database the DSN before you create it.

You can change your default database with the USE statement:

$dbh = new PDO("mysql:host=...;dbname=mysql", ...);

$dbh->query("create database newdatabase");

$dbh->query("use newdatabase");

Subsequent CREATE TABLE statements will be created in your newdatabase.

---

Re comment from @Mike:

When you switch databases like that it appears to force PDO to emulate prepared statements. Setting PDO::ATTR_EMULATE_PREPARES to false and then trying to use another database will fail.

I just did some tests and I don't see that happening. Changing the database only happens on the server, and it does not change anything about PDO's configuration in the client. Here's an example:

<?php

// connect to database
try {
    $pdo = new PDO('mysql:host=huey;dbname=test', 'root', 'root');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
} catch(PDOException $err) {
    die($err->getMessage());
}

$stmt = $pdo->prepare("select * from foo WHERE i = :i");
$result = $stmt->execute(array("i"=>123));
print_r($stmt->fetchAll(PDO::FETCH_ASSOC));

$pdo->exec("use test2");

$stmt = $pdo->prepare("select * from foo2 WHERE i = :i AND i = :i");
$result = $stmt->execute(array("i"=>456));
print_r($stmt->fetchAll(PDO::FETCH_ASSOC));

If what you're saying is true, then this should work without error. PDO can use a given named parameter more than once only if PDO::ATTR_EMULATE_PREPARES is true. So if you're saying that this attribute is set to true as a side effect of changing databases, then it should work.

But it doesn't work -- it gets an error "Invalid parameter number" which indicates that non-emulated prepared statements remains in effect.

PDO, $_GET, and SELECTing from MySQL Database

Try this;

connection.php

try{
$db = new PDO('mysql:host=localhost;dbname=database_name;charset=utf8mb4', 'database_username', 'database_password');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
}
catch (PDOException $ex){
echo $ex->getMessage();return false;
}

function retrieve($query,$input) {
 global $db;
 $stmt = $db->prepare($query);
 $stmt->execute($input);
 $stmt->setFetchMode(PDO::FETCH_OBJ);   
 return $stmt;
}

To retrieve data, call the retrieve() function

Retrieval page, say display.php

require 'connection.php';
$getid = $_GET["id"];
$result=retrieve("SELECT paste FROM pasteinfo WHERE id=?",array($getid));
$row=$result->fetch();
//To get paste column of that id
$paste=$row->paste;
echo $paste;

Select data from database with PDO

$stmt = $db->prepare("SELECT id FROM tablename");
try{
    $stmt->execute();
}catch(PDOException $err){
    //some logging function
}
//loop through each row
while($result=$stmt->fetch(PDO::FETCH_ASSOC)){
    //select column by key and use
    echo $result['id'];
} 

alternatively you could use fetchAll to select whole dataset into a variable. Then you wouldn't need to loop

Heres a useful resource for PDO:

http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers

select sql row using pdo with where statement

Assuming that you know how to connect database using PDO, here is how to select SQL with PDO.

$stmt = $db->prepare("select userID from tblUsers where logged = '1' AND ip = :ip");
$stmt->execute(array('ip' => $ip));
$listArray = $stmt->fetchAll();

Notice the :ip at the end of SELECT. If you don't use ? as a parameters, the prefix : is mandatory and the word after that should be the same as the key in the execute function.

EDIT

In case that the above code is inside the function and $db is outside the function, declare $db as global variable inside the function.

Switch between multiple database in PDO

There is not, you will need to create two PDO objects for the separate connections if you would like to use both at runtime.

Edit: Interesting point by @laz below (which I'm guessing is the cause of negative votes on my answer). I was thinking under the assumption that the databases were on separate servers tbh, in which case my answer stands.

PHP MYSQL PDO Database Connection

As u_mulder said in the comments,

your connect method will not return anything.

I updated it as below and made some change on your getData() method:

    public function connect() {
        $con = 'mysql:host=' . $this->dbHost . ';dbname=' . $this->dbName;
        $options = array(
            PDO::ATTR_PERSISTENT => true,
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
        );

        try {
            //$this->dbHandler = new PDO($con, $this->dbUser, $this->dbPass, $options);

            return  new PDO("mysql:host=".$this->dbHost.";dbname=".$this->dbName,$this->dbUser,$this->dbPass);
        } catch (PDOException $e) {
            $this->error = $e->getMessage();
            echo $this->error;
        }
    }
}

class View extends Data {

    public function getData($table){
        try {
            $sql="SELECT * FROM $table";
            $q = $this->connect()->query($sql) or die("failed!");

            while($r = $q->fetch(PDO::FETCH_ASSOC)){  $data[]=$r;  }
            return $data;

        }
        catch(PDOException $e)
        {
            echo 'Query failed'.$e->getMessage();
        }

    }

}

$view = new View;
$result = $view->getData('equipment');
print_r($result);

Although I prefer remove connect method and add a constructor in your Data class as below:

    public $this->conn;
    public function __construct(){

        $this->conn = new PDO("mysql:host=".$this->dbHost.";dbname=".$this->dbName,$this->dbUser,$this->dbPass);

    }

and then change my getData as below:

$q = $this->conn->query($sql) or die("failed!");

‌Because as ADyson said in the comments :

you shouldn't really be connecting again every time you run a query..

Selecting MySql table data into an array using PDO class?

Don't inject values into your SQL queries. Use parameter binding instead.

$q = $pdo->prepare('SELECT id, prek_name, prek_value FROM prekes WHERE customer_id = ?');
$q->execute([$pirkejas]); // if PHP < 5.4, use array($pirkejas)
$prekes = $q->fetchAll(PDO::FETCH_ASSOC);

Now $prekes will be an array of rows where each row is an associative array.

<?php foreach ($prekes as $row) : ?>
<tr>
    <td>
        <input name="prekes[<?= (int) $row['id'] ?>][pavadinimas]"
               value="<?= htmlspecialchars($row['prek_name']) ?>">
    </td>
    <td>
        <input name="prekes[<?= (int) $row['id'] ?>][kaina]"
               value="<?= htmlspecialchars($row['prek_value']) ?>">
    </td>
</tr>
<?php endforeach ?>

return one value from database with mysql php pdo

You could create a function for this and call that function each time you need a single value. For security reasons, avoid concatenating strings to form an SQL query. Instead, use prepared statements for the values and hardcode everything else in the SQL string. In order to get a certain column, just explicitly list it in your query. a fetchColumn() method also comes in handy for fetching a single value from the query

function getSingleValue($conn, $sql, $parameters)
{
    $q = $conn->prepare($sql);
    $q->execute($parameters);
    return $q->fetchColumn();
}

Then you can simply do:

$name = getSingleValue($conn, "SELECT name FROM login_users WHERE id=?", [$userid]); 

and it will get you the desired value.

So you need to create that function just once, but can reuse it for different queries.

This answer has been community edited addressing security concerns

displaying mysql select statement using PHP PDO

This issue you have relates to the scope of the "$getUsers" variable. Because the function does not return its value the HTML file cannot access this and the fetch() method is trying to be called on a undefined value, hence the "non-object" part within the error.

<?php
  /** Include the db file which should define a connection as so
  $db = new PDO('dblib:host=$hostname;dbname=$dbName', $user, $pass); etc
  **/
  include 'dbc.php';

  page_protect();

  function get_users($db) 
  {   
    $result = $db->query("SELECT employeeid, fullname FROM Persons order by fullname asc");
    return $result;
  }
  $users = get_users($db);

  /** $users->fetch() can now be used **/

?>

You should also consider some error checking to ensure that the returned db result is actually a PDO result or you may still encounter this issue.

---

Related Topics