Displaying last 'N' posts by friends & me | PHP-MySQL

[gdebojyoti]

Hello.

I want to display the latest 'N' posts made by my friends and me in my home page. Now there are 2 tables in the database related to this.

One of them is "friends" (list of members who are my friends can be obtained from this table). The other is "updates" (all the status updates are stored in this table).

The common field to both the tables is "memberID" (a unique number allotted to each member of the site).

So how do I obtain and print out the required information from these 2 tables?

I hope my question is clear. Do let me know if I need to clarify further.

By the way, I am developing the site using PHP (procedural programming only; no OOP), and I'm using MySQL database. Also, I would like the processing time and power required for running the script to be as low as possible, since I will be going for shared hosting.

 

[Hrishi]

I think you will need to use INNER JOIN method ON memberID (since that's common in both the tables) and publish the results ( array of result_table.last_post & result_table.username ) from the resultant table.

Not sure if it's the entire PHP code that you want or is it just the technique.

 

[krishnandu.sarkar]

You need to use limit based on UpdateTime column.

Eg. SELECT TOP 10 FROM tblUpdate ORDER BY UpdateTime DESC (This is for SQL Server, MySQL uses Limit.)

 

[gdebojyoti]

Re: Displaying last 'N' posts by friends & me | PHP-MySQL

Finally got it to work.

mysql_query("SELECT * FROM updates AS su
LEFT JOIN friends AS fr ON fr.frContactID = su.authorID
JOIN (members AS u) ON (u.roll = su.authorID)
WHERE (fr.frHostID = 105090 or su.authorID = 105090)
GROUP BY su.statusID ORDER BY su.statusID DESC LIMIT 0,5");

By the way, "members" is a 3rd table from where I can pull data such as name and profile link.