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

Discussion in 'Scripts, 3rd Party Apps, and Programming' started by gdebojyoti.mail96, Jun 29, 2013.

  1. gdebojyoti.mail96

    gdebojyoti.mail96 Member

    Messages:
    89
    Likes Received:
    0
    Trophy Points:
    6
    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.
     
    Last edited: Jun 29, 2013
  2. essellar

    essellar Community Advocate Community Support

    Messages:
    3,295
    Likes Received:
    227
    Trophy Points:
    63
    Take a look at the MySQL IN, ORDER BY and LIMIT clauses. That will let you do something like:

    Code:
    SELECT <fields> FROM <table> WHERE <identifier> IN (<comma-separated list of identifiers>) ORDER BY <time> DESC LIMIT 0, <N>
    
     
  3. gdebojyoti.mail96

    gdebojyoti.mail96 Member

    Messages:
    89
    Likes Received:
    0
    Trophy Points:
    6
  4. gdebojyoti.mail96

    gdebojyoti.mail96 Member

    Messages:
    89
    Likes Received:
    0
    Trophy Points:
    6
    It is working okay as of now. I am currently using the following statement -

    Code:
    $myQuery = mysql_query("select * from statusUpdates where hostID in (select contactID from friends where hostID = '105086') order by statusID desc limit 0,5");
    while ($row = mysql_fetch_assoc($myQuery))
    {
        //echo $row['hostID'].$row['status'].$row['timeStamp'];
    }
    However, I also want to extract data such as profile_link, profile_picture etc from another table called "users".
    So how do I extract data from another table at the same time?



    I tried to solve the above mentioned problem using INNER JOIN. However I encounter a problem with 2 tables only when the name of the columns become common in both case, while the data is not. My code -

    Code:
    $myQuery = mysql_query("SELECT * FROM statusUpdates INNER JOIN(friends) ON((statusUpdates.hostID = friends.contactID OR statusUpdates.hostID = friends.hostID) AND friends.hostID = 105086) ORDER BY statusUpdates.statusID DESC LIMIT 0,5");
    Let me explain this further -
    Suppose I need to extract both table1.colA and table2.colA, whose values are different. So how do I print them in PHP. If their values had been same, I may have pulled them by $result['colA']. Alternatively, if the fields had different names (colA and colB respectively, for instance), I would have used $result['colA'] and $result['colB']. But that's not the case. So how do I print their values now? I tried using $result['field1']['colA']; but it threw an error. So what do I do now?

    I cannot use AS because according to MySQL docs, "it is not permissible to refer to a column alias in a WHERE clause because the column value might not yet be determined when the WHERE clause is executed". Seems like my own query isn't working with AS because of something similar to that.

    I hope all of this is making sense.
     
    Last edited: Jul 1, 2013
  5. gdebojyoti.mail96

    gdebojyoti.mail96 Member

    Messages:
    89
    Likes Received:
    0
    Trophy Points:
    6
    Okay, problem solved. First of all, I changed the names of the fields of my tables, making each one unique (although I would have preferred getting a solution without editing my database).

    Then I used this script to pull data from all 3 tables.

    Code:
    $myQuery = mysql_query("SELECT * FROM statusUpdates INNER JOIN(friends) ON((statusUpdates.authorID = friends.frContactID OR statusUpdates.authorID = friends.frHostID) AND friends.frHostID = 105090) INNER JOIN (users) ON (users.roll = statusUpdates.authorID) ORDER BY statusUpdates.statusID DESC LIMIT 0,5");
    while ($row = mysql_fetch_assoc($myQuery))
    {
        //echo $row['userName'].$row['profileLink'].$row['status'].$row['statusTimeStamp'];
    }
    
     

Share This Page