1

I have two tables that share the same row of userid "$u". Table (users) contains all pertinent info on the users, and table (friends) contains info on relationship between user1 and user2 and accepted or blocked friendship.

I want to join the rows of "firstname" and "lastname" from (users) table and add it to the WHERE condition (user1="username" OR user2="username" AND accepted="1") of the relationship from the (friends) table.

Then echo those results of that query into an Unordered Listview in my html. I need help on the proper syntax of putting this together. I am building a social-network and listing out "friends" of each user.

I have it mostly figured out, but it's not outputting anything...probably an error in my syntax...which is why I am posting here for assistance.

Here is my code:

<ul data-role="listview" data-autodividers="true">
<?php

include_once("php_includes/db_conx.php");

$sql = "SELECT * FROM users WHERE username='$u' AND activated='1
        INNER JOIN friends on users.id=friends.id 
        WHERE (friends.user1='$u' OR friends.user2='$u' AND friends.accepted='1')";
$query = mysqli_query($db_conx, $sql);
while ($row = mysqli_fetch_array($query, MYSQLI_ASSOC)) {

echo "<li><a href='user.php?u=".$u."'>". $row['firstname']. '&nbsp' .$row['lastname'] ."</a></li>"; 

?>
</ul>
user3188874
  • 11
  • 4
  • Does the query work if you use it outside of PHP? – Barmar Mar 16 '17 at 17:35
  • I don't think it's the whole problem, but your parentheses are wrong. It should be `WHERE (friends.user1='$u' OR friends.user2='$u') AND friends.accepted='1'`. See http://stackoverflow.com/questions/27663976/sql-statement-is-ignoring-where-parameter – Barmar Mar 16 '17 at 17:37
  • * Not sure what you mean by "work outside of php" as php is server-side code on mysql database...sort of has to be php? *I fixed the parentheses, but still no output. Just a list item with no data? – user3188874 Mar 16 '17 at 17:42
  • I mean try the query by hand, either from the `mysql` CLI program or with a tool like PhpMyAdmin. Make sure it returns what you want before worrying about how to format it in PHP. – Barmar Mar 16 '17 at 19:17
  • Okay I found my issue: I was not looping through the array...duh! Thank you guys for your help. Took me all day, but now I'm one step closer to goal! – user3188874 Mar 17 '17 at 03:54
  • Isn't `while ($row = mysqli_fetch_array($query, MYSQLI_ASSOC)` looping through the results? What array do you need to loop through? – Barmar Mar 17 '17 at 16:12

0 Answers0