Showing Last 5 'entires' into my Table

OddLaW · #1 (**permalink**) 02-15-05, 19:55

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!!

r937 · #2 (**permalink**) 02-15-05, 20:16

1. why do you want the 2nd one hidden? that makes no sense

2. what version of mysql are you on?

OddLaW · #3 (**permalink**) 02-15-05, 20:58

r937, Sorry it was a typo. I'm running MySQL 4.0.22-standard

Please help!

r937 · #4 (**permalink**) 02-15-05, 21:29

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
;

OddLaW · #5 (**permalink**) 02-15-05, 21:50

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.

r937 · #6 (**permalink**) 02-15-05, 22:06

i gave you the solution for 4.0 -- "a bit slower but it works"

OddLaW · #7 (**permalink**) 02-15-05, 23:09

I get this error:

Quote:

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

r937 · #8 (**permalink**) 02-15-05, 23:22

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

OddLaW · #9 (**permalink**) 02-15-05, 23:36

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.

r937 · #10 (**permalink**) 02-15-05, 23:40

looks okay to me (but what do i know, i don't do php)

OddLaW · #11 (**permalink**) 02-15-05, 23:48

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?

r937 · #12 (**permalink**) 02-15-05, 23:59

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

OddLaW · #13 (**permalink**) 02-27-05, 10:54

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!!!

Internet Services

Web Development

Digital Marketing

Consumer Tech