MySQL Select data from table1 based on table2

Question

I want to SELECT data FROM status table WHERE account_name or author is either $logname or $username and if account_name or author are friends from friends table

Here is `status table`

data account_name author

1 Hello John John

2 Am good John Doe

3 Please Doe James

4 Who is? James Smith

5 Hmmm John Williams

6 Hell Banks James

Here is `friends table`

user1 user2

John Doe

James Doe

Smith James

Williams John

Banks James

What I wanted to do is to be able to SELECT all data from status table where account_name and author is John or Doe or John's friends i.e Williams.

So the output of the query when $username="Doe" and $logname="John" should be 1, 2, 3 and 5, but when $username="Doe" and $logname="Doe" should be 1, 2, 3, 4, 5 and 6.

Here is what I have tried so far, but am getting all the results from status or just the result where only account_name or author is either $logname or $username.

$username = "Doe";
$logname = "John"; 

$query=mysqli_query($db_conx, "SELECT s.id, s.account_name, s.author, s.data, s.postdate  FROM status s INNER JOIN  friends f ON f.user1='$username' OR f.user2='$username' WHERE s.account_name='$username' OR s.author='$username' OR s.account_name='$logname' OR s.author='$logname' GROUP BY s.id ORDER BY s.postdate DESC");


    //I have also tried these two queries but not giving me what I want

//$query = mysqli_query($db_conx, "SELECT * FROM status WHERE account_name = '$username' OR author = '$username' ORDER BY postdate DESC");
    $num_row = mysqli_num_rows($query);
    echo " Numbers ".$num_row;
    //$query =mysqli_query($db_conx, "SELECT s.* , f.* FROM status s, friends f WHERE s.account_name='$user' AND f.user1='$user' OR f.user2='$user'");


    while($row=mysqli_fetch_array($query))
    {


?>
    <tr>
    <td><p><?php echo $row['data']; ?></p></td>
    <td><p><?php echo $row['id']; ?></p></td>
    <td><p><?php echo $row['author']; ?></p></td>
    <td><p><?php echo $row['account_name']; ?></p></td>
    </tr>


<?php   
    }

What I have checked so far, please any help will be appreciated.

Two mysqli queries

Difference between left join and right join in SQL Server

How can an SQL query return data from multiple tables

Select results from table1 based on entries on table2

Mande Kira Mande Kira · Accepted Answer · 2016-10-12T10:33:18

It seems you are trying to SELECT record of friends of friend when posted on the friend or a friend posted on his friend? Similar to a facebook feed. I dont know if there is a better way of doing this, but you can try the code bellow, need to improve it or someone can improve it here.

$query = mysqli_query($db_conx, "SELECT * FROM friends WHERE user1='kira' OR user2 = 'mandekira' OR user2='kira' OR user1 = 'mandekira'");
$num_row = mysqli_num_rows($query);
?>
<table>
<?php
while($row=mysqli_fetch_array($query))
{
    $sql = mysqli_query($db_conx, "SELECT * FROM status WHERE (account_name='".$row['user1']."' AND author='".$row['user2']."') OR (account_name='".$row['user2']."' AND author='".$row['user2']."') ORDER BY postdate DESC");
    while($row=mysqli_fetch_array($sql))
{
?>
    <tr>
    <td width="100"><?php echo $row['data']; ?></td>
    <td width="100"><?php echo $row['account_name']; ?></td>
    <td width="100"><?php echo $row['author']; ?></td>
    </tr>
<?php   
}
    }
?>
</table>
<?php

MySQL Select data from table1 based on table2

Here is status table

Here is friends table

2 Answers

Here is `status table`

Here is `friends table`