kel Posted May 3, 2009 Share Posted May 3, 2009 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 Link to comment https://forums.phpfreaks.com/topic/156630-mysql_fetch_array-putting-results-into-tables/ Share on other sites More sharing options...
Ken2k7 Posted May 3, 2009 Share Posted May 3, 2009 Put all the print statement you have at the bottom of the while loop to be inside the while loop, after all the variables. Link to comment https://forums.phpfreaks.com/topic/156630-mysql_fetch_array-putting-results-into-tables/#findComment-824739 Share on other sites More sharing options...
kel Posted May 3, 2009 Author Share Posted May 3, 2009 Have put the print statements inside the while loop, but nothing has changed Link to comment https://forums.phpfreaks.com/topic/156630-mysql_fetch_array-putting-results-into-tables/#findComment-824747 Share on other sites More sharing options...
Ken2k7 Posted May 3, 2009 Share Posted May 3, 2009 Can you post your updated code? Link to comment https://forums.phpfreaks.com/topic/156630-mysql_fetch_array-putting-results-into-tables/#findComment-824751 Share on other sites More sharing options...
kel Posted May 3, 2009 Author Share Posted May 3, 2009 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>"); } ?> Link to comment https://forums.phpfreaks.com/topic/156630-mysql_fetch_array-putting-results-into-tables/#findComment-824752 Share on other sites More sharing options...
Ken2k7 Posted May 3, 2009 Share Posted May 3, 2009 <?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? Link to comment https://forums.phpfreaks.com/topic/156630-mysql_fetch_array-putting-results-into-tables/#findComment-824763 Share on other sites More sharing options...
kel Posted May 3, 2009 Author Share Posted May 3, 2009 Same as before, no change [attachment deleted by admin] Link to comment https://forums.phpfreaks.com/topic/156630-mysql_fetch_array-putting-results-into-tables/#findComment-824769 Share on other sites More sharing options...
kel Posted May 3, 2009 Author Share Posted May 3, 2009 I just ran the query in mysql and there should be 3 records Link to comment https://forums.phpfreaks.com/topic/156630-mysql_fetch_array-putting-results-into-tables/#findComment-824771 Share on other sites More sharing options...
Ken2k7 Posted May 3, 2009 Share Posted May 3, 2009 <?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. Link to comment https://forums.phpfreaks.com/topic/156630-mysql_fetch_array-putting-results-into-tables/#findComment-824775 Share on other sites More sharing options...
kel Posted May 3, 2009 Author Share Posted May 3, 2009 still no change Link to comment https://forums.phpfreaks.com/topic/156630-mysql_fetch_array-putting-results-into-tables/#findComment-824776 Share on other sites More sharing options...
kel Posted May 3, 2009 Author Share Posted May 3, 2009 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 Link to comment https://forums.phpfreaks.com/topic/156630-mysql_fetch_array-putting-results-into-tables/#findComment-824778 Share on other sites More sharing options...
kel Posted May 3, 2009 Author Share Posted May 3, 2009 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 />"; } ?> Link to comment https://forums.phpfreaks.com/topic/156630-mysql_fetch_array-putting-results-into-tables/#findComment-824780 Share on other sites More sharing options...
kel Posted May 3, 2009 Author Share Posted May 3, 2009 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 Link to comment https://forums.phpfreaks.com/topic/156630-mysql_fetch_array-putting-results-into-tables/#findComment-824781 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.