Using two SQL tables to pull information from both

Question

I need some help. I am attempting to pull a few bits of information from two tables within the same database.

I want to pull the user_id from activity_points and match it with user_id from user. When it has matched them I want it to display the full_name for that user.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<?php 

$db_host = "localhost";
$db_username = "******"; 
$db_pass = "*********";
$db_name = "******";

mysql_connect("$db_host","$db_username","$db_pass") or die(mysql_error());

mysql_select_db("$db_name") or die("no database by that name");

?>
<?php
$staticList = "";
$sql = mysql_query("SELECT * FROM phpfox_user_activity WHERE activity_points ORDER BY activity_points DESC LIMIT 10");
$sql2 = mysql_query("SELECT * FROM phpfox_user");
$peopleCount = mysql_num_rows($sql); // count the output amount
if ($peopleCount > 1) {
while($row = mysql_fetch_array($sql)){ 
             $user_id = $row["user_id"];
             $activity_points= $row["activity_points"];
             $activity_total= $row["activity_total"];

//$user_id = $user_id = $full_name 
$account = mysql_num_rows($sql2); // count the output amount
if ($account > 0) {
while($row = mysql_fetch_array($sql2)){
        $user_id1 = $row["user_id"];
        $full_name = $row["full_name"];
}
if ($user_id == $user_id1){

$staticList .= '<table width="100%" border="1" cellspacing="0" cellpadding="6">
        <tr>
          <td width="17%" valign="top">' . $full_name . '</td>
          <td width="83%" valign="top">' . $activity_points . '<br /></td>
        </tr>
      </table>';
}
else{
echo "No Dice"; 
}
        }
    }
}

?>
</head>

<body>
<?php echo $staticList; ?>
</body>
</html>

Thank you!

I am going to start reading into this more :D – Ajsmut Mar 30 '15 at 08:46 — Ajsmut, Mar 30 '15 at 08:46

Markus · Accepted Answer · 2015-03-30T08:32:21.033

join both tables with the user_id like so

   SELECT 
      phpfox_user.full_name 
   FROM phpfox_user 
   INNER JOIN phpfox_user_activity ON phpfox_user.user_id = phpfox_user_activity.user_id 
   WHERE phpfox_user_activity.activity_points = ??
   ORDER BY phpfox_user_activity.activity_points DESC LIMIT 10

you probably need to specify the amount of acitivity points in the where clause too!

EDIT

for the php code, maybe something like so

$sql = mysql_query("SELECT phpfox_user.full_name, phpfox_user_activity.* FROM phpfox_user INNER JOIN phpfox_user_activity ON phpfox_user.user_id = phpfox_user_activity.user_id WHERE phpfox_user_activity.activity_points = ??  ORDER BY phpfox_user_activity.activity_points DESC LIMIT 10");
$peopleCount = mysql_num_rows($sql); // count the output amount
if ($peopleCount > 1) {
while($row = mysql_fetch_array($sql)){ 
             $full_name = $row["full_name"];
             $user_id = $row["user_id"];
             $activity_points= $row["activity_points"];
             $activity_total= $row["activity_total"];

score 0 · Answer 2 · edited May 23 '17 at 10:24

You can use INNER JOIN to achieve your goal:
Why don't you have a binded variable/value in your first query when you are using WHERE? ...WHERE activity_points ORDER...
Use mysqli_* prepared statement instead of deprecated mysql_* to prevent SQL injections.

Your query would look like:

SELECT phpfox_user_activity.activity_points, phpfox_user_activity.activity_total, phpfox_user.full_name
FROM phpfox_user
INNER JOIN phpfox_user_activity ON phpfox_user.user_id = phpfox_user_activity.user_id
WHERE phpfox_user_activity.activity_points = '' /* PUT A VALUE INSIDE THAT CLAUSE */
ORDER BY phpfox_user_activity.activity_points DESC LIMIT 10

If you want to see a glimpse of prepared_statement, take this:

$db_host = "localhost";
$db_username = "******"; 
$db_pass = "*********";
$db_name = "******";

/* ESTABLISH CONNECTION */
$con = new mysqli($db_host, $db_username, $db_pass, $db_name);

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

if($stmt = $con->prepare("SELECT phpfox_user_activity.activity_points, phpfox_user_activity.activity_total, phpfox_user.full_name FROM phpfox_user INNER JOIN phpfox_user_activity ON phpfox_user.user_id = phpfox_user_activity.user_id WHERE phpfox_user_activity.activity_points = ? ORDER BY phpfox_user_activity.activity_points DESC LIMIT 10")){

  $stmt->bind_param("s",$variableToBindOnYourQuery); /* REPLACE NECESSARY VARIABLE TO BE BIND IN YOUR WHERE CLUASE */
  $stmt->execute();
  $stmt->bind_result($activity_points,$activity_total,$full_name); /* BIND RESULT TO THIS VARIABLES */
  while($stmt->fetch()){

    ?>
    <table width="100%" border="1" cellspacing="0" cellpadding="6">
      <tr>
        <td width="17%" valign="top"><?php echo $full_name; ?></td>
        <td width="83%" valign="top"><?php echo $activity_points; ?><br /></td>
      </tr>
    </table>
    <?php

  } /* END OF WHILE LOOP */
  $stmt->close();
} /* END OF PREPARED STATEMENT */

Using two SQL tables to pull information from both

2 Answers2