Sql Help

[Canjucks]

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>";

[Isomerizer]

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>";

[Canjucks]

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?

[AlabamaHit]

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>";

[Canjucks]

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 :)