CODEWITHSUNDEEP
phpmysqlhtml-tableecho
Mike Gracia
Mike Gracia

Reputation: 21

Displaying info from MySQL - from 2 different tables... Argh! Noob in trouble

I am trying to pull some stats from a gameserver database, and return them in a table.

I have managed to do the first bit - pulling 10 results, displaying in a table in html, HOWEVER... the next bit has me stumped... I for each player I want to get some info from another table...

Here is what I have so far... excuse the messy code, I am just learning!

// adding ALL info from the first 10 tables 'profile' based on humanity, ascending, to     the variable 'profile_info'

$profile_info = mysql_query("SELECT * FROM profile ORDER BY humanity desc LIMIT 10");

while($row = mysql_fetch_array($profile_info))
  {
// Below I add the players unique ID into the variable $unique, to be used later for         pulling their survival time from the 2nd table, which is called 'survivor' 

  $unique = $row['unique_id'];
  echo "<tr>";
  echo "<td class=\"c1\">" . $row['name'] . "</td>";
  echo "<td class=\"c2\">" . $row['humanity'] . "</td>";
  echo "<td class=\"c3\">" . $row['total_survivor_kills'] . "</td>";
  echo "<td class=\"c4\">" . $row['total_bandit_kills'] . "</td>";
  echo "<td class=\"c5\">" . $row['total_zombie_kills'] . "</td>";
  echo "<td class=\"c6\">" . $unique . "</td>";

//In the line below, I try to get data from the 2nd table (called survivor), checking   for the unique_id for the player (extracted from the first table, called 'profile') which is common across both tables and which have a 0 in the field 'is_dead'

 $result = mysql_query("SELECT * FROM `survivor` WHERE `unique_id` ='.$unique' AND     `is_dead` = 0") or die(mysql_error());

 echo $unique;

  if (mysql_num_rows($result)) {
    $survivors_survival_time = mysql_fetch_assoc($result);
      echo "<td class=\"c7\">" . $survivors_survival_time['survival_time'] . "</td>";

}

I hope that, even though the code above is probably rubbish, you can see what I am trying to do?

Most of it works fine, it is just that the part where I try to get the info for a player from the second table, based on their unique_id from their row in the first table, it doesn't work :(

Any ideas, or is the above so bad I should just give up?

Upvotes: 2

Views: 172

Answers (4)

Dave
Dave

Reputation: 3658

You can combine those queries using a join:

SELECT 
    * 
FROM 
    profile AS p
LEFT JOIN 
    survivor AS s ON p.unique_id = s.unique_id 
WHERE 
    s.is_dead = 0
ORDER BY 
    humanity DESC 
LIMIT 
    10

Then simply loop the results. Using LEFT JOIN gives you all the results from profile and any matches in survivor. If you change that to JOIN (i.e. drop the LEFT) it will give you only rows where a match exists in profile AND survivor.

A couple of suggestions:

  1. Explicitly state which columns you want, i.e. "SELECT name, humanity, survival_time, etc..." instead of SELECT *.
  2. Use a query method that allows you to use prepared statements, such as PDO.
  3. Use single quotes instead of doubles so that you don't have to escape all the doubles in your HTML output. Anyone else who reads your code will thank you for that!

Upvotes: 0

Juzt1s
Juzt1s

Reputation: 27

I do not know about MySQL, because I've always used MSSQL, but here's how I would write it in PHP and mssql:

'SELECT * FROM survivor WHERE unique_id="'.$unique.'" AND is_dead = 0'

Try it and let me know ;)

Upvotes: 0

newfurniturey
newfurniturey

Reputation: 38456

I believe you have a typo in your query that pulls info for each individual player here:

mysql_query("SELECT * FROM `survivor` WHERE `unique_id` ='.$unique' AND     `is_dead` = 0")

Specifically, the unique_id = '.$unique' part, where there is an extra . in the value field.

Try removing it to get the following:

$result = mysql_query("SELECT * FROM `survivor` WHERE `unique_id`='$unique' AND `is_dead` = 0") or die(mysql_error());

This, of course, is under the assumption that you don't prepend a . to each of your unique_id values in the survivor table.

Side-note (not answer specific):
If you were to update your code to use the MySQLi or PDO libraries opposed to the deprecated mysql_ functions, you would have the ability to use prepared statements. Using these would prevent minor errors like the one noted above as well as provide more-secure code too.

Upvotes: 2

Vince Lowe
Vince Lowe

Reputation: 3620

Nest your while loops or read about mysql LEFT JOIN and update your query.

Upvotes: 0

Related Questions