Prepared Statements - Multiple Queries

Question

I'm just getting familiar with prepared statements. I have a query working, with a loop to bind the results into something I can work with easier.

Now, I'd like to add another query and I'm not sure of the correct syntax

/* Create a new mysqli object with database connection parameters */
$mysqli = new mysqli($hostname_db, $username_db, $password_db, $database_db);
if(mysqli_connect_errno()) {
  echo "Connection Failed: " . mysqli_connect_errno();
  exit();
}

/* Create a prepared statement */
if($stmt = $mysqli -> prepare("SELECT * FROM table WHERE id = ?")) {

    /* Bind parameters, s - string, b - blob, i - int, etc */
    $stmt -> bind_param("i", $rid);
    $stmt -> execute();

    /* USE loop to bind result and fetch */
    $meta = $stmt->result_metadata();
    while ($field = $meta->fetch_field()) {$parameters[] = &$row[$field->name];}
    call_user_func_array(array($stmt, 'bind_result'), $parameters);
    while ($stmt->fetch()) {
      foreach($row as $key => $val) { $x[$key] = $val;
      }
      $results[] = $x;
    }
    /* Close statement */
    $stmt -> close();

------------------>SHOULD I PUT ANOTHER QUERY HERE?


   }

------------------>OR SHOULD I PUT ANOTHER QUERY HERE INSTEAD?

   /* Close connection */
   $mysqli -> close();

----------------->Or, should I put it here

//END mysqli query method

I'm not sure of which of the three positions is the best place/best practice. I could get it done with trial and error, but I'd like to know what is the best practice and I can't seem to locate a clear answer.

Thanks in advance.

Thanks @eggyal, that worked. Do you want to answer the question so I can mark it answered? — Kevin, May 24 '13 at 00:04

Your Common Sense · Accepted Answer · 2013-05-24T04:14:14.723

1

Just put your statements one under another. Do not use close(), do not use mysqli prepared statements, do not collect 200:

$stmt = $pdo -> prepare("SELECT * FROM table WHERE id = ?");
$stmt -> execute(array($rid));
$results = $stmt -> fetchAll(); // for many rows

$stmt = $pdo -> prepare("SELECT id FROM table WHERE name = ?");
$stmt -> execute(array($name));
$id = $stmt -> fetchColumn(); // for single scalar value

$stmt = $pdo -> prepare("SELECT * FROM table WHERE id = ? LIMIT 1");
$stmt -> execute(array($rid));
$row = $stmt -> fetch(); // for single row

and so on

Or, with even more intelligent library, it could be even

// one single line to get your $results instead of screenful of code, mind you
$results = $db->getAll("SELECT * FROM table WHERE id = ?i",$rid);
// as well as others
$id      = $db->getOne("SELECT id FROM table WHERE name = ?s", $name);
$row     = $db->getRow("SELECT * FROM table WHERE id = ?i LIMIT 1",$rid)

Still want to stick with raw mysqli?

edited May 24 '13 at 04:14

answered May 24 '13 at 03:52

Your Common Sense

156,878
40
214
345

Ha. @YourCommonSense, you are slowly bringing me around. You answered [my question here](http://stackoverflow.com/questions/16557785/sql-injection-prevention-get-vars) and so I'm trying to learn and implement prepared statements, but I thought mysqli was the way to go. I see now you are strongly recommending pdo versus mysqli. Is it basically because it's cleaner and simpler? – Kevin May 24 '13 at 06:44
Saner I'd say. For some reason mysqli makes very basic things extremely complicated. However, with some effort you can make it work, if create a class to encapsulate all that messy code in. But PDO is surely way more usable out of the box. – Your Common Sense May 24 '13 at 07:11
I'm reading on PDO right now after learning mysqli all day. I see that with mysqli, when using 'bind_parameter', you can tag "i" for integer. Does PDO not have this comparable setting? – Kevin May 24 '13 at 07:13
It has, the very similar method called `bindValue()`. However, most of time you don't need such a separation, as mysql can sort all the types out. – Your Common Sense May 24 '13 at 07:15
So would you say the injection vulnerabilities are handled just by using the pdo. I don't have to worry about bindValue()? – Kevin May 24 '13 at 07:38
err... yes, as long as you are using prepared statements. but with pdo you can omit manual binding, by passing variables into execute() - PDO will bind them internally, all with string type. But, as I said before, mysql server can sort all the types itself. – Your Common Sense May 24 '13 at 07:42

Prepared Statements - Multiple Queries

1 Answers1