Retrieve One Row Of MySQL

How do I retrieve only one row of database info? here's what I have so
far


$db_host = "localhost";
$db_user = "username";
$db_pwd = "password";
$db_name = "dbname";
mysql_connect($db_host, $db_user, $db_pwd);
mysql_select_db($db_name);
?>

Re: Retrieve One Row Of MySQL

On Wed, 03 Oct 2007 16:41:08 +0200, Lamer wrote:

> How do I retrieve only one row of database info? here's what I have so=

> far
> $sql =3D "SELECT * FROM colors";

http://dev.mysql.com/doc/refman/4.1/en/select.html
Check out the 'LIMIT' clause.


-- =

Rik Wasmus

Re: Retrieve One Row Of MySQL

On 3 oct, 11:41, Lamer wrote:
> How do I retrieve only one row of database info? here's what I have so
> far
>
> > $db_host = "localhost";
> $db_user = "username";
> $db_pwd = "password";
> $db_name = "dbname";
> mysql_connect($db_host, $db_user, $db_pwd);
> mysql_select_db($db_name);
> ?>
>

Re: Retrieve One Row Of MySQL

In article <1191424134.543150.96700@o80g2000hse.googlegroups.com>,
Claudio Corlatti wrote:
>well i didn't understand your question very well, but i suppose that
>you want to obtain just one row from the table "colors".
>
>to do that you need to add a "where" clause in your mysql query, for
>instance "select * from colors where idColor=1"

That will work, but a LIMIT should also be specified for efficiency.
The query "SELECT * FROM MyColorTable WHERE ColorID=26 LIMIT 1" will
cause MySQL to stop searching the table as soon as one row is found.
Otherwise it will keep searching for other rows with ColorID=26.

-A

Re: Retrieve One Row Of MySQL

"axlq" wrote in message
news:fe1auv$g0r$1@blue.rahul.net...
> In article <1191424134.543150.96700@o80g2000hse.googlegroups.com>,
> Claudio Corlatti wrote:
>>well i didn't understand your question very well, but i suppose that
>>you want to obtain just one row from the table "colors".
>>
>>to do that you need to add a "where" clause in your mysql query, for
>>instance "select * from colors where idColor=1"
>
> That will work, but a LIMIT should also be specified for efficiency.
> The query "SELECT * FROM MyColorTable WHERE ColorID=26 LIMIT 1" will
> cause MySQL to stop searching the table as soon as one row is found.
> Otherwise it will keep searching for other rows with ColorID=26.
>
> -A

WHY does the OP wand to stop at one? Is this simply check for the
existinance of some value in a particular field/ If that is so, why not use
the COUNT operator in the sql call?

Shelly

Re: Retrieve One Row Of MySQL

Post removed (X-No-Archive: yes)

Re: Retrieve One Row Of MySQL

On Oct 3, 9:33 pm, Gary L. Burnore wrote:
> On Wed, 3 Oct 2007 21:16:22 -0400, "Shelly"
>
>
>
> wrote:
>
> >"axlq" wrote in message
> >news:fe1auv$g0r$1@blue.rahul.net...
> >> In article <1191424134.543150.96...@o80g2000hse.googlegroups.com>,
> >> Claudio Corlatti wrote:
> >>>well i didn't understand your question very well, but i suppose that
> >>>you want to obtain just one row from the table "colors".
>
> >>>to do that you need to add a "where" clause in your mysql query, for
> >>>instance "select * from colors where idColor=3D1"
>
> >> That will work, but a LIMIT should also be specified for efficiency.
> >> The query "SELECT * FROM MyColorTable WHERE ColorID=3D26 LIMIT 1" will
> >> cause MySQL to stop searching the table as soon as one row is found.
> >> Otherwise it will keep searching for other rows with ColorID=3D26.
>
> >> -A
>
> >WHY does the OP wand to stop at one? Is this simply check for the
> >existinance of some value in a particular field/ If that is so, why not=
use
> >the COUNT operator in the sql call?
>
> Maybe because if you've got hundreds of thousands of rows to count
> and, instead, you want to stop after looking at only one to see if the
> table is non-empty, you'd save some processing time?
>
>
>
> >Shelly
>
> --
> gburnore at DataBasix dot Com
> ------------------------------------------------------------ -------------=
--
> How you look depends on where you go.
> ------------------------------------------------------------ -------------=
--
> Gary L. Burnore | ÝÛ³ºÝ³Þ³ºÝ=
³³Ýۺݳ޳ºÝ³Ý³Þ³ºÝ³Ý ÝÛ=B3
> | ÝÛ³ºÝ³Þ³ºÝ=
³³Ýۺݳ޳ºÝ³Ý³Þ³ºÝ³Ý ÝÛ=B3
> Official .sig, Accept no substitutes. | ÝÛ³ºÝ³Þ³ºÝ=
³³Ýۺݳ޳ºÝ³Ý³Þ³ºÝ³Ý ÝÛ=B3
> | ÝÛ 0 1 7 2 3 / ݳ=DE 3 7=
4 9 3 0 Û³
> Black Helicopter Repair Services, Ltd.| Official Proof of Purchase
> ==================== =====
==================== =====3D=
==================== =====3D=
=3D

The LIMIT clause is the most efficient means of returning one and only
one row from at table of data LIMIT 1.

Or, three and at most three. LIMIT 3.

All other means listed here require a significant amount of additional
and, depending upon your applications needs, quite needless processing
to get the job done.

Re: Retrieve One Row Of MySQL

"Shelly" wrote in message
news:13g8fo4delbjk96@corp.supernews.com...

