Display All Database Records

  • Thread starter Thread starter Anonymous
  • Start date Start date
A

Anonymous

Guest
Hi,

I am attempting to display all records from the database but the following code
only displays the last record ??



$query="SELECT * FROM table";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

$i=0;
while ($i < $num) {

$caID=mysql_result($result,$i,"caID");
$caCuNo=mysql_result($result,$i,"caCustomerNo");
$caDOD=mysql_result($result,$i,"caDODdisplay");
$caDOT=mysql_result($result,$i,"caDropOffTime");
$caPUD=mysql_result($result,$i,"caPUDdisplay");
$caPUT=mysql_result($result,$i,"caPickUpTime");
$caDays=mysql_result($result,$i,"caDays");

$i++;
}
?>

Any Ideas

Thanks
 
You aren't fetching the array of results. If done properly you just need the while loop and no count:
Code:
<?php
$query="SELECT * FROM table";
$result=mysql_query($query);

$num=mysql_numrows($result);

if ($num > 0) {
	while ($row = mysql_fetch_array($result)) {
	
	$caID=$row['caID'];
	$caCuNo=$row['caCustomerNo'];
	$caDOD=$row['caDODdisplay'];
	$caDOT=$row['"caDropOffTime'];
	$caPUD=$row['caPUDdisplay'];
	$caPUT=$row['caPickUpTime'];
	$caDays=$row['caDays'];
	
	}
}
?>
 
Hi,

I have made changes as you suggested but
still only getting last record displayed
??

Thanks
 
correct this query its
$num=mysql_num_rows($result);

you have missed underscore (_) between num and rows
 
in you while loop you are over writing your variables with each iteration of records. So you will only store the last record when the loop finishes.

store them in an array or print/echo the rows from inside the loop.

where are you trying to display your records ? on a web page?
 
The problem is that you have closed the MySQL Connection before looping.

Your query should look EXACTLY like this:

PHP:
<?php

$table = 'table'; // set the name of the table.. it remains dynamic in case you need it again.
$query="SELECT * FROM $table";     // The actual MySQL query..
$result=mysql_query($query);     // getting the resource 
$num=mysql_num_rows($result);     // getting the number of rows in the query.. nothing else.
if ($num > 0) {
   while ($row = mysql_fetch_assoc($result)) { // changed it to assoc, since array is numeric..
   
   // below this line is pointless since you don't do anything with it. I am commenting out the whole section and replacing it with something that will show you that all lines are being showed..
   /* // Comment more than one line..
   $caID=$row['caID'];
   $caCuNo=$row['caCustomerNo'];
   $caDOD=$row['caDODdisplay'];
   $caDOT=$row['"caDropOffTime'];
   $caPUD=$row['caPUDdisplay'];
   $caPUT=$row['caPickUpTime'];
   $caDays=$row['caDays'];
   */ // END of the multiple line comment.

   echo '<pre>'; print_r($row); echo '</pre>';
   }
}
?>

If you want to store the entire dataset for use at a later time, you must stuff the $row into another array. You do this by:

PHP:
/* ... */ excluding your other code ..
while ($row = mysql_fetch_assoc($result)) { // remember.. ASSOC not ARRAY.
  $array[] = $row;
}
 

Now $array is stuffed with all of your row data.
then you can iterate through $array to display your contents at a later time in your script.

for example:
PHP:
foreach($array as $key => $subarray) { 
  // we don't care about the $Key 
  echo '<pre>'; print_r($subarray); echo '</pre>';

  // albeit, here you can do something like.. echo $subarray['fieldname'] here..
}
 

Hope this makes sense for ya.
 
I've tried that thanks.

What I am trying to do is select each row from the table
and insert in a predefined table form my web page
 
*sigh*

PHP:
$query = "SELECT * FROM $table":
$result = mysql_query($query);
$num = mysql_num_rows($result);

if ($num > 0) { 
  echo '<table>';
  echo '<tr>';
  echo '<th>Header 1</th>';
  echo '<th>Header 2</th>';
  // keep doing this for however many records you want to show.. 
  echo '</tr>';
  while ($row = mysql_fetch_assoc($result)) { 
     echo '<tr>';
     echo '<td>'.$row['forHeader1'].'</td>';
     echo '<td>'.$row['forHeader2'].'</td>'; // obviously change the index to match your field name..
     /* keep doing this for however many headers you have.. */
     echo '</tr>';
  }
} else { 
  echo '
  <table>
    <tr>
      <td>Sorry.. We got nothing..</td>
    </tr>
  </table>
  ';
}
 
 
Back
Top