Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2005
    Location
    Oklahoma City, Oklahoma
    Posts
    39

    Unanswered: Showing Last 5 'entires' into my Table

    I want to show the last 5 entirees/posts/comments that was submitted into my Table, but when I use the the SQL I have right now it displays the Last 5 Comments because I DESC'ed it, and it Limits the comments by 5, the problem is that when I post a comment it puts the comment 'on the top' when displayed, and not the bottom.

    MY CODE:
    Code:
    SELECT * FROM comments WHERE business='$_GET[business]' ORDER BY id DESC LIMIT 0,5
    How it shows on my web page:

    COMMENTS:
    6th Comment Submitted
    5th Comment Submitted
    4th Comment Submitted
    3rd Comment Submitted
    2nd Comment Submitted
    1st Comment Submitted (hidden because Limit = 5)

    This is how I want it to show:

    COMMENTS:
    1st Comment Submitted (hidden because Limit = 5)
    2nd Comment Submitted
    3rd Comment Submitted
    4th Comment Submitted
    5th Comment Submitted
    6th Comment Submitted

    I've read on some tutorials that you can use PHP to reverse the order, but surly there is a way to due it via SQL?

    PLEASE HELP! THANK YOU in advance!!
    Last edited by OddLaW; 02-15-05 at 22:02.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1. why do you want the 2nd one hidden? that makes no sense

    2. what version of mysql are you on?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2005
    Location
    Oklahoma City, Oklahoma
    Posts
    39
    r937, Sorry it was a typo. I'm running MySQL 4.0.22-standard

    Please help!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you were on 4.1, i would suggest --
    Code:
    select * 
      from (
           select *
             from comments 
           where business = '$_GET[business]' 
           order by id desc 
           limit 0,5
           ) as derived_table
    order by id
    this will be a bit slower but it works --
    Code:
    create table hold_on_a_sec
    select * 
      from comments 
     where business = '$_GET[business]' 
    order by id desc 
    limit 0,5
    ;
    select * from hold_on_a_sec
    order by id
    ;
    drop table hold_on_a_sec
    ;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2005
    Location
    Oklahoma City, Oklahoma
    Posts
    39
    Thanks for your help, but could you help me upgrade to 4.1, or do you have a solution for 4.0?

    EDIT: If you don't have a solution for 4.0, I will just search google on tutorials on how to upgrade. Thanks though.
    Last edited by OddLaW; 02-15-05 at 22:54.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i gave you the solution for 4.0 -- "a bit slower but it works"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2005
    Location
    Oklahoma City, Oklahoma
    Posts
    39
    I get this error:

    Error! You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '; select * from hold_on_a_sec order by id ; drop table hold_on_a_sec

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    wow, that's weird

    how are you feeding those queries to mysql? some kind of odbc interface?

    because through a front end like phpmyadmin or mysql-front, there's no problem in passing in more than one statement separated by semicolons
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2005
    Location
    Oklahoma City, Oklahoma
    Posts
    39
    All I know is I'm on a Linux server. I posted my code from my web site before I made the changes. Here is my PHP / HTML / SQL code from my site:

    PHP Code:
    <?

    include('config.php');
    $cnx mysql_connect("$sqlConn""$sqlUser""$sqlPass");
    mysql_select_db("$sqlDB"$cnx);

    if(isset(
    $_POST[newcomment])){
    $date date('n-j-y');
    $query mysql_query("INSERT INTO comments
       (
         `user` ,
         `comment` ,
         `business`,
         `date`
       )
    VALUES
      (
         '
    $_POST[user]',
         '
    $_POST[comment]',
         '
    $_GET[business]',
         '
    $date'
      )"


    or die(
    'Insert a Comment.' mysql_error() );

    print(
    '');
    }

    $query mysql_query("SELECT * FROM comments WHERE business='$_GET[business]' ORDER BY id DESC LIMIT 0,5") or die('Error! ' mysql_error() );
    $numOfRows mysql_num_rows($query); 

    if(
    $numOfRows 1)
    {
    print(
    'No comments.'); print "some html";
    }

    while(
    $row mysql_fetch_array($query))
    {    
    print(
    "some html");
    }
        
    ?>
    EDIT: I removed the HTML to clean up the code to make it easier to read for you.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    looks okay to me (but what do i know, i don't do php)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2005
    Location
    Oklahoma City, Oklahoma
    Posts
    39
    Hey, thanks for replying so quickly to my posts. You're the best help I've ever seen!

    Do you have any suggestons, maybe use INTERSECT, or UNION instead of a semi-colons?

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if php is as easy as coldfusion (and i have no reason to suspect it isn't), you really should be able to sort 5 rows back into ascending sequence with a small piece of code

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2005
    Location
    Oklahoma City, Oklahoma
    Posts
    39

    Hi!

    Sorry it took so long to reply. I've been focusing my attention on another part of my code and had'nt realized you replied!

    :-D

    I'll give the PHP code a try, I'm sure it will work!!!

Posting Permissions

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