> WHY does the OP wand to stop at one? Is this simply check for the
> existinance of some value in a particular field/ If that is so, why not
> use the COUNT operator in the sql call?

That's an easy one to answer - efficiency.
Count has to check ALL of the rows for a value, and then to tally up the
count.
This SELECT TOP just gets one cell from one row - and does nothing more.

Re: Retrieve One Row Of MySQL

Post removed (X-No-Archive: yes)

Re: Retrieve One Row Of MySQL

"Gary L. Burnore" wrote in message
news:fe3q30$9q0$4@blackhelicopter.databasix.com...

> You sure quoted a lot

as did you :-)

> to say "I agree" :)

Re: Retrieve One Row Of MySQL

On Oct 4, 5:39 pm, Gary L. Burnore wrote:
> On Thu, 04 Oct 2007 12:44:17 -0000, phpCodeHead
>
>
>
> wrote:
> >On Oct 3, 9:33 pm, Gary L. Burnore wrote:
> >> On Wed, 3 Oct 2007 21:16:22 -0400, "Shelly"
>
> >> wrote:
>
> >> >"axlq" wrote in message
> >> >news:fe1auv$g0r$1@blue.rahul.net...
> >> >> In article <1191424134.543150.96...@o80g2000hse.googlegroups.com>,
> >> >> Claudio Corlatti wrote:
> >> >>>well i didn't understand your question very well, but i suppose that
> >> >>>you want to obtain just one row from the table "colors".
>
> >> >>>to do that you need to add a "where" clause in your mysql query, for
> >> >>>instance "select * from colors where idColor=3D1"
>
> >> >> That will work, but a LIMIT should also be specified for efficiency.
> >> >> The query "SELECT * FROM MyColorTable WHERE ColorID=3D26 LIMIT 1" w=
ill
> >> >> cause MySQL to stop searching the table as soon as one row is found.
> >> >> Otherwise it will keep searching for other rows with ColorID=3D26.
>
> >> >> -A
>
> >> >WHY does the OP wand to stop at one? Is this simply check for the
> >> >existinance of some value in a particular field/ If that is so, why =
not use
> >> >the COUNT operator in the sql call?
>
> >> Maybe because if you've got hundreds of thousands of rows to count
> >> and, instead, you want to stop after looking at only one to see if the
> >> table is non-empty, you'd save some processing time?
>
> >> >Shelly
>
> >> --
> >> gburnore at DataBasix dot Com
> >> ------------------------------------------------------------ ----------=
-----
> >> How you look depends on where you go.
> >> ------------------------------------------------------------ ----------=
-----
> >> Gary L. Burnore | ÝÛ³ºÝ³Þ³=BA=
ݳ³Ýۺݳ޳ºÝ³Ý³Þ³ºÝ³ ÝÝÛ³
> >> | ÝÛ³ºÝ³Þ³=BA=
ݳ³Ýۺݳ޳ºÝ³Ý³Þ³ºÝ³ ÝÝÛ³
> >> Official .sig, Accept no substitutes. | ÝÛ³ºÝ³Þ³=BA=
ݳ³Ýۺݳ޳ºÝ³Ý³Þ³ºÝ³ ÝÝÛ³
> >> | ÝÛ 0 1 7 2 3 / ݳ=DE =
3 7 4 9 3 0 Û³
> >> Black Helicopter Repair Services, Ltd.| Official Proof of Purchase
> >> ==================== ===3D=
==================== =====3D=
==================== =====3D=
==
>
> >The LIMIT clause is the most efficient means of returning one and only
> >one row from at table of data LIMIT 1.
>
> >Or, three and at most three. LIMIT 3.
>
> >All other means listed here require a significant amount of additional
> >and, depending upon your applications needs, quite needless processing
> >to get the job done.
>
> You sure quoted a lot to say "I agree" :)
> --
> gburnore at DataBasix dot Com
> ------------------------------------------------------------ -------------=
--
> How you look depends on where you go.
> ------------------------------------------------------------ -------------=
--
> Gary L. Burnore | ÝÛ³ºÝ³Þ³ºÝ=
³³Ýۺݳ޳ºÝ³Ý³Þ³ºÝ³Ý ÝÛ=B3
> | ÝÛ³ºÝ³Þ³ºÝ=
³³Ýۺݳ޳ºÝ³Ý³Þ³ºÝ³Ý ÝÛ=B3
> Official .sig, Accept no substitutes. | ÝÛ³ºÝ³Þ³ºÝ=
³³Ýۺݳ޳ºÝ³Ý³Þ³ºÝ³Ý ÝÛ=B3
> | ÝÛ 0 1 7 2 3 / ݳ=DE 3 7=
4 9 3 0 Û³
> Black Helicopter Repair Services, Ltd.| Official Proof of Purchase
> ==================== =====
==================== =====3D=
==================== =====3D=
=3D

"i agree" was not the thought in my head :)

Re: Retrieve One Row Of MySQL

On 3 oct, 21:09, a...@spamcop.net (axlq) wrote:
> In article <1191424134.543150.96...@o80g2000hse.googlegroups.com>,
> Claudio Corlatti wrote:
>
> >well i didn't understand your question very well, but i suppose that
> >you want to obtain just one row from the table "colors".
>
> >to do that you need to add a "where" clause in your mysql query, for
> >instance "select * from colors where idColor=1"
>
> That will work, but a LIMIT should also be specified for efficiency.
> The query "SELECT * FROM MyColorTable WHERE ColorID=26 LIMIT 1" will
> cause MySQL to stop searching the table as soon as one row is found.
> Otherwise it will keep searching for other rows with ColorID=26.
>
> -A

yes, you are right the limit function is the best option in this case.