RE: [PHP] Next and Preview Row
Good day, It should be pointed out that even with this syntax, the database will still scour all of the rows in the table. The only savings is that the database won't return all of these rows due to the limit statement. It might be more efficient to use min and max to determine which row in the database is before and after the desired row, and then only fetch those rows. This can easily done with subselects, but since MySQL does not support them one will have to use multiple queries to piece it together. Of course, if your table is small then this is mostly irrelevant anyway. It might also be worthwhile to note that this code can be made into one query with the UNION statement. However, MySQL doesn't support that part of SQL either (although the documentation says that it's provided in version 4.X ). Darren Gamble Planner, Regional Services Shaw Cablesystems GP 630 - 3rd Avenue SW Calgary, Alberta, Canada T2P 4L4 (403) 781-4948 -Original Message- From: Fournier Jocelyn [Presence-PC] [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 1:24 PM To: Chris Boget; Raymond Gubala; Mark Lo; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [PHP] Next and Preview Row Yes, but how to get ONLY the 3 records you need ? Because often in an application, you don't care about the other records. In this case AFAIK, there is no other solution than issuing at least two queries : SELECT * FROM table WHERE field<='ID00025' ORDER BY field DESC LIMIT 2 SELECT * FROM table WHERE field>'ID00025' ORDER BY field ASC LIMIT 1 - Original Message - From: "Chris Boget" <[EMAIL PROTECTED]> To: "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]>; "Raymond Gubala" <[EMAIL PROTECTED]>; "Mark Lo" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, February 22, 2002 9:17 PM Subject: Re: [PHP] Next and Preview Row > > It seems worst to me because in your case mysql has to retrieve all the > > rows. If it's a table with 1 million records or more, this should hurt ;) > > As I said, it was pseudo code. Now, imagine that you were just > getting the records for a particular user? a particular application? > Where there won't be millions and millions of rows? > > Chris > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Next and Preview Row
Yes, but how to get ONLY the 3 records you need ? Because often in an application, you don't care about the other records. In this case AFAIK, there is no other solution than issuing at least two queries : SELECT * FROM table WHERE field<='ID00025' ORDER BY field DESC LIMIT 2 SELECT * FROM table WHERE field>'ID00025' ORDER BY field ASC LIMIT 1 - Original Message - From: "Chris Boget" <[EMAIL PROTECTED]> To: "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]>; "Raymond Gubala" <[EMAIL PROTECTED]>; "Mark Lo" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, February 22, 2002 9:17 PM Subject: Re: [PHP] Next and Preview Row > > It seems worst to me because in your case mysql has to retrieve all the > > rows. If it's a table with 1 million records or more, this should hurt ;) > > As I said, it was pseudo code. Now, imagine that you were just > getting the records for a particular user? a particular application? > Where there won't be millions and millions of rows? > > Chris > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Next and Preview Row
Hi,
It seems worst to me because in your case mysql has to retrieve all the
rows.
If it's a table with 1 million records or more, this should hurt ;)
Regards,
Jocelyn Fournier
Presence-PC
- Original Message -
From: "Chris Boget" <[EMAIL PROTECTED]>
To: "Raymond Gubala" <[EMAIL PROTECTED]>; "Mark Lo"
<[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Sent: Friday, February 22, 2002 4:45 PM
Subject: Re: [PHP] Next and Preview Row
> > The solution I have been using is to do three queries similar to the
below
> > SELECT * FROM table WHERE field='ID00025'
> > SELECT * FROM table WHERE field<'ID00025' ORDER BY field DESC LIMIT 0,1
> > SELECT * FROM table WHERE field>'ID00025' ORDER BY field ASC LIMIT 0,1
> > If you whish more row returned change the number in the LIMIT
>
> Why bother with 3 queries? It's a waste of resources, especially if
> you are working with the same record set...
>
> Example (pseudo)code:
>
>
> $query = "SELECT * FROM table";
> $result = mysql( $dbname, $query );
>
>for( $i = 0; $i < mysql_num_rows( $result ); $i++ ) {
> echo "Previous field: " . mysql_result( $result, ( $i - 1 ),
"field" );
> echo "Current field: " . mysql_result( $result, $i, "field" );
> echo "Next field: " . mysql_result( $result, ( $i + 1 ), "field" );
>
> }
>
> ?>
>
> Chris
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php (the manual)
>http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Next and Preview Row
> It seems worst to me because in your case mysql has to retrieve all the > rows. If it's a table with 1 million records or more, this should hurt ;) As I said, it was pseudo code. Now, imagine that you were just getting the records for a particular user? a particular application? Where there won't be millions and millions of rows? Chris -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Next and Preview Row
> The solution I have been using is to do three queries similar to the below > SELECT * FROM table WHERE field='ID00025' > SELECT * FROM table WHERE field<'ID00025' ORDER BY field DESC LIMIT 0,1 > SELECT * FROM table WHERE field>'ID00025' ORDER BY field ASC LIMIT 0,1 > If you whish more row returned change the number in the LIMIT Why bother with 3 queries? It's a waste of resources, especially if you are working with the same record set... Example (pseudo)code: Chris -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Next and Preview Row
The solution I have been using is to do three queries similar to the below SELECT * FROM table WHERE field='ID00025' SELECT * FROM table WHERE field<'ID00025' ORDER BY field DESC LIMIT 0,1 SELECT * FROM table WHERE field>'ID00025' ORDER BY field ASC LIMIT 0,1 If you whish more row returned change the number in the LIMIT -- Raymond Gubala Program Coordinator Multimedia Design and Web Developer Durham College mailto:[EMAIL PROTECTED] > From: "Mark Lo" <[EMAIL PROTECTED]> > Date: Fri, 22 Feb 2002 23:15:18 +0800 > To: <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Subject: [PHP] Next and Preview Row > > Dear All, > > I am using PHP + MYSQL. I have a question that I need some expert > to help. That is: > > How do I find out a next and preview row values by using PHP and MYSQL. For > examples, > > Row 10ID00010need this value > Row 11ID00025have this value on hand > Row 12ID00063need this value > > The questions is how do I find out the values in Row 10 and Row 12, if and > only if I only have one data that is "ID00025" (select * from table where > field="ID00025") . But, the questions is How do I find out the data in Row > 10 and Row 12 which is "ID00010" and "ID00063" assume I don't know the > values of "ID00025" is in Row 11. > > Thank you so much for your help. > > > Mark Lo > > > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Next and Preview Row
My dump: # phpMyAdmin MySQL-Dump # http://phpwizard.net/phpMyAdmin/ # # Host: 192.168.1.11:3306 Database : test # # # Table structure for table 'some' # CREATE TABLE some ( id varchar(6) NOT NULL, descr varchar(250) NOT NULL ); # # Dumping data for table 'some' # INSERT INTO some VALUES ( 'ID0001', 'first line'); INSERT INTO some VALUES ( 'ID0002', 'Second line'); INSERT INTO some VALUES ( 'ID0010', 'Third row'); INSERT INTO some VALUES ( 'ID0015', 'Fourth row'); INSERT INTO some VALUES ( 'ID0023', 'Fifth row'); INSERT INTO some VALUES ( 'ID0026', 'Sixth row'); Try this sql on this table: select * from some where id<'ID0015' order by id desc limit 0,1; select * from some where id>'ID0015' order by id limit 0,1; Best regards, Andrey Hristov - Original Message - From: "Mark Lo" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, February 22, 2002 5:15 PM Subject: [PHP] Next and Preview Row > Dear All, > > I am using PHP + MYSQL. I have a question that I need some expert > to help. That is: > > How do I find out a next and preview row values by using PHP and MYSQL. For > examples, > > Row 10ID00010need this value > Row 11ID00025have this value on hand > Row 12ID00063need this value > > The questions is how do I find out the values in Row 10 and Row 12, if and > only if I only have one data that is "ID00025" (select * from table where > field="ID00025") . But, the questions is How do I find out the data in Row > 10 and Row 12 which is "ID00010" and "ID00063" assume I don't know the > values of "ID00025" is in Row 11. > > Thank you so much for your help. > > > Mark Lo > > > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Next and Preview Row
> How do I find out a next and preview row values by using PHP and MYSQL. For > examples, Take a look at the function mysql_result(); Chris -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
