Query 2 tables in 1 While

[slj90]

I want to count the number of likes a status has. I have a 'likes' table seperate from the 'stream' table. For now I will keep it simple and only display the number of likes instead of displaying all the usernames that have liked it (even though the system already saves the usernames).

 

Here is the main query:

//query the database
$query = mysql_query("SELECT `stream_id`, `stream_username`, `stream_status`, `stream_date` FROM stream ORDER BY stream_id DESC LIMIT 50");


//fetch the results /
echo "<hr><br />";
WHILE($rows = mysql_fetch_array($query)):

$name= $rows['stream_username'];
$status = $rows['stream_status'];
$statustime = $rows['stream_date'];
$time =  strtotime($statustime);
$thisid = $rows['stream_id'];

echo $name . $status . timeAgo($time) . " ago"
echo "<br /><hr><br />";
endwhile;

And here is one which counts the number of likes 

$likesresult = mysql_query("SELECT likes_location_id FROM likes WHERE likes_location_id='$thisid'"); 
$likescount = mysql_num_rows($likesresult); 

So how would I go about merging the two together and displaying '$likescount' after '$status'?

Thanks,

 

[Jacques1]

You've asked pretty much the same question a couple of months ago, and the answer is still the same: You use an SQL join.

 

It might actually be a good idea to spend some time on the basics of PHP and SQL. That code is ... weird. I see PHPHTML spaghetti code, ancient functions (mysql_query() etc.), odd syntax (like the while statement), useless code (like stuffing all contents of the row into separate variables) and of course bad practices like dumping raw variables into SQL queries. This is not how PHP works. 

Edited December 9, 2014 by Jacques1

[maxxd]

This is not how PHP works. 

 

As one who's been dealing with WordPress for the last several months, unfortunately PHP can work like this.

 

The thing is, it really shouldn't.

[slj90]

Ok. When I get time I will do as you say and spend some time on the basics of PHP and SQL. I'm not familiar with mysqli etc.
 

Anyway, I've put this together for now:

$sql = 'SELECT s.stream_id, s.stream_status,   
    l.likes_location_id                                     
FROM stream s
LEFT JOIN likes l ON l.likes_location_id = s.stream_id   
ORDER BY s.stream_id DESC';

$result = mysql_query($sql);
while($row = mysql_fetch_array($result))
{
 $status= $row['stream_status'];
$likecount= mysql_num_rows($result); 

echo $status . "Likes: " . $likecount . "<br><br>";
}

It displays the status, but it only display the ID number of the 'like'. How do I get it to show the total number/count?

Thanks,

Edited December 10, 2014 by slj90

[slj90]

Sorry it displays the row count for the stream table not the ID.

[maxxd]

You shouldn't even be getting the ID of the 'like', you should be getting a total count of stream records that have associated like records, and it should be the same for every iteration of the loop unless I'm too tired to read your code correctly. mysql_num_rows returns the number of records returned in the set. To get a breakdown of likes per stream, look up COUNT() and GROUP BY MySQL clauses.

[hansford]

I haven't used this old deprecated code in years, but I'm going to try and take a crack at it off the top of my head.

$result = mysql_query($sql);
$likecount = mysql_num_rows($result);

while($row = mysql_fetch_array($result))
{
 $status = $row['stream_status']; 

echo $status . 'Likes: ' . $likecount . '<br /><br />';
}

Learn the new stuff because we can assist you better and because, one day, you're code will magically just stop working on any server. And that time is coming faster than you think.

Edited December 10, 2014 by hansford

[slj90]

Thanks,

I've managed to get the outcome I was looking for using the following SQL

 

SELECT s.*, COUNT(l.likes_location_id) AS likeCount
 FROM stream AS s
  LEFT JOIN likes AS l ON ( l.likes_location_id = s.stream_id )
 GROUP BY s.stream_id