dflow Posted September 14, 2009 Share Posted September 14, 2009 i would like to do the following loop: i have a proposals table with the following fields: proposalID requestID statusID and a status_list table statusID statuslabel get the proposals result by requestID with status label filtered by the each specific row's statusID proposal#1+STATUSLABEL(BY proposals.statusID) proposal#2+STATUSLABEL(BY proposals.statusID) how do i approach this? Link to comment https://forums.phpfreaks.com/topic/174229-loop-question/ Share on other sites More sharing options...
dflow Posted September 14, 2009 Author Share Posted September 14, 2009 i would like to do the following loop: i have a proposals table with the following fields: proposalID requestID statusID and a status_list table statusID statuslabel get the proposals result by requestID with status label filtered by the each specific row's statusID proposal#1+STATUSLABEL(BY proposals.statusID) proposal#2+STATUSLABEL(BY proposals.statusID) how do i approach this? i got this code together <?php $ProposalLists_endRow = 0; $ProposalLists_columns = 1; // number of columns $ProposalLists_hloopRow1 = 0; // fit row flag do { if($ProposalLists_endRow == 0 && $ProposalLists_hloopRow1++ != 0) echo "<tr>"; ?> <td> <a href="edit_proposal_form.php?PropID=<?php echo $row_ProposalLists['ProposalID']; ?>&RID=<?php echo $row_ProposalLists['RequestID']; ?>"><?php echo $row_ProposalLists['ProposalID']; ?>-<?php echo $row_ProposalLists['DepartureDate']; ?>-<?php echo $row_ProposalLists['ReturnDate']; ?>-<?php echo $row_ProposalLists['lanirltd_price_per_night']; ?>--<?php echo $row_StatusDisplay['StatusLabel']; ?><?php echo $row_ProposalLists['StatusID']; ?></a></td> <?php $ProposalLists_endRow++; if($ProposalLists_endRow >= $ProposalLists_columns) { ?> </tr> <?php $ProposalLists_endRow = 0; } } while ($row_ProposalLists = mysql_fetch_assoc($ProposalLists)); if($ProposalLists_endRow != 0) { while ($ProposalLists_endRow < $ProposalLists_columns) { echo("<td> </td>"); $ProposalLists_endRow++; } echo("</tr>"); }?> loop is fine but the status label is not filtered Link to comment https://forums.phpfreaks.com/topic/174229-loop-question/#findComment-918479 Share on other sites More sharing options...
RussellReal Posted September 14, 2009 Share Posted September 14, 2009 $q = mysql_query("SELECT * FROM proposals JOIN status_list ON (proposals.statusID = status_list.statusID)"); while ($row = mysql_fetch_assoc($q)) { print_r($row); } Link to comment https://forums.phpfreaks.com/topic/174229-loop-question/#findComment-918481 Share on other sites More sharing options...
dflow Posted September 14, 2009 Author Share Posted September 14, 2009 $q = mysql_query("SELECT * FROM proposals JOIN status_list ON (proposals.statusID = status_list.statusID)"); while ($row = mysql_fetch_assoc($q)) { print_r($row); } yeah but what about WHERE RequestID=2 for example? Link to comment https://forums.phpfreaks.com/topic/174229-loop-question/#findComment-918581 Share on other sites More sharing options...
RussellReal Posted September 15, 2009 Share Posted September 15, 2009 add it to the end Link to comment https://forums.phpfreaks.com/topic/174229-loop-question/#findComment-918624 Share on other sites More sharing options...
dflow Posted September 16, 2009 Author Share Posted September 16, 2009 add it to the end i get an error: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\wamp\www\lanirltdcom\backoffice\test.php on line 18 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Untitled Document</title> </head> <body> <?php $con = mysql_connect("localhost","root",""); if (!$con) { die('Could not connect: ' . mysql_error()); } $q = mysql_query("SELECT * FROM proposals JOIN status_list ON (proposals.statusID = status_list.statusID) WHERE proposals.RequestID=2 "); while ($row = mysql_fetch_assoc($q)) { print_r($row); } ?> </body> </html> <?php mysql_close($con); ?> Link to comment https://forums.phpfreaks.com/topic/174229-loop-question/#findComment-919489 Share on other sites More sharing options...
RussellReal Posted September 16, 2009 Share Posted September 16, 2009 that SHOULD work.. try quoting the 2 WHERE proposals.requestID = '2' Link to comment https://forums.phpfreaks.com/topic/174229-loop-question/#findComment-919605 Share on other sites More sharing options...
dflow Posted September 16, 2009 Author Share Posted September 16, 2009 that SHOULD work.. try quoting the 2 WHERE proposals.requestID = '2' still get the error Link to comment https://forums.phpfreaks.com/topic/174229-loop-question/#findComment-919771 Share on other sites More sharing options...
gevans Posted September 16, 2009 Share Posted September 16, 2009 You shouldn't use WHERE here, as you're already making a filtering comparison. Use AND to add further conditions; $q = mysql_query("SELECT * FROM proposals JOIN status_list ON (proposals.statusID = status_list.statusID) AND proposals.RequestID=2 "); Also, you may have problems selecting everything '*' as there are fields in both tables with the same name (you'll probably get this error next). Link to comment https://forums.phpfreaks.com/topic/174229-loop-question/#findComment-919775 Share on other sites More sharing options...
dflow Posted September 17, 2009 Author Share Posted September 17, 2009 i still get that error Link to comment https://forums.phpfreaks.com/topic/174229-loop-question/#findComment-920047 Share on other sites More sharing options...
RussellReal Posted September 18, 2009 Share Posted September 18, 2009 ON is just a clause for the JOIN, WHERE is acceptable in the syntax, and is actually proper. Link to comment https://forums.phpfreaks.com/topic/174229-loop-question/#findComment-920924 Share on other sites More sharing options...
gevans Posted September 19, 2009 Share Posted September 19, 2009 Ahh yes sorry, I miss read the document I was looking at. Just scanned it briefly. Both can be used and have their advantages in different situations. Link to comment https://forums.phpfreaks.com/topic/174229-loop-question/#findComment-921104 Share on other sites More sharing options...
dflow Posted September 22, 2009 Author Share Posted September 22, 2009 ok fixed the error how do i echo the status_list.status_label ? Link to comment https://forums.phpfreaks.com/topic/174229-loop-question/#findComment-922918 Share on other sites More sharing options...
RussellReal Posted September 22, 2009 Share Posted September 22, 2009 print_r the row you get with mysql_fetch_assoc() and you'll see the fields' names Link to comment https://forums.phpfreaks.com/topic/174229-loop-question/#findComment-922934 Share on other sites More sharing options...
dflow Posted September 22, 2009 Author Share Posted September 22, 2009 cool thanks Link to comment https://forums.phpfreaks.com/topic/174229-loop-question/#findComment-922940 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.