[SOLVED] Print/echo all returned MySQLi rows

[cgm225]

Currently, I have some MySQLi code (shown below) that returns rows based on a prepared statement, of which I print out the first entry.  My question is, how do I print/echo ALL the returned rows... currently, it only does the first one.

 

Thanks in advance!

 

$stmt = $mysqli->prepare("SELECT id, title, note, date, timestamp FROM notes ORDER BY id DESC");
$stmt->execute();
$stmt->bind_result($id, $title, $note, $date, $timestamp);
$stmt->fetch();
printf("%s %s %s %s %s\n", $id, $title, $note, $date, $timestamp);
$stmt->close();

 

[rhodesa]

I haven't done a whole lot with MySQLi, but it should just be:

 

<?php
$stmt = $mysqli->prepare("SELECT id, title, note, date, timestamp FROM notes ORDER BY id DESC");
$stmt->execute();
$stmt->bind_result($id, $title, $note, $date, $timestamp);
while($stmt->fetch())
  printf("%s %s %s %s %s\n", $id, $title, $note, $date, $timestamp);
$stmt->close();
?>

[cgm225]

Using your exact example above results in my script not loading.  No error messages are given, it simply does not load.  This occurs for some reason I don't understand, when I use that while statement.  Any ideas how to debug this?

[BlueSkyIS]

i don't know if this will help, but i'm not a big fan of the short whiles, ifs or anything like that. to maintain consistency, i prefer to block them even if they're only one line. so i'd go with this whether it helps or not.

 

while($stmt->fetch()) {
  printf("%s %s %s %s %s\n", $id, $title, $note, $date, $timestamp);
}

[cgm225]

This piece of code works, printing the first retrieved row/entry...

$stmt = $mysqli->prepare("SELECT id, title, note, date, timestamp FROM notes ORDER BY id DESC");
$stmt->execute();
$stmt->bind_result($id, $title, $note, $date, $timestamp);
$stmt->fetch();
printf("%s %s %s %s %s\n", $id, $title, $note, $date, $timestamp);    

 

This piece of code does not work, and firefox displays a "Problem loading page" screen...

$stmt = $mysqli->prepare("SELECT id, title, note, date, timestamp FROM notes ORDER BY id DESC");
$stmt->execute();
$stmt->bind_result($id, $title, $note, $date, $timestamp);
while($stmt->fetch()) {
     printf("%s %s %s %s %s\n", $id, $title, $note, $date, $timestamp);
}
$stmt->close();

 

 

Is there a problem with my code, or maybe a bigger issue with my php/mysql server settings?

 

Thanks again in advance!

[BlueSkyIS]

sorry, i have no experience with mysqli, but it looks like fetch() should be mysqli_fetch().

 

there doesn't appear to be a built-in fetch() function, but there is a mysqli_fetch() function.

[cgm225]

Thanks for the suggestions.  That function is depreciated, but, nevertheless, I tried it and the problem remained.

 

Any other ideas?

[BlueSkyIS]

well, if nothing else, i'm learning mysqli while trying to help. this works for me:

 

if ($stmt = $mysqli->prepare("SELECT id, date, bill_street1 FROM orders ORDER BY date ASC LIMIT 5")) { 
    $stmt->execute(); 

    /* bind variables to prepared statement */ 
    $stmt->bind_result($col1, $col2, $col3); 

    /* fetch values */ 
    while ($stmt->fetch()) { 
        printf("%s %s %s<BR>", $col1, $col2, $col3); 
    } 

    /* close statement */ 
    $stmt->close(); 
}

 

got it from here: http://devzone.zend.com/node/view/id/686

 

i don't see anything odd in your code. it looks like it should work properly. perhaps there is an issue in your php/mysql installation.

[cgm225]

How would I debug the issue if it is a MySQL/PHP installation issue.  Do I need to enact anything else in the php.ini other than the mysqli extension?

[cgm225]

I have reduced the problem further..  The while does not timeout if I exclude the "notes" mysql field which is a larger size, longtext.

 

Any ideas?

[cgm225]

I think I may be having this problem:: http://bugs.php.net/bug.php?id=42776 and http://framework.zend.com/issues/browse/ZF-1498

[rhodesa]

Try this:

 

<?php
  if($result = $mysqli->query("SELECT id, title, note, date, timestamp FROM notes ORDER BY id DESC")){
    while($row = $result->fetch_assoc()) {
      printf("%s %s %s %s %s\n", $row['id'], $row['title'], $row['note'], $row['date'], $row['timestamp']);
    }
    $result->close();
  }else
    printf("MySQLi Error: %s\n", $mysqli->error);
  $mysqli->close();
?>