Zaharadin Adamu Zaharadin Adamu - 1 month ago 15
MySQL Question

MySQL Select data from table1 based on table2

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

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
Comments