script won't echo first row in table

chrisdee

Hi,

I don't understand why my script below won't echo the first row in my mysqli table.
It starts from row 2. Any ideas why?

<?php 
$link   = mysqli_connect('localhost','mydatabase','password','username'); # Connect to database 
$query  = "SELECT * from mytable"; # Select what to query 
$result = mysqli_query($link, $query); # Query table 

echo "<table border=1>"; 
# echo table keys/titles
echo "<tr>"; 
foreach(mysqli_fetch_assoc($result) as $keys => $values) { 
 echo "<th align=left>".ucfirst($keys)."</th>"; 
} 
echo "</tr>"; 

# echo table data
while ($rows = mysqli_fetch_assoc($result)) { 
 echo "<tr>"; 
 foreach($rows as $row) { 
  print "<td>".$row."</td>"; 
 } 
 echo "</tr>"; 
} 
echo "</table>"; 

mysqli_free_result($result); 
mysqli_close($link); 
?>

chrisdee

I managed to "solve" it by doing the following :

<?php
$link   = mysqli_connect('localhost','db','pass','user'); # Connect to database
$query  = "SELECT * from table"; # Select what to query
$titles = mysqli_query($link, $query); # Query table
$data   = mysqli_query($link, $query); # Query table


echo "<table border=1>";
echo "<tr>";
foreach(mysqli_fetch_assoc($titles) as $keys => $values) { # echo tables keys/titles
 echo "<th align=left>".ucfirst($keys)."</th>";
}
print "<th align=left>Reservasjon</th>";
echo "</tr>";

while ($rows = mysqli_fetch_assoc($data)) { # echo table data
 echo "<tr>";
 foreach($rows as $row) {
  print "<td>".$row."</td>";
 }
 echo "<td><a href=edit.php?id=".$rows['id'].">Edit</a></td>";
 echo "</tr>";
}
echo "</table>";

mysqli_free_result($titles); # close titles query
mysqli_free_result($data);   # close data query
mysqli_close($link);         # close link
?>

dalecosp

The internal pointer to the result set isn't being reset. Normally you don't construct queries like this; if you want to use a data set more than once most people will place the data into a PHP array.

If you really want to do it this way, rewind the resultset's pointer with:

mysqli_data_seek($result,0);

... before the 2nd call to fetch_assoc().

chrisdee

dalecosp;11052415 wrote:
if you want to use a data set more than once most people will place the data into a PHP array.

How do I do this?

NogDog

chrisdee;11052419 wrote:
How do I do this?

mysqli_fetch_all()

However, be careful if there's a chance you could be pulling in a lot of records, as large PHP arrays are a good way to eat up memory.

dalecosp

NogDog;11052421 wrote:
mysqli_fetch_all()

However, be careful if there's a chance you could be pulling in a lot of records, as large PHP arrays are a good way to eat up memory.

That's one way, and a good one, with the understanding you may not want to grab anything huge.

Classic usage was something like:

<?php

//perhaps a listing of products.  What page are we on?  That determines the $start variable.
$start = $page_number-100+1;

$s = "select title,description,price from products limit $start, 100;";
$r = mysqli_query($s);

if ($r && $r->num_rows) {
   $data_array = array();
   $n = 0;
   while ($row = mysqli_fetch_assoc($r)) {
      $data_array[$n]['title'] = $row['title'];
      $data_array[$n]['description'] = $row['description'];
      $data_array[$n]['price'] = $row['price'];
      $n++;
   }
}

NogDog

And it looks like you can use mysqli_fetch_field(), mysqli_fetch_fields(), and/or mysqli_fetch_field_direct() to get the column name info if that's all that's needed. Otherwise, what I've typically done is something like:

$headingsDone = false;
while($row = your_fetch_function()) {
  if(!$headingsDone) {
    $headingsDone = true;
    echo '<tr>';
    foreach($row as $key => $value) {
      echo '<td>'.ucfirst($key).'</td>';
    }
    echo "</tr>\n";
  }
  echo '<tr>';
  foreach($row as $value) {
    echo "<td>$value</td>\n";
  }
  echo "</tr>\n";
}