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

Answer 1

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

Sign up

Here is
`status table`

Here is
`friends table`