load posts from each friend

Question

I'm currently working on a "news feed" type of script and I am trying to load the posts created by the user's friends. My current code partially works; it only loads from one friend and finishes. What am I doing wrong?

<?php

$infofriends = mysql_fetch_array(mysql_query("SELECT * FROM btfriend WHERE `user_id`='".$infousr['auto']."'"));
$infofrnd = mysql_fetch_array(mysql_query("SELECT * FROM btaccs WHERE `auto`='".$infofriends['friend_id']."'"));
$posts = mysql_query("SELECT * FROM btpost WHERE `user`='".$infofrnd['user']."' ORDER BY `auto` DESC") or die('Error: '.mysql_error());


while($row = mysql_fetch_array( $posts )) {
$infobeer = mysql_fetch_array(mysql_query("SELECT * FROM btbeer WHERE `beer`='".$row['beer']."'"));
$infouser = mysql_fetch_array(mysql_query("SELECT * FROM btaccs WHERE `user`='".$row['user']."'"));
....

(Currently sloppy, I'll be editing that afterwards).

MySQL structures:

btfriend

mysql> DESCRIBE btfriend;
+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| auto      | int(11) | NO   | PRI | NULL    | auto_increment |
| user_id   | int(11) | YES  |     | NULL    |                |
| friend_id | int(11) | YES  |     | NULL    |                |
+-----------+---------+------+-----+---------+----------------+

btaccs

mysql> DESCRIBE btaccs;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| auto      | int(11)      | NO   | PRI | NULL    | auto_increment |
| user      | varchar(150) | YES  |     | NULL    |                |
| display   | varchar(150) | YES  |     | NULL    |                |
| pass      | varchar(250) | YES  |     | NULL    |                |
| email     | varchar(150) | YES  |     | NULL    |                |
| firstname | varchar(150) | YES  |     | NULL    |                |
| lastname  | varchar(150) | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

btpost

mysql> DESCRIBE btpost;
+---------+---------------+------+-----+---------+----------------+
| Field   | Type          | Null | Key | Default | Extra          |
+---------+---------------+------+-----+---------+----------------+
| auto    | int(11)       | NO   | PRI | NULL    | auto_increment |
| user    | varchar(500)  | YES  |     | NULL    |                |
| beer    | varchar(911)  | YES  |     | NULL    |                |
| img     | varchar(30)   | YES  |     | NULL    |                |
| rate    | varchar(10)   | YES  |     | NULL    |                |
| loc     | varchar(1000) | YES  |     | NULL    |                |
| comment | varchar(1500) | YES  |     | NULL    |                |
| fb      | varchar(10)   | YES  |     | NULL    |                |
| type    | int(2)        | YES  |     | NULL    |                |
+---------+---------------+------+-----+---------+----------------+

It'd be great if someone could help!

Answer 1

Try this with using single query with join

SELECT p.* 
FROM btpost p
INNER JOIN btfriend f ON (p.`user` = f.friend_id)
WHERE f.user_id  =$infousr['auto']

Then loop through all the results from query,this will give you all the posts where btpost's user is equal to the friend's id btfriend and these are the friends of your given user id $infousr['auto'] I assume $infousr['auto'] will have the user id

Answer 2

First of all, you're using mysql_* functions; those have been deprecated, and will stop working at some point in the future. Look at switching to mysqli_ or PDO instead - they both make it easier to write safer code.

Secondly, you're calling your code in such a way that you're expecting it to work:

$infofriends = mysql_fetch_array(mysql_query("SELECT * FROM btfriend WHERE `user_id`='".$infousr['auto']."'"));

If the query fails, you'll be passing a boolean false to mysql_fetch_array; it's a lot easier to make each statement one at a time, and handle errors as they come up:

$friendset = mysql_query("SELECT * FROM btfriend WHERE `user_id`='".$infousr['auto']."'") or die (mysql_error());
$infofriends = mysql_fetch_array($friendset) or die (mysql_error());

That will generate an error on the appropriate line if something goes wrong - it's a little more code, but it's much easier to debug and maintain.

Finally, your actual question; you're only getting one friend, because you're only calling mysql_fetch_array() once on the friend query; that will return to the top row. A quick solution would be to loop through the results separately to generate a list, and then pass that into the second query:

$friendset = mysql_query("SELECT * FROM btfriend WHERE `user_id`='".$infousr['auto']."'") or die (mysql_error());

$friendArray = array();

while ($infofriends = mysql_fetch_array($friendset)) {
    $friendArray[] = $infofriends['friend_id'];
}
$friendArray[] = $infousr['auto'];

// At this point, you have an array of friend IDs.

$posts = mysql_query("SELECT * FROM btpost INNER JOIN btaccs ON btpost.user=btaccs.user WHERE btaccs.auto IN (" . implode(',', $friendArray) . ") ORDER BY btpost.auto DESC") or die('Error: '.mysql_error());

Note that it's using an IN to retrieve all the IDs at once; you might want to add an extra parameter to the ORDER BY to arrange the posts by friend.

Note that I've not tested this, so there may be issues with the syntax, but I hope it's enough for you to get the general idea.

-- We solved this in the chat and have added $friendArray[] = $infousr['auto']; which would also include the posts from the current user. The current user's ID, along with the friend IDs, would then go into the implode function in the query. We also linked the btpost and btaccs tables because btaccs held the user's ID, while btpost held the username. Full chat transcript -alexpja

Answer 3

I think you're missing a loop. Let's break down your code:

<?php

// Here, you run a query that presumably returns multiple rows, but you're only looking at the first row:
$infofriends = mysql_fetch_array(mysql_query("SELECT * FROM btfriend WHERE `user_id`='".$infousr['auto']."'"));
// That gave you a single array of the first friend that MySQL found

// Now, you take the `friend_id` field from that single result, and you run it against the `btaccs` table to get some more information:
$infofrnd = mysql_fetch_array(mysql_query("SELECT * FROM btaccs WHERE `auto`='".$infofriends['friend_id']."'"));
// Once again, you have a single row at this point. (Although here, I'm assuming that's ok, since a user's ID probably appears only once in this table

// Now, you take the single user that you've looked up, and you find posts associated with that user:
$posts = mysql_query("SELECT * FROM btpost WHERE `user`='".$infofrnd['user']."' ORDER BY `auto` DESC") or die('Error: '.mysql_error());

I think you can see that it's the first statement that limits it to a single user. Try creating an array before running that statement, and then looping through your result set, adding to that array.

A few other things I'd seriously consider:

1) Take advantage of PHP's double-quote string substitution...

mysql_query("SELECT * FROM btfriend WHERE `user_id`='{$infousr['auto']}'"

is a little easier to read than

mysql_query("SELECT * FROM btfriend WHERE `user_id`='".$infousr['auto']."'"

2) Read up on JOINs in MySQL. Everything you've done here can actually be collapsed into a single query in MySQL. It'd be a little too much to go into detail here, but you can start with the MySQL docs: http://dev.mysql.com/doc/refman/5.0/en/join.html