order by limit causes parse error, unexpected T_STRING

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rioguia
Forum Newbie
Posts: 4
Joined: Tue Nov 30, 2004 8:37 am
Location: www.substantis.com

order by limit causes parse error, unexpected T_STRING

Post by rioguia »

I am trying to adapt a shoutbox to display the most recent 5 posts in a column with the oldest at the top and the newest at the bottom (The original code displays the newest at the top).

To achieve my goal, i am trying to follow an example in the comments to the mysql manual which says as follows "you can use ORDER BY on a UNION that consists of only one SELECT (and thus doesn't actually include the word "UNION"). Suppose you want the last 5 entries in a table, but you want them in ascending order. You can use this query:

( SELECT * FROM table_name ORDER BY ranking DESC
LIMIT 5 ) ORDER BY ranking;


I am trying to use the above as an example. With the code as i have modified, I get the following error in /var/log/httpd/mysite.com
PHP Parse error: parse error, unexpected T_STRING in /var/www/html/vh07/oxyshout/oxyshout_view.php on line 251, referer: http://www.dexter1976.com/
line 251 starts as follows "$result = $oxyshout->query "

the original code was like this:

Code: Select all

$result = $oxyshout->query("SELECT user, contact, message, date, time, ip, session FROM ".$oxyshout->oxycfg['table_messages']." ORDER BY id DESC LIMIT ".$oxycfg["post_limit"]."");
$i = 0;
while($r = @mysql_fetch_array($result)) {


i have modified code:

Code: Select all

$result = $oxyshout->query("SELECT user, contact, message, date, time, ip, session FROM ".$oxyshout->oxycfg['table_messages']." ORDER BY id DESC LIMIT ".$oxycfg["post_limit"]."") ORDER BY id;
$i = 0;
while($r = @mysql_fetch_array($result)) {
Last edited by rioguia on Wed Dec 01, 2004 1:21 am, edited 1 time in total.
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post by kettle_drum »

Always put LIMIT last in the query and you should find it works.
Last edited by kettle_drum on Tue Nov 30, 2004 9:03 am, edited 1 time in total.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Re: order by limit causes parse error, unexpected T_STRING

Post by timvw »

rioguia wrote:

Code: Select all

$result = $oxyshout->query("SELECT user, contact, message, date, time, ip, session FROM ".$oxyshout->oxycfg['table_messages']." ORDER BY id DESC LIMIT ".$oxycfg["post_limit"]."") ORDER BY id;
Aside from the already mentionned problem with your invalid sql, you have a syntax error. Here is how i would do it, have a look in the manual, language.variables.string section to find out what the other alternatives are...

Code: Select all

$result = $oxyshout->query("SELECT user, contact, message, date, time, ip, session FROM {$oxyshout->oxycfg['table_messages']} ORDER BY id DESC LIMIT {$oxycfg["post_limit"]}");
rioguia
Forum Newbie
Posts: 4
Joined: Tue Nov 30, 2004 8:37 am
Location: www.substantis.com

SQL syntax near '(ORDER BY id LIMIT 10)' at line 1[

Post by rioguia »

Thanks for your kind suggestions. I tried incorporating them in the code. I especially like the way timvw uses parentheses. It makes the code so much easier to read. Regarding timvw's suggestions on the manual, I looked at the manual but did not see anything I could apply to this problem (this is my first coding exercise).

I am still trying to get the entries in a shout box to display the last five posts, with the newest at the bottom and the oldest at the top. i get the following error when I modify the code like this:
You have an error in your SQL syntax near '(ORDER BY id LIMIT 10)' at line 1

from the var/log/httpd, I get
[client 69.17.65.22] PHP Notice: Uninitialized string offset: 87 in /var/www/html/vh07/oxyshout/mysql.php on line 132

Code: Select all

$result = $oxyshout->query("SELECT user, contact, message, date, time, ip, session FROM {$oxyshout->oxycfg['table_messages']} ORDER BY id DESC (ORDER BY id LIMIT {$oxycfg["post_limit"]})");
$i = 0;
while($r = @mysql_fetch_array($result)) {
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

If you want to order on 2 or more columns: not like you try order by foo, order by foo2

Code: Select all

ORDER BY foo, foo2
rioguia
Forum Newbie
Posts: 4
Joined: Tue Nov 30, 2004 8:37 am
Location: www.substantis.com

are you saying i should use an alias for the id column?

Post by rioguia »

i'm not sure i understand your response. I do not think i am searching on two columns. I think I am searching on the same column twice (First to get the last five posts and then to take that result and sort it so that the posts are displayed oldest first/top and newest last/bottom.

I made the code like this:

Code: Select all

[$result = $oxyshout->query("SELECT user, contact, message, date, time, ip, session FROM {$oxyshout->oxycfg['table_messages']} ORDER BY id DESC (ORDER BY id2 LIMIT {$oxycfg["post_limit"]})");
$i = 0;
while($r = @mysql_fetch_array($result))
That gives an error
Unknown column 'id2' in 'order clause'
Are you telling me to use an alias for the colum "id"? I read in the manual about using aliases but di dn't understand how to declare them.
User avatar
Maugrim_The_Reaper
DevNet Master
Posts: 2704
Joined: Tue Nov 02, 2004 5:43 am
Location: Ireland

Post by Maugrim_The_Reaper »

It's your syntax - why have two order by conditions for the same field?

Code: Select all

<?php
$result = $oxyshout->query("select user, contact, message, date, time, ip, session from {$oxyshout->oxycfg['table_messages']} order by id desc limit {$oxycfg["post_limit"]}");
?>
If you need to order by two field - "order by col1, col2 limit 1" where col1 etc are the fields you want to order by.
rioguia
Forum Newbie
Posts: 4
Joined: Tue Nov 30, 2004 8:37 am
Location: www.substantis.com

thanks for the help

Post by rioguia »

thanks for your help.
why have two order by conditions for the same field?
I may be approaching this problem incorrectly.

Order #1.

I want to find the most recent messages and post them. I am using the posting id number to do this. It's just a number which increments with each posting (This is a shoutbox script).

Order #2.
I need to display the posted messages. The application interface is laid out with the user input field at the bottom. We want to display only the most recent posts. We want the oldest post to be displayed first (at the top of the column) and the newest post displayed last (at the bottom) of the column (near the user input field). We don't want to display all the messages that have ever been posted. We want to limit the number posted using the variable as per the config file.

Desired Shoutbox Display
where id = highest id in the database

Code: Select all

| ----------|
 |   id-4    |
 | ----------|
 | ----------|
 |   id-3    |
 | ----------|
 | ----------|
 |   id-2    |
 | ----------|
 | ----------|
 |   id-1    |
 | ----------|
 | ----------|
 |   id      |
 | ----------|
User avatar
Maugrim_The_Reaper
DevNet Master
Posts: 2704
Joined: Tue Nov 02, 2004 5:43 am
Location: Ireland

Post by Maugrim_The_Reaper »

To grab the oldest - order the timestamp (which you should add), and id ASC - i.e. oldest or lowest id/timestamp first. Then loop through the array to display.

e.g.

Code: Select all

<?php
$result = $oxyshout->query("select user, contact, message, date, time, ip, session from {$oxyshout->oxycfg['table_messages']} order by id ASC limit {$oxycfg["post_limit"]}");
?>
Not sure what the first order you mentioned is for, posting? Are they not alreayd posted if on the database?
Post Reply