Results 1 to 13 of 13
Thread: PHP + mySQL : Selecting rows
-
04-14-2004, 08:05 AM #1Newbie
- Join Date
- Apr 2004
- Posts
- 10
PHP + mySQL : Selecting rows
How can I select a previous row and a next row?
For example I have a table like this:
Id: Value
1 A
2 B
3 C
4 D
5 E
6 F
I just queried (select from) where id was 3, is there a way to select from the previous row and next row (without using id's)?
I don't want to use auto incremented Ids, because sometimes a row can be deleted between them, so you get something like 1-2-4-5 (missing the 3).
-
04-14-2004, 08:27 AM #2Web Hosting Guru
- Join Date
- Nov 2003
- Location
- Ljubljana, Slovenija, Europe
- Posts
- 298
You WANT to use auto_incremment! That's the only proper way to handle table information. As for the previous and the next row, that's fairly easy. You should query the entire table and have the rows sorted by the auto_incremment column, that would give you a result that contains rows. Every result row would represent a table row and they would be order as created. So there would be no problem with missing index values. You would simply take the previous or the next row from results according to the the row in results that you have the relevant data. Good luck! Airnine
-
04-14-2004, 09:15 AM #3Newbie
- Join Date
- Apr 2004
- Posts
- 10
No, users will be able to delete a row (a comment for example) and then it will skip an ID for sure.
-
04-14-2004, 10:16 AM #4Retired Moderator
- Join Date
- Apr 2003
- Location
- London, UK
- Posts
- 4,721
You could return the last id with mysql_insert_id() then use SELECT * FROM table WHERE id > lasd_id LIMIT 1
And the same with WHERE id < lasd_id LIMIT 1 to return the previous and next rows.Hyperconfused (™)
-
04-14-2004, 02:52 PM #5Newbie
- Join Date
- Apr 2004
- Posts
- 10
WHERE id < '$last_id' LIMIT 1
causes it to return an id at the beginning of the table, so always 1.
But > works good for the next row.
-
04-14-2004, 03:16 PM #6Retired Moderator
- Join Date
- Apr 2003
- Location
- London, UK
- Posts
- 4,721
WHERE id < '$last_id' ORDER BY id DESC LIMIT 1
try that instead.Hyperconfused (™)
-
04-14-2004, 05:50 PM #7Newbie
- Join Date
- Apr 2004
- Posts
- 10
Gives me a syntax error
-
04-14-2004, 05:52 PM #8Newbie
- Join Date
- Apr 2004
- Posts
- 10
Hmm, I tried a direct result query, maybe order only works with arrays?
-
04-15-2004, 02:35 AM #9Web Hosting Master
- Join Date
- Jul 2003
- Location
- Kuwait
- Posts
- 5,104
don't use ' ' around a variable name, it does not get interpreted.
PHP Code:$var = "Hello";
echo '$var'; //prints $var
echo "$var"; //prints Hello
-
04-15-2004, 06:19 AM #10Retired Moderator
- Join Date
- Apr 2003
- Location
- London, UK
- Posts
- 4,721
It will in a MySQL query, as long as the variable has already been defined properly in the script.
Hyperconfused (™)
-
04-15-2004, 09:21 AM #11Web Hosting Master
- Join Date
- Jul 2003
- Location
- Kuwait
- Posts
- 5,104
It will not if the query is encapsulated in ' ', in which case, it will throw a invalid query/syntax error. Which you can catch with mysql_error();
-
04-15-2004, 10:29 AM #12Newbie
- Join Date
- Apr 2004
- Posts
- 10
Thanks, it worked now.
-
04-16-2004, 02:34 PM #13WHT Addict
- Join Date
- Apr 2004
- Posts
- 104
Originally posted by fyrestrtr
It will not if the query is encapsulated in ' ', in which case, it will throw a invalid query/syntax error. Which you can catch with mysql_error();PHP Code:$id = "MyName";
$sql='SELECT * FROM table WHERE id = '$id'';
mysql_query($sql);
echo $sql;
//Echo will display:
//SELECT * FROM table where id = $id
THEREFORE, the following IDENTICAL query (double quoted) WILL work.
PHP Code:$id="MyName";
$sql="SELECT * FROM table WHERE id = '$id' ";
mysql_query($sql);
echo $sql;
//this will display :
// SELECT * FROM table WHERE id = MyName
It's just that the original SQL query posted just before your comment of "if single quoted it will not work" was "probably" intended to be parsed by php..
Just wanted to clear up that point, before some noobs come by and give up in frustration when their code won't work..