msyqli prepared statement not working right

Question

I'm converting this query into a prepared statement:

        $stmt = "SELECT * FROM `table` WHERE col1 ='". $var1. "' and col2 = '".$var2."' and col3 ='".$var3."'";
        $result = mysqli_query($db, $stmt);
        $item_row=mysqli_fetch_row($result);

with it's result I echo out item_row[1], item_row[2], etc. This works fine.

This is the prepared statement I've put together, but it's not working:

    $stmt = $db->prepare("SELECT * FROM `table` WHERE col1 = ? and col2 = ? and col3 =?");          
            $stmt->bind_param("sss", $var1,$var2,$var3);
            $stmt->execute();
            while ($item_row = $stmt->fetch()) {
            print_r($item_row);     // prints: 1
            var_dump($item_row);    // prints: bool(true)
            }

I've tried using the code from How can I prevent SQL injection in PHP? but it breaks the whole page.

I've also read and tried examples from How can I prevent SQL injection in PHP?, but none work. The above example is the farthest I've gotten.

Any ideas what I need to do?

your column names are wrong in your PDO statement you need to use `col1` , `col2`, `col3` just like you do in your first statement. — cmorrissey, Feb 21 '14 at 20:44
I changed the names to make them easier to read. They do in fact match up. And if that were the case I don't think I get `bool(true)` — Ghost Echo, Feb 21 '14 at 21:24

score 1 · Accepted Answer · answered Feb 21 '14 at 20:45

1

Try this: a way to return all data neatly in one array: Also make sure the name of the columns you are fetching are correct.

$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;
}

answered Feb 21 '14 at 20:45

Edward

3,061
6
32
52

names match up, i just copied them over wrong here. Would this code come after the `$stmt->execute();`? – Ghost Echo Feb 21 '14 at 21:38
after execute() since it's binding the result. – Edward Feb 21 '14 at 22:21
this is just about working how I want it. But how can I echo `$x[5]`? I can echo `$x[the_column_name]`, but the other one doesn't do anything. – Ghost Echo Feb 24 '14 at 15:53
your answer along with http://www.php.net/manual/en/mysqli-stmt.bind-result.php#85470 helped me arrive at my solution – Ghost Echo Feb 24 '14 at 18:16

miken32 · Answer 2 · 2014-02-21T21:43:54.163

1

Have you tried binding the result to a variable?

$stmt = $db->prepare("SELECT col1, col2, col3 FROM `table` WHERE col1 = ? and col2 = ? and col3 =?");
$stmt->bind_param("sss", $var1, $var2, $var3);
$stmt->execute();
$stmt->bind_result($col1, $col2, $col3);
while ($stmt->fetch()) {
    echo "$col1 $col2 $col3<br/>";
}

PDO is much more intuitive, and would look something like this:

//assuming $db is a PDO object
$stmt = $db->prepare("SELECT col1, col2, col3 FROM `table` WHERE col1 = ? and col2 = ? and col3 =?");
$params = array($var1, $var2, $var3);
$stmt->execute($params);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
var_dump($rows);

edited Feb 21 '14 at 21:43

answered Feb 21 '14 at 21:32

miken32

42,008
16
111
154

This seems to be the right direction.. But I need to `SELECT *` so I can echo out something like `item_row[5]` (as seen in my 1st example). How can I bind *? Something like `$stmt->bind_result($item_row[]);`. See what I mean? – Ghost Echo Feb 24 '14 at 15:16
I guess that's another reason to go with PDO instead of mysqli. You can skip the result binding and dump it right into an array directly. If you're sticking with mysqli, you'll just need to bind as many variables as there are columns. – miken32 Feb 24 '14 at 18:05
1st thanks for the response. Your answer helped me move in the direction I needed. But in Edward's answer I can echo out `$parameter[1]`, `parameter[2]`, etc. I believe I found my answer with his and also using this: http://www.php.net/manual/en/mysqli-stmt.bind-result.php#85470 – Ghost Echo Feb 24 '14 at 18:14
1

Yup, interesting use of `call_user_func_array()` for sure. I would encourage you to investigate PDO further though. It's so nice not to have to worry about counting question marks and dealing with stuff like "sssiissi" when making queries! – miken32 Feb 24 '14 at 18:22

msyqli prepared statement not working right

2 Answers2