PHP How to query information needed from 3 tables in one select statement

Question

I have these 3 tables:

users table(PK user_id)

Fields: user_id, user_first_name, user_last_name, username, user_email...etc

pals table (FK user1_id, user2_id from PK user_id in users table)

pal_id    user1_id    user2_id          status        timestamp
7           98              97               0     2011-02-02 21:44:28
8           92              98               1     2011-02-04 08:06:00
9           95              92               0     2011-02-04 08:05:54
10         97              92               1     2011-02-04 08:05:28
11         97              95               1     2011-02-04 08:06:33

picture table (FK user_id from PK user_id in users table)

picture_id  picture_url             picture_thumb_url               user_id  avatar         timestamp
73      ../User_Images/66983.jpg    ../User_Images/Thumbs/66983.jpg     92     0    2011-02-03 21:52:02
74      ../User_Images/56012.jpg    ../User_Images/Thumbs/56012.jpg     92     0    2011-01-25 12:09:58
75      ../User_Images/58206.jpg    ../User_Images/Thumbs/58206.jpg     95     0    2011-01-22 22:12:35
76      ../User_Images/85428.jpg    ../User_Images/Thumbs/85428.jpg     98     0    2011-01-23 23:50:16
77      ../User_Images/42325.jpg    ../User_Images/Thumbs/42325.jpg     98     0    2011-01-24 00:11:15
78      ../User_Images/73154.jpg    ../User_Images/Thumbs/73154.jpg     98     1    2011-01-24 00:11:15
81      ../User_Images/92865.jpg    ../User_Images/Thumbs/92865.jpg     92     0    2011-01-31 18:24:34
82      ../User_Images/75611.jpg    ../User_Images/Thumbs/75611.jpg     92     0    2011-01-26 18:08:52
83      ../User_Images/74829.jpg    ../User_Images/Thumbs/74829.jpg     95     0    2011-02-01 20:48:48
84      ../User_Images/5987.jpg     ../User_Images/Thumbs/5987.jpg      92     1    2011-02-03 21:52:02

I am making a social networking site where I want to have a user's pals shown in a PHP generated table. I want to show pal thumbnails and other information below these thumbnails so that if you click on a thumbnail, it takes you to that user's profile.

From above, user 92 is pals with user 98 because they have confirmed friendship(shown as status = '1')

user1_id in the pals gets the user id of the initiator of the friendship. For pal_id=8, user 92 requested friendship, 95 confirmed it. Had user 95 requested friendship and it had been confirmed, user2_id would be reading 92. User 92 has another pal, user 97. 97 initiated the friendship.

Users can have pictures, stored in the picture table above. A user selects his avatar from his pictures...that is done by setting one of his picture.avatar = '1'.

Now, how will I make my PHP query to show pal thumbnails and info?

I have this so far(my desired results are way off!)

<?php require_once('Connections/connections.php'); ?>
<?php
//query username
$user_id = $_SESSION['UserSession'];
$user_id = mysql_real_escape_string($user_id);
mysql_select_db($database_connections, $connections);
$query_user_info = "SELECT username FROM users WHERE user_id='$user_id'";
$user_info = mysql_query($query_user_info, $connections) or die(mysql_error());
$row_user_info = mysql_fetch_assoc($user_info);

bla bla bla

<?php
while ($pal_no = mysql_fetch_assoc($pal_no_result))
{
    //get pal info
    $query_pal_info = "SELECT * FROM users INNER JOIN pals ON (pals.user1_id = users.user_id) OR (users.user_id = pals.user2_id) 
    INNER JOIN picture ON picture.user_id = users.user_id AND picture.avatar = '1' WHERE users.user_id = '$user_id'";
    $pal_info = mysql_query($query_pal_info , $connections) or die(mysql_error());
    $totalRows_pal_info = mysql_num_rows($pal_info);

    //echo table with pal information
    echo "\n<table>";
    $j = 5;
    while ($row_pal_info = mysql_fetch_assoc($pal_info))
    {
        if($j==5) echo "\n\t<tr>";
        $thumbnail_user = $row_pal_info['picture_thumb_url'] != '' ? $row_pal_info['picture_thumb_url'] : '../Style/Images/default_avatar.png';
        echo "<td width='100' height='100' align='center' valign='middle'><a href = 'user_view.php?user_id2={$row_pal_info['user_id']}'>
        <img src='/NNL/User_Images/$thumbnail_user' border='0'/></a></td>\n";
        $j--;
        if($j==0) {
        echo "\n\t</tr>\n\t<tr>";
        $j = 5;
        } 
    }
    if($j!=5) echo "\n\t\t<td colspan=\"$j\"></td>\n\t</tr>";
    echo "\n</table>";
}
?>

Ze problem iz here:

$query_pal_info = "SELECT * FROM users INNER JOIN pals ON (pals.user1_id = users.user_id) OR (users.user_id = pals.user2_id) 
    INNER JOIN picture ON picture.user_id = users.user_id AND picture.avatar = '1' WHERE users.user_id = '$user_id'";

How do I fix it? Thanks in advance guys.

Answer 1

This is my final answer! And this is working perfectly!!

class MySQLDatabase{
    private  $hostname = 'localhost';
    private  $username = 'root';
    private  $password = 'password';
    private  $dbname = 'test';
    private $result;

    private $connection;

    function __construct()
    {
        $this->connect();
    }

    public function connect()   
    {
        $this->connection = mysql_connect($this->hostname, $this->username, $this->password);
        if (!$this->connection)
            die(mysql_error());         

        if (!mysql_select_db($this->dbname))
            die(mysql_error());
    }

    public function query($sql)
    {
        if(!$this->connection)
            $this->connect();
        $this->result = mysql_query($sql);      
        if(!$this->result)
            die(mysql_error());
        return $this->result; 
    }

    public function numOfRows()
    {
        return mysql_num_rows($this->result);
    }

    public function fetchArray($resultset)
    {
        return  mysql_fetch_assoc($resultset);
    }
}

$mysqldb = new MySQLDatabase();

$userid = 92;


$sql1 = "SELECT pals.user2_id AS pals_id1, users.user_first_name AS pals_first_name, ".
    " users.user_last_name AS pals_last_name, picture.picture_thumb_url AS ".
    "  picure, picture.avatar AS avatar FROM pals INNER JOIN (users LEFT JOIN picture ".
    " on picture.user_id = users.user_id) ON users.user_id = pals.user2_id WHERE pals.user1_id".
    " =".$userid." AND picture.avatar = 1 GROUP BY pals_id1;";

$palinfo1 = $mysqldb->query($sql1);


$sql2 = "SELECT pals.user1_id AS pals_id1, users.user_first_name AS pals_first_name, ".
    " users.user_last_name AS pals_last_name, picture.picture_thumb_url AS picure, picture.avatar".
    " AS avatar FROM pals INNER JOIN (users LEFT JOIN picture on picture.user_id = users.user_id)".
    " ON users.user_id = pals.user2_id WHERE pals.user2_id = ".$userid." AND picture.avatar = 1 GROUP BY pals_id1;";
$palinfo2 = $mysqldb->query($sql2);


echo "<table>";

while($palinfo = $mysqldb->fetchArray($palinfo1)){
    echo "<tr>";

    foreach($palinfo as $info => $value){
        echo "<td>$value</td>";
    }

    echo "</tr>";
}

while($palinfo = $mysqldb->fetchArray($palinfo2))
{
    echo "<tr>";

    foreach($palinfo as $info => $value){
        echo "<td>$value</td>";
    }

    echo "</tr>";
}


echo "</table>";

And also you need at least two queries to display all friend list. Best of luck with your project.