Converting to Prepared Statements - How to echo results of the query

Question

I'm converting a query to prepared statements for the first time and I'm having trouble figuring out how to extract the data...

Here's my code:

/* 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();
}

   if($stmt = $mysqli -> prepare("SELECT name FROM table WHERE id=?")) {
      /* Bind parameters, s - string, b - blob, i - int, etc */
      $stmt -> bind_param("i", $rid);
      $stmt -> execute();
      $stmt -> bind_result($result);
      $stmt -> fetch();
      $stmt -> close();
   }    
   $mysqli -> close();

In this example, I was able to display the results in the body of the page like this:

<?php echo $results; ?>

However, when I changed the query in the example above to grab all the fields, I couldn't figure out how to display it:

SELECT * FROM table WHERE id = ?

This did not work:

<?php echo $results['id'] ?>

What am I missing here? How would I display a random field name from that query?

THanks!!

score 0 · Answer 1 · answered May 23 '13 at 19:15

0

Bind a variable per-column in the query:

$stmt = $mysqli->prepare("SELECT foo, bar, baz ...");
$stmt->bind_result($foo, $bar, $baz);

answered May 23 '13 at 19:15

Marc B

356,200
43
426
500

Uhg! I was afraid it would be something like that. Here's my issue: The 'real' query is: select field1, field2, field3, up to maybe field 30 or 40. Is there an alternative? – Kevin May 23 '13 at 19:20

score 0 · Answer 2 · edited May 23 '17 at 11:50

Yes, it won't work this way as you choose wrong driver, which renders prepared statements quite unusable.

Here is the right code using the right driver:

/* Create a new PDO object with database connection parameters */
$dsn = "mysql:host=localhost;dbname=test;charset=utf8";
$opt = array(
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
);
$pdo = new PDO($dsn,'root','', $opt);    

/* Prepare a query, bind parameters and execute them */
$stmt = $pdo->prepare("SELECT name FROM table WHERE id=?");
$stmt -> execute(array($rid));
$row = $stmt -> fetch();

Now you can echo result the way you prefer, which is way more useful than one offered by mysqli

<?=$row['id'] ?>

Note the dramatically shortened code.

score 0 · Accepted Answer · edited May 23 '17 at 10:25

0

You have to think of storing columns one by one like this:

$stmt->execute();

$stmt->bind_result($col1, $col2);

or use code from this answer, which gives you an array of all columns without specyfing each other.

edited May 23 '17 at 10:25

Community

1
1

answered May 23 '13 at 19:21

Daniel Kmak

18,164
7
66
89

I know this answer was downvoted, but ultimately, the link he provided was what I was looking for, so I'm marking it as the answer. – Kevin May 23 '13 at 21:05
Thanks, however I love people who downvote for no reason or fear to comment and say what's wrong with my answer. Good luck and have a nice day! – Daniel Kmak May 23 '13 at 22:05

Converting to Prepared Statements - How to echo results of the query

3 Answers3