0

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

Community
  • 1
  • 1
Zaharadin Adamu
  • 15
  • 5

2 Answers2

0

That sample data was really helpful, I came up with this:

$query=mysqli_query($db_conx, "
 SELECT
`status`.id,
`status`.account_name,
`status`.author,
`status`.`data`
 FROM
`status`
LEFT JOIN friends AS acc_friends ON `status`.account_name IN (acc_friends.friend1, acc_friends.friend2)
LEFT JOIN friends AS au_friends ON `status`.author IN (au_friends.friend1, au_friends.friend2)
WHERE
'$logname' IN (acc_friends.friend1, au_friends.friend1,acc_friends.friend2, au_friends.friend2)
OR '$username' IN (acc_friends.friend1, au_friends.friend1,acc_friends.friend2, au_friends.friend2)
 ");

I missunderstood your needs at first, this time is pullign every record which is a friend from author OR account, it then goes thru that result searching for Banks or Doe (change them to logname and username) in the friends list because searching in account and author again is just redundant.

This query should do the trick :)

Solrac
  • 924
  • 8
  • 23
  • Thank you, let me get to my PC, I will try and let you know. – Zaharadin Adamu Oct 10 '16 at 17:52
  • I got the same output as the query I posted in the question. But I have added tables structures and more explanation to my question. Thank you. – Zaharadin Adamu Oct 10 '16 at 22:20
  • I updated the answer with a query that should solve this issue – Solrac Oct 11 '16 at 03:54
  • Am sorry for late reply, I have been trying to re-manipulate your code to see if I can achieve it, yet no success. – Zaharadin Adamu Oct 12 '16 at 10:01
  • yeah, you only had to change those 2... as if it will return lots of records just to show you whats the real relation between account->friends and author->friends you need to `GROUP BY id` it in order to narrow those results down. – Solrac Oct 12 '16 at 14:00
0

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
Mande Kira
  • 190
  • 1
  • 14