SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict AdRock952's Avatar
    Join Date
    Aug 2006
    Posts
    243
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    getting the id of the last record

    I'm in the middle of creating a small message board but have hit a brick wall.

    I can output, the subject, the author, the number of replies but what I can't do is get the author of the last post.

    How can i get I either add to my query to get the author of the last post or create another query to get this info and put it into the table

    I have tried using last_insert_id() and mysql_insert_id() but that always returns 0

    PHP Code:
    $sql="SELECT t.topicname, t.author, count( message ) AS message
        FROM topics t INNER JOIN messages m ON m.topicid = t.topicid INNER JOIN boards b ON b.boardid = t.boardid WHERE b.boardid = 
    $id  GROUP BY t.topicname";

    // Perform a query getting back a MySQLResult object
    $result $db->query($sql);

    $err $result->size();

    // Include the header html
    require_once("header.inc.php");

    if(
    $err !=0) {
        echo 
    '<table id="boards">
            <tr>
                <th class="thTopic">Topic</th>
                <th class="thAuthor">Author</th>
                <th class="thReplies">Replies</th>
                <th class="thInfo">Last Post</th>
            </tr>'
    ;
        while (
    $row $result->fetch()) {
            
    $topic ucwords($row['topicname']);
            
            echo 
    "<tr><td class='tdTopic'><a href=''>{$topic}</a><br /></td>";
            echo 
    "<td class='tdAuthor'>{$row['author']}<br /></td>";
            echo 
    "<td class='tdReplies'>{$row["message"]}</td>";
            echo
    " <td class='tdInfo'></td></tr>";
        }
        echo 
    '</table>'

  2. #2
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Off the top of my head, try using MAX() in the MySQL query. mysql_insert_id() is, as you found out, for INSERT statement only.

    PHP Code:
    $sql="SELECT t.topicname, t.author, count( message ) AS message
    FROM topics t INNER JOIN messages m ON m.topicid = t.topicid INNER JOIN boards b ON b.boardid = t.boardid WHERE b.boardid = 
    $id AND t.authorid = (SELECT MAX(authorid) FROM topics) GROUP BY t.topicname"

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    sorry, spiderling, MAX(authorid) FROM topics is just the newest guy who made a post

    author of the last post requires an additional subquery, but this is fine because it also allows doing the GROUP BY for the count of messages in that same subquery, making the outer query more efficient
    Code:
    SELECT t.topicname
         , g.msgs
         , m.authorname
      FROM boards AS b
    INNER 
      JOIN topics AS t 
        ON t.boardid = b.boardid
    INNER 
      JOIN ( SELECT topicid
                  , COUNT(*) AS msgs
                  , MAX(dateposted) AS lastpost 
               FROM messages 
             GROUP
                 BY topicid ) AS g
        ON g.topicid = t.topicid 
    INNER
      JOIN messages AS m
        ON m.topicid = t.topicid 
       AND m.dateposted = g.lastpost
     WHERE b.boardid = $id
    please notice also the sequence i wrote the 4 tables in the FROM clause

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My mistake. Somewhat of an assumption on my part.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    no, no, it was a good step, it showed you are thinking along the right lines
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •