I'm having a very weird problem with $result->fetch_assoc(). Here are two
very simplified examples. One uses a prepared statement (although for this
exercise I'm not using any bound parameters, my results aren't affected);
the other example uses $db->query(). Look at the retrieved $row.

<?php
$db = new mysqli('host.giiexpress.com', 'giiexpr', 'xxxx', 'giiexpr_db')
or die('connect failed');
$select = 'SELECT prod_num, prod_title FROM prod WHERE prod_num = 53851 and
prod_title != "snort"';
$s = $db->prepare($select)
or die($db->error);
$s->execute()
or die($db->error);
$result = $db->store_result()
or die($db->error);
echo '$result: ' . "\n"; var_dump($result);
$num_rows = $result->num_rows;
echo '$num_rows: ' . "\n"; var_dump($num_rows);
$row = $result->fetch_assoc()
or die($db->error);
echo '$row: ' . "\n"; var_dump($row);
?>

This one gives me empty strings:

$result:
object(mysqli_result)#3 (0) {
}
$num_rows:
int(1)
$row:
array(2) {
["prod_num"]=>
string(0) ""
["prod_title"]=>
string(0) ""
}

If I simply use $db->query(), I get the proper results:

<?php
$db = new mysqli('host.giiexpress.com', 'giiexpr', 'xxxx', 'giiexpr_db
or die('connect failed');
$select = 'SELECT prod_num, prod_title FROM prod WHERE prod_num = 53851 and
prod_title != "snort"';

$result = $db->query($select);

echo '$result: ' . "\n"; var_dump($result);
$num_rows = $result->num_rows;
echo '$num_rows: ' . "\n"; var_dump($num_rows);
$row = mysqli_fetch_assoc($result);
echo '$row: ' . "\n"; var_dump($row);
?>

$result:
object(mysqli_result)#2 (0) {
}
$num_rows:
int(1)
$row:
array(2) {
["prod_num"]=>
string(5) "53851"
["prod_title"]=>
string(41) "2007 U.S. Consumer Digital Imaging Survey"
}

I've tried this on Windows using PHP 5.2.2, and on CentOS using 5.0.4. I get
the same results.

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com <http://www.the-infoshop.com/>
www.giiexpress.com <http://www.giiexpress.com/>
www.etudes-marche.com

I think I've figured things out. First off, I missed $db->stmt_init()
completely. Second, I missed the fact that you HAVE to bind the retrieved
values to variables, you can't create a result object and then use
$result->fetch_array(). And so on...

In short, I guess I was mixing incompatible techniques. I really need to pay
attention to the classes of all of the objects when I code. This is a
perfect example of how someone who is confused can write confused code.

I really hate the idea of binding so many parameters and so many result
variables, but I guess it is the tedious part of using prepared statements.

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com


> -----Original Message-----
> From: Jerry Schwartz [mailto:jschwartz@the-infoshop.com]
> Sent: Thursday, July 26, 2007 10:29 AM
> To: mysql@lists.mysql.com
> Subject: PHP mysqli problem
>
> I'm having a very weird problem with $result->fetch_assoc().
> Here are two
> very simplified examples. One uses a prepared statement
> (although for this
> exercise I'm not using any bound parameters, my results
> aren't affected);
> the other example uses $db->query(). Look at the retrieved $row.
>
> <?php
> $db = new mysqli('host.giiexpress.com', 'giiexpr', 'xxxx',
> 'giiexpr_db')
> or die('connect failed');
> $select = 'SELECT prod_num, prod_title FROM prod WHERE
> prod_num = 53851 and
> prod_title != "snort"';
> $s = $db->prepare($select)
> or die($db->error);
> $s->execute()
> or die($db->error);
> $result = $db->store_result()
> or die($db->error);
> echo '$result: ' . "\n"; var_dump($result);
> $num_rows = $result->num_rows;
> echo '$num_rows: ' . "\n"; var_dump($num_rows);
> $row = $result->fetch_assoc()
> or die($db->error);
> echo '$row: ' . "\n"; var_dump($row);
> ?>
>
> This one gives me empty strings:
>
> $result:
> object(mysqli_result)#3 (0) {
> }
> $num_rows:
> int(1)
> $row:
> array(2) {
> ["prod_num"]=>
> string(0) ""
> ["prod_title"]=>
> string(0) ""
> }
>
> If I simply use $db->query(), I get the proper results:
>
> <?php
> $db = new mysqli('host.giiexpress.com', 'giiexpr', 'xxxx', 'giiexpr_db
> or die('connect failed');
> $select = 'SELECT prod_num, prod_title FROM prod WHERE
> prod_num = 53851 and
> prod_title != "snort"';
>
> $result = $db->query($select);
>
> echo '$result: ' . "\n"; var_dump($result);
> $num_rows = $result->num_rows;
> echo '$num_rows: ' . "\n"; var_dump($num_rows);
> $row = mysqli_fetch_assoc($result);
> echo '$row: ' . "\n"; var_dump($row);
> ?>
>
> $result:
> object(mysqli_result)#2 (0) {
> }
> $num_rows:
> int(1)
> $row:
> array(2) {
> ["prod_num"]=>
> string(5) "53851"
> ["prod_title"]=>
> string(41) "2007 U.S. Consumer Digital Imaging Survey"
> }
>
> I've tried this on Windows using PHP 5.2.2, and on CentOS
> using 5.0.4. I get
> the same results.
>
> Regards,
>
> Jerry Schwartz
> The Infoshop by Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
>
> www.the-infoshop.com <http://www.the-infoshop.com/>
> www.giiexpress.com <http://www.giiexpress.com/>
> www.etudes-marche.com
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=j...e-infoshop.com
>
>