Rickie W Rickie W - 1 year ago 45
PHP Question

How to display only rows that match values in another table

Okay, afraid of being bashed on for not searching first. I have spent about 3 nights trying to figure it out, but everything I do either returns with 500 error or script process half way. Finally registered to ask for help!

I run a game server, and on the website I have a Top 30 leaderboard which is working wonders (found the code directly off another topic here), the issue I'm having is not being able to use the JOIN function that everybody is suggesting in order to prevent the ADMIN's characters from being listed as well.

Here's the code that I have on my website right now, it shows the rank number 1-30, character name and the level in a table. Here's it working on my website

$stmt = $dbh->prepare('SELECT * FROM chars ORDER BY CAST(experience AS UNSIGNED ) DESC LIMIT 30;');
$result = $stmt->fetchAll();
$place = 1;

echo '<table class="justShowme" style="width:600px;height:150px;">

<td>Character Name</td>

foreach ($result as $index => &$item) {

$exp = floor(pow($item['experience'] + 1, 1/4));
$name = $item['name'];

echo '<tr>';
echo "<td><B>" . $place++ . "</B></td>";
echo "<td>" . $name . "</td>";
echo "<td>" . $exp . "</td>";
echo '</tr>';
echo '</table></center>';

I'm not very familiar with MySQL, so I'll just start by listing out what I know is necessary...

  1. 'chars' table includes the character information

    'sID' column is unique and matches the subscriber 'ID' column, whoever owns the character

  2. 'subscriber' table includes the account information and admin status

    'ID' is the subscriber ID which the 'sID' from chars table refers to
    'admin' is the admin status of the account as Y or N

If a character has an sID value of a subscriber ID with the admin value as Y, it should not be listed.

If the character has an sID value of a subscriber ID with the admin value N, it will be listed and the table is listed as DESC and only show 30 rows of results.

How would I go about doing this?

Any help would be greatly appreciated! This is my first post, so tips on future help requests would be nice too :) Thank you in advance!

Answer Source
SELECT tb1.*
    FROM chars tb1
    JOIN subscriber tb2
    ON tb1.sID=tb2.ID
    WHERE admin = 'N'
    LIMIT 30;