Page 1 of 1
order by limit causes parse error, unexpected T_STRING
Posted: Tue Nov 30, 2004 8:49 am
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)) {
Posted: Tue Nov 30, 2004 8:50 am
by kettle_drum
Always put LIMIT last in the query and you should find it works.
Re: order by limit causes parse error, unexpected T_STRING
Posted: Tue Nov 30, 2004 9:01 am
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"]}");
SQL syntax near '(ORDER BY id LIMIT 10)' at line 1[
Posted: Wed Dec 01, 2004 1:02 am
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)) {
Posted: Wed Dec 01, 2004 1:44 am
by timvw
If you want to order on 2 or more columns: not like you try order by foo, order by foo2
are you saying i should use an alias for the id column?
Posted: Wed Dec 01, 2004 3:18 am
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.
Posted: Wed Dec 01, 2004 5:35 am
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.
thanks for the help
Posted: Wed Dec 01, 2004 10:17 am
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 |
| ----------|
Posted: Thu Dec 02, 2004 6:54 am
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?