mysql_fetch_array - putting results into tables

[kel]

Hi guys (noobie here)

 

I'm trying to write a script that will query my mysql database and put the results into a table. So far I have been able to return one record (there should be more), but can't seem to work out how to get them all displayed. i think I need to create some kind of loop but I'm not really sure where to start.

 

Here is the code that I am currently using:

 

<?php

$result = mysql_query("SELECT it.asset_tag, it.serial, it.sched_date, sv.serv_date, sv.comment FROM item it, service sv WHERE it.asset_tag = sv.asset_tag AND it.sched_date < Date_sub(current_date(), interval 3 month)");

 

// Start table

print ("<TABLE BORDER=1>\n");

print ("<TH>ASSET TAG</TH><TH>SERIAL</TH><TH>MAINTENENCE SCHEDULED DATE</TH><TH>SERVICE DATE</TH><TH>COMMENT</TH>");

 

// ---------------------------------------------------need to get this to loop.

 

while ($row_array = mysql_fetch_array($result))

  {

$asset_tag = $row_array[0];

$serial = $row_array[1];

$sched_date = $row_array[2];

$serv_date = $row_array[3];

$comment = $row_array[4];

  }

 

print("</TD></TR>\n");

 

print ("<TR ALIGN=LEFT VALIGN = TOP>");

print ("<TD>$asset_tag</TD>");

print ("<TD>$serial</TD>");

print ("<TD>$sched_date</TD>");

print ("<TD>$serv_date</TD>");

print ("<TD>$comment</TD>");

?>

 

Can anyone see where i am going wrong or what I could do to make sure all records are displayed in the table?

 

Thanks

kel

[Ken2k7]

Put all the print statement you have at the bottom of the while loop to be inside the while loop, after all the variables.

[kel]

Have put the print statements inside the while loop, but nothing has changed

[Ken2k7]

Can you post your updated code?

[kel]

Sure

 

<?php

$result = mysql_query("SELECT it.asset_tag, it.serial, it.sched_date, sv.serv_date, sv.comment FROM item it, service sv WHERE it.asset_tag = sv.asset_tag AND it.sched_date < Date_sub(current_date(), interval 3 month)");

 

// Start table

print ("<TABLE BORDER=1>\n");

print ("<TH>ASSET TAG</TH><TH>SERIAL</TH><TH>MAINTENENCE SCHEDULED DATE</TH><TH>SERVICE DATE</TH><TH>COMMENT</TH>");

 

// ---------------------------------------------------need to get this to loop.

 

while ($row_array = mysql_fetch_array($result))

  {

$asset_tag = $row_array[0];

$serial = $row_array[1];

$sched_date = $row_array[2];

$serv_date = $row_array[3];

$comment = $row_array[4];

print("</TD></TR>\n");

print ("<TR ALIGN=LEFT VALIGN = TOP>");

print ("<TD>$asset_tag</TD>");

print ("<TD>$serial</TD>");

print ("<TD>$sched_date</TD>");

print ("<TD>$serv_date</TD>");

print ("<TD>$comment</TD>");

  }

?>

[Ken2k7]

<?php
$result = mysql_query("SELECT it.asset_tag, it.serial, it.sched_date, sv.serv_date, sv.comment FROM item it, service sv WHERE it.asset_tag = sv.asset_tag AND it.sched_date < Date_sub(current_date(), interval 3 month)");

// Start table
print ("<TABLE BORDER=1>\n");
print ("<TH>ASSET TAG</TH><TH>SERIAL</TH><TH>MAINTENENCE SCHEDULED DATE</TH><TH>SERVICE DATE</TH><TH>COMMENT</TH>");

// ---------------------------------------------------need to get this to loop.

echo mysql_num_rows($result);  // <-- added this line

while ($row_array = mysql_fetch_array($result))
  {
   $asset_tag = $row_array[0];
   $serial = $row_array[1];
   $sched_date = $row_array[2];
   $serv_date = $row_array[3];
   $comment = $row_array[4];
   print("</TD></TR>\n");
   print ("<TR ALIGN=LEFT VALIGN = TOP>");
   print ("<TD>$asset_tag</TD>");
   print ("<TD>$serial</TD>");
   print ("<TD>$sched_date</TD>");
   print ("<TD>$serv_date</TD>");
   print ("<TD>$comment</TD>");
  }
?>

 

I added one line (as you can see above). Can you tell me what the prints out?

[kel]

Same as before, no change

 

 

 

[attachment deleted by admin]

[kel]

I just ran the query in mysql and there should be 3 records

[Ken2k7]

<?php
$result = mysql_query("SELECT it.asset_tag, it.serial, it.sched_date, sv.serv_date, sv.comment FROM item it, service sv WHERE it.asset_tag = sv.asset_tag AND it.sched_date < Date_sub(current_date(), interval 3 month)");
echo mysql_num_rows($result);  // <-- added this line

Okay, do that and tell me what the echos out.

[kel]

still no change

[kel]

I did have the correct results before i tried to put all of this into a table - unless I've managed to remove some code since without realising

[kel]

Previously had the script written as below - this at least gave me the asset_tag for 3 records (albeit not in a table)

 

<?php $db_selected = mysql_select_db('maint_db', $db_link); ?> <! selects the relevant database -->

 

 

<FORM ACTION="Handleform.php" METHOD="POST"> <! this(action bit) says which page(or script) is going to receive data from the form -->

 

<!item - asset, serial, prt-name, sched_date -->

<TABLE BORDER=1><TR BGCOLOR="RED"></TR>

<TD>Asset:</TD>

<TD><INPUT TYPE=TEXT NAME="Asset" SIZE=10><BR></TD>

<TD>Area:</TD>

<TD><INPUT TYPE=TEXT NAME="Area" SIZE=35><BR></TD>

</TR>

<TR BGCOLOR="white">

<TD>Serial:</TD>

<TD><INPUT TYPE=TEXT NAME="Serial" SIZE=30><BR></TD>

<TD>Workstation:</TD>

<TD><INPUT TYPE=TEXT NAME="Workstation" SIZE=3><BR></TD>

</TR>

<TR BGCOLOR="white">

<TD>Printer Name:</TD>

<TD><INPUT TYPE=TEXT NAME="Prt_name" SIZE=25><BR></TD>

</TR>

<TR BGCOLOR="white">

<TD>Make:</TD>

<TD><INPUT TYPE=TEXT NAME="Make" SIZE=25><BR></TD>

</TR>

<TR BGCOLOR="white">

<TD>Model:</TD>

<TD><INPUT TYPE=TEXT NAME="Model" SIZE=25><BR></TD>

</TR>

<TR><TD></TD></TR>

<TR BGCOLOR="white">

<TD>Service ID:</TD>

<TD><INPUT TYPE=TEXT NAME="Serv_id" SIZE=25><BR></TD>

<TD>User:</TD>

<TD><INPUT TYPE=TEXT NAME="Username" SIZE=25><BR></TD>

</TR>

<TR BGCOLOR="white">

<TD>Scheduled Maintenance:</TD>

<TD><INPUT TYPE=TEXT NAME="Sched_date" SIZE=25><BR></TD>

<TD>Comments:</TD>

<TD><TEXTAREA NAME="Comments" ROWS=5 COLS=40></TEXTAREA><BR></TD>

</TR>

<TR BGCOLOR="white">

<TD>Scheduled Maintenance:</TD>

<TD><INPUT TYPE=TEXT NAME="Sched_date" SIZE=25><BR></TD>

</TR>

 

</TABLE>

<BR>

<INPUT TYPE=RESET NAME="RESET" VALUE="Reset">

<INPUT TYPE=SUBMIT NAME="NEW" VALUE="New">

<INPUT TYPE=SUBMIT NAME="DELETE" VALUE="Delete">

<INPUT TYPE=SUBMIT NAME="UPDATE" VALUE="Update">

<INPUT TYPE=SUBMIT NAME="SEARCH" VALUE="Search">

</FORM>

 

<?php

$result = mysql_query("SELECT it.asset_tag, it.serial, it.sched_date, sv.serv_date, sv.comment FROM item it, service sv WHERE it.asset_tag = sv.asset_tag AND it.sched_date < Date_sub(current_date(), interval 3 month)");

 

while($row = mysql_fetch_array($result))

  {

  echo $row['asset_tag'];

// print_r($row);

echo "<br />";

  }

 

?>

[kel]

Sorry what I meant to say was that last bit was the page that I am using and the submit button calls the script that I originally posted - which should then put the query results into a table