Jump to content
MakeWebGames

Sql Help


Recommended Posts

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>";
Link to comment
Share on other sites

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>";
Link to comment
Share on other sites

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


Link to comment
Share on other sites

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

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...