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? Quote Link to comment 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 Quote Link to comment 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); } Quote Link to comment 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? Quote Link to comment Share on other sites More sharing options...
RussellReal Posted September 15, 2009 Share Posted September 15, 2009 add it to the end Quote Link to comment 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); ?> Quote Link to comment 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' Quote Link to comment 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 Quote Link to comment 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). Quote Link to comment Share on other sites More sharing options...
dflow Posted September 17, 2009 Author Share Posted September 17, 2009 i still get that error Quote Link to comment 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. Quote Link to comment 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. Quote Link to comment 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 ? Quote Link to comment 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 Quote Link to comment Share on other sites More sharing options...
dflow Posted September 22, 2009 Author Share Posted September 22, 2009 cool thanks Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.