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.

if you are still having problem then you can mail me your database export sql file — S L
– S L, Commented Feb 4, 2011 at 11:10
@experimentX. Maybe I have redundant info in my database. You have an email address I can send it to? — Kinyanjui Kamau
– Kinyanjui Kamau, Commented Feb 4, 2011 at 14:03
but i am not sure i wil be able to reply today since it's 8 pm here and i am still in office — S L
– S L, Commented Feb 4, 2011 at 14:09
@experimentX. It is sent. Please reply when you have the time. Thanks and good weekend — Kinyanjui Kamau
– Kinyanjui Kamau, Commented Feb 4, 2011 at 14:31

S L · Accepted Answer · 2011-02-05 06:54:56Z

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.

edited Feb 5, 2011 at 6:54

answered Feb 4, 2011 at 6:01

S L

14.3k18 gold badges81 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

Kinyanjui Kamau Over a year ago

Thanks for the reply experimentX. I'll get back to you

Kinyanjui Kamau Over a year ago

ok. "SELECT pals.*, user_id AS user_id, users.user AS username, picture.picture AS picture FROM users INNER JOIN (pals LEFT JOIN picture ON pals.user2_id = picture.userid) ON user_id = pals.user1_id";............i get: Column 'user_id' in field list is ambiguous

S L Over a year ago

@Kinyanjui Kamau okay user1 i pal is our user an user2 is the pal of our user (so we are selecting pal's photo from picure) okay will update answer please check

Kinyanjui Kamau Over a year ago

when i try: "SELECT pals.*, users.user_id AS user_id, users.user_id AS username, picture.picture_id AS picture FROM users INNER JOIN (pals LEFT JOIN picture ON pals.user2_id = picture.user_id) ON users.user_id = pals.user1_id";.......i get:Notice: Undefined index: picture_thumb_url in C:\wamp\www\NNL\pals.php on line 130 many times

Kinyanjui Kamau Over a year ago

I did not define the 3 tables properly: users, pals and picture

|

Collectives™ on Stack Overflow

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

1 Answer 1

15 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

15 Comments

Your Answer

Sign up or log in

Post as a guest

Related