Canjucks Posted November 11, 2008 Posted November 11, 2008 I want to display all fields from the court table. What is doing is displaying a user but it isn't the last user to go through the courts. I have played around with the sql as far as I can go. I have had some interesting results but only ever one field. It is the last thing I need to get working and as many issues I have had are fixed. Please help me with some suggestions as I truly have run outof ideas for the minute :) Here's the annoying code: $q=$db->query("SELECT * FROM court WHERE courtVERDICT>0 ORDER BY courtID DESC"); while($case=$db->fetch_row($q)) { $q=$db->query("SELECT * FROM users WHERE userid={$case['courtID']}"); $user=$db->fetch_row($q); if($case['courtVERDICT'] == 1) { $outcome="Not Guilty"; } if($case['courtVERDICT'] == 2) { $outcome="Guilty"; } echo "<tr> <td>{$user['username']} [{$user['userid']}]</td> <td>{$case['courtREASON']}</td> <td>$outcome</td> <td>{$case['courtVDAYS']}</td> </tr>"; } echo "</table>"; Quote
Isomerizer Posted November 11, 2008 Posted November 11, 2008 Re: Sql Help Try this out... $q = $db->query("SELECT `courtREASON`, `courtVDAYS` FROM `court` WHERE `courtVERDICT` > 0 ORDER BY `courtID` DESC"); while($case = $db->fetch_array($q)) { $q=$db->query( sprintf("SELECT `username`, `userid` FROM `users` WHERE `userid` = %u", $case['courtID']) ); $user = $db->fetch_object($q); $outcome = ($case['courtVERDICT'] == 1) ? 'Not Guilty' : 'Guilty'; echo " <tr> <td>".$user->username." [".$user->userid."]</td> <td>".$case['courtREASON']."</td> <td>".$outcome."</td> <td>".$case['courtVDAYS']."</td> </tr>"; } echo "</table>"; Quote
Canjucks Posted November 11, 2008 Author Posted November 11, 2008 Re: Sql Help I do appreciate your help but this is the error i get: Fatal error: Call to undefined method database::fetch_array() on line 15 any ideas? Quote
AlabamaHit Posted November 12, 2008 Posted November 12, 2008 Re: Sql Help Try this. $q = $db->query("SELECT `courtREASON`, `courtVDAYS`, `courtVERDICT` FROM `court` WHERE `courtVERDICT` > 0 ORDER BY `courtID` DESC"); while($case = $db->fetch_row($q)) { $q=$db->query( sprintf("SELECT `username`, `userid` FROM `users` WHERE `userid` = %u", $case['courtID']) ); $user = $db->fetch_object($q); $outcome = ($case['courtVERDICT'] == 1) ? 'Not Guilty' : 'Guilty'; echo " <tr> <td>".$user->username." [".$user->userid."]</td> <td>".$case['courtREASON']."</td> <td>".$outcome."</td> <td>".$case['courtVDAYS']."</td> </tr>"; } echo "</table>"; Quote
Canjucks Posted November 12, 2008 Author Posted November 12, 2008 Re: Sql Help thank you for the help. What I ended up doing was making a new field and making this a primary key with auto increments. And then in the first query instead of sorting by court ID i sorted by court KEY (primary key) and this seems to have worked. But will give your suggestion a go Alabama just because you took the time to try and help me out :) Quote
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.