#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2001
    Location
    Canada / NZ / UK
    Posts
    73
    Rep Power
    18
    this is the discussion section again which is something like a simple forum. i've joined three tables to give me the results that I want but one problem remains.

    here is the statement:

    $sql = "SELECT a.article_no,a.author,a.heading,d.reply_by,d.added_date,d.added_time FROM mag_articles AS a,mag_discussion AS d WHERE a.article_no=d.article_no GROUP BY a.article_no ORDER BY d.added_date DESC,d.added_time DESC";

    when i get around to printing the rows in the table (such as last response, last reply time), it orders the responses correctly (in reverse order) but only from the first person to respond and ordered by the time of the first reply.

    for example:

    Article Replies Last Reply Date and Time
    Test 1 2 Universal Jess 21:25
    written by Universal Sea January 26th, 2001
    Test 2 4 Universal Sea 22:35
    written by Universal Jess January 25th, 2001
    Test 3 7 Universal Sea 19:13
    written by Universal Sea January 25th, 2001

    the "last reply" fields there are the names of the first person to reply to that article. the date and time listed are at the date and time of the first response.

    i realise there must be some form of LIMIT but not for the total number of articles discussed, but just for the last date, time and reply-by.

    i also devoured the mySQL manual and tried things like the MAX statement on the date and time which worked in that it printed out the latest time in the column for each article. as in, a reply at 22:30 yesterday would be printed before a reply at 20:00 tonight.

    ...

    i've just spent 30 more minutes toying with this - (many times i type up long posts and i exhaust every option before i actually press submit - 90% of the time i figure it out before).

    i removed the MAX statement and tried a negative limit, i think i am going in circles here.

    i figure i could redesign the table and put a timestamp in an INT field to organise things better, but the problem still remains of getting the last row's information.

    i know this is probably very simple and i apologise.
    Highest Leader of UM
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2001
    Location
    Canada / NZ / UK
    Posts
    73
    Rep Power
    18
    anybody, please?
    Highest Leader of UM
  4. #3
  5. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Caro, Michigan
    Posts
    14,946
    Rep Power
    4554
    I really don't understand what you're asking for. Sorry. What exactly do you want the query to return and how are your tables laid out?

    ---John Holmes...
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2001
    Location
    Canada / NZ / UK
    Posts
    73
    Rep Power
    18
    essentially its like a discussion forum similar to this board.

    here is the WHILE loop:

    $sql = "SELECT a.article_no,a.author,a.heading,d.reply_by,d.added_date,d.added_time FROM mag_articles AS a,mag_discussion AS d WHERE a.article_no=d.article_no GROUP BY a.article_no ORDER BY d.added_date DESC,d.added_time DESC";
    $result = mysql_query($sql);
    while($row = mysql_fetch_array($result)) {
    $article_url = $row["article_no"];
    $author = $row["author"];
    $article = $row["heading"];
    $reply_by = $row["reply_by"];
    $added_date = $row["added_date"];
    $added = explode("-", $added_date);
    $added = date('F jS, Y', mktime(0,0,0,$added[1],$added[2],$added[0]));
    $added_time = $row["added_time"];
    $added_time = substr($added_time,0,5);
    $sql = "SELECT * FROM mag_discussion WHERE article_no='$article_url'";
    $res = mysql_query($sql);
    $replies = mysql_num_rows($res);
    $disc_block .= "
    <tr>
    <td width=\"290\" valign=\"top\" bgcolor=\"#131313\">
    <a href=\"discussion.php?article=$article_url\" class=\"um\"><b>$article</b></a>
    </td>
    <td width=\"50\" valign=\"top\" align=\"center\" bgcolor=\"#232323\">
    <b>$replies</b>
    </td>
    <td width=\"150\" valign=\"top\" align=\"center\" bgcolor=\"#131313\">
    <b>$reply_by</b>
    </td>
    <td width=\"140\" valign=\"top\" align=\"center\" bgcolor=\"#232323\">
    <b>$added_time</b>
    </td>
    </tr>
    <tr>
    <td width=\"290\" valign=\"top\" bgcolor=\"#232323\">
    written by <b class=\"teal\">$author</b>
    </td>
    <td width=\"60\" valign=\"top\" bgcolor=\"#131313\">
    </td>
    <td width=\"150\" valign=\"top\" bgcolor=\"#232323\">
    </td>
    <td width=\"130\" valign=\"top\" align=\"center\" bgcolor=\"#131313\" class=\"time\">
    $added
    </td>
    </tr>
    ";
    }


    what i want to do is have the last time and the name of the last reply to be the last results printed. as it stands now, the "threads" are sorted in reverse order, but from the time of the very first reply in that thread. and the name printed is that of the first person to reply in that thread.

    the two tables in question are "mag_articles" and "mag_discussion", which are joined to reference the article being discussed.

    again, i think this is probably something very simple that i am overlooking. i wish there was a "LAST" command or something similar, as in "SELECT LAST(reply_by), LAST(added_date),LAST(added_time)," etc etc but still retaining the results.

    i have a feeling my problem may lie in the GROUP BY clause which i need to prevent multiple discussion replies showing up.

    thanks.
    Highest Leader of UM
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2000
    Posts
    34
    Rep Power
    18
    OK Universal Sea since you have been so helpful to me I did a little experiment.

    I figured out a Select statement that works on the following basis.

    There are 3 articles each with 3 comments (9 total) and the query pulls up only the latest comments (1 for each article)from 2 tables (adding a third table is no problem.)

    I have just uploaded my example here is the link :

    http://embassylinks.com/test/test.php

    if you give me a minute I will load a second example that calls all the comments under this link :

    http://embassylinks.com/test/test1.php

    I hope it is what you want.

    1st edit/addition .
    I also uploaded 2 additional queries that show the original tables which will give you a better idea of what I did.

    The links are :

    http://embassylinks.com/test/Publications.php

    http://embassylinks.com/test/Comments.php

    NB. I was responding from your first post - had a hell of a time understanding it so did not read your further posts before working all this out - just in case I'm on the wrong track.

    [Edited by ais on 01-27-2001 at 07:04 PM]
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2001
    Location
    Canada / NZ / UK
    Posts
    73
    Rep Power
    18
    thanks alot, ais.

    for your first example, you retrieved the very last time and comment author from the table?

    what was the query for that?
    Highest Leader of UM
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2000
    Posts
    34
    Rep Power
    18
    We must have cross posted, I was just trying to add it to my previous post, but it didn't work.

    Here it is :

    $sql = "SELECT Article, Responder, Comments, MAX(Date) AS Latest FROM Publications LEFT JOIN Comments ON Publications.Article=Comments.Article1 GROUP BY Article ORDER BY Latest DESC";

    Hope it helps - is it what you were trying to do?

    By the way I also uploaded queries for the full tables so you can see exactly what I did - see my previous post for the links.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2001
    Location
    Canada / NZ / UK
    Posts
    73
    Rep Power
    18
    hi ais....

    that looks like it could work. i also need to alter my database to replace the "date" and "time" fields and merge them. i have never done a left join before so i will learn this now and probably be back here tomorrow to see how it goes.

    i would test it right now but we are in the middle of a huge global webcast on http://www.universalmetropolis.com and my computer's RAM is not holding up the browser well at all itself.

    thanks much again, it is much appreciated. you should also post your main URL in your profile, i'm always curious to see the sites that other people on here are involved in.
    Highest Leader of UM
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2001
    Location
    Canada / NZ / UK
    Posts
    73
    Rep Power
    18
    wait two more things quickly -

    1) how do i call the columns in PHP now that you've joined things?

    is it something like:

    $sql = // what you showed me
    $result = mysql_query($sql);
    while($row=mysql_fetch_array($result)) {
    $article = $row["article"];
    $responder = $row["responder"];

    ///etc, etc


    or do i just call "latest"?

    i assume its the former, again i am itching to try it out but can't.

    2) please leave those links up there until tomorrow afternoon for me!!!

    cheers
    Highest Leader of UM
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2000
    Posts
    34
    Rep Power
    18
    $sql = "SELECT Article, Responder, Comments, MAX(Date) AS Latest FROM Publications LEFT JOIN Comments ON Publications.Article=Comments.Article1 GROUP BY Article ORDER BY Latest DESC";


    $result = mysql_query($sql,$db)
    or die (sprintf ("Cannot execute query [%s]: %s",
    mysql_errno (), mysql_error ()));

    echo "<table border=1>\n";
    echo "<tr><td><b>Article</b></td><td><b>Responder</b></td><td><b>Comments</b></td><td><b>Date</b></td></tr>\n";

    while ($item = mysql_fetch_array($result)) {


    printf("<tr><td> %s </td><td> %s </td><td> %s </td><td> %s </td></tr>\n", $item["Article"], $item["Responder"], $item["Comments"], $item["Latest"]);}

    echo "</table>\n";

    Just to be complete here is the rest of the script.

    You can either reference them direct like I did above or you can assign a variable as per your first example.

    2 things to keep in mind, the field "Date" in my example must now be referenced as $item["Latest"] and not $item["Date"] and if you have 2 colums named the same, the later column in the join takes precedence so avoid using same names.

    I will leave the samples up, just let me know when you're done.

    Hope it works for you.

    It's 06:00 am in the morning here, haven't been to bed yet - just watched Agassi win the Aus Open - so I'll toddle off to bed now.

    Cheers.
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2001
    Location
    Canada / NZ / UK
    Posts
    73
    Rep Power
    18
    thanks alot again......

    i will let you know how it goes tomorrow.
    Highest Leader of UM

IMN logo majestic logo threadwatch logo seochat tools logo