[PHP] SQL - Select rand() ?

[James, Yz]

Hi Guys,

If I wanted to retrieve just one field randomly from a MySQL table, would I
just use something like:

"SELECT RAND(id) FROM table  LIMIT 0,1" ?

I suppose the best thing for me to do would be to try it, but I am fast
losing the will to stay awake ;)

Thanks, as always,

James.



-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

[PHP] SQL Select Unique() ?

[James, Yz]

Hi Guys,

Is there a method of extracting rows from a MySQL table Uniquely (as in only
once) ?. For example, when a user performs a search, using two words,
it may return the same row twice if the search is spread over two or more
SQL "selections".  An example:

If hypothetical row 129 is a Public House, but the public house doubles up
as a restaurant, a search like this might return the same result twice.
Here is our hypothetical pub:

id = 129
name = The Blue Bell Inn
category = Public House
description = The Blue Bell Inn is a hypothetical pub, in the heart of rural
England.  Why not visit, have a drink and perhaps even dine in our fine
Restaurant area.

The user might perform the search, searching by "category" OR by
"description".  So if they typed "Public House / Restaurant" as the query,
the following would occur:

$sql = "SELECT * FROM table WHERE category LIKE 'Public House / Restaurant'
OR description LIKE 'Public House / Restaurant'";

Surely that would bring the same row back twice.  Is there any way of
selecting from the table just once, without having to restrict the search
facility to something like:

"SELECT * FROM table WHERE category LIKE '%$searchtext%'";

as opposed to having the "OR" in as well?

Thanks, as always,

James.



-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Re: [PHP] SQL - Select rand() ?

[Steve Lawson]

Sup,
In the newer versions of mySQL, you can get random results like

SELECT id FROM table ORDER BY rand() LIMIT 0,1

SL.



- Original Message -
From: "James, Yz" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, April 17, 2001 2:55 PM
Subject: [PHP] SQL - Select rand() ?


> Hi Guys,
>
> If I wanted to retrieve just one field randomly from a MySQL table, would
I
> just use something like:
>
> "SELECT RAND(id) FROM table  LIMIT 0,1" ?
>
> I suppose the best thing for me to do would be to try it, but I am fast
> losing the will to stay awake ;)
>
> Thanks, as always,
>
> James.
>
>
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> To contact the list administrators, e-mail: [EMAIL PROTECTED]
>


-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Re: [PHP] SQL Select Unique() ?

[David Robley]

On Fri, 20 Apr 2001 05:41, James, Yz wrote:
> Hi Guys,
>
> Is there a method of extracting rows from a MySQL table Uniquely (as in
> only once) ?. For example, when a user performs a search, using two
> words, it may return the same row twice if the search is spread over
> two or more SQL "selections".  An example:
>
> If hypothetical row 129 is a Public House, but the public house doubles
> up as a restaurant, a search like this might return the same result
> twice. Here is our hypothetical pub:
>
> id = 129
> name = The Blue Bell Inn
> category = Public House
> description = The Blue Bell Inn is a hypothetical pub, in the heart of
> rural England.  Why not visit, have a drink and perhaps even dine in
> our fine Restaurant area.
>
> The user might perform the search, searching by "category" OR by
> "description".  So if they typed "Public House / Restaurant" as the
> query, the following would occur:
>
> $sql = "SELECT * FROM table WHERE category LIKE 'Public House /
> Restaurant' OR description LIKE 'Public House / Restaurant'";

This of course won't select the hypothetical cae in question. Perhaps you 
might be betterr off using a dropdown list built from the table of 
categories that you are using as a lookup table (you are, aren't you?)

> Surely that would bring the same row back twice.  Is there any way
> of selecting from the table just once, without having to restrict the
> search facility to something like:
>
> "SELECT * FROM table WHERE category LIKE '%$searchtext%'";

Again, that won't work in this case - you might want to separate the 
words in the search string and do an OR search on all the words.
 
>
> as opposed to having the "OR" in as well?
>
> Thanks, as always,
>
> James.

Broadly, in this type of situation the row will only be returned once 
notwithstanding that it might meet several citeria.

-- 
David Robley| WEBMASTER & Mail List Admin
RESEARCH CENTRE FOR INJURY STUDIES  | http://www.nisu.flinders.edu.au/
AusEinet| http://auseinet.flinders.edu.au/
Flinders University, ADELAIDE, SOUTH AUSTRALIA

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Re: [PHP] SQL Select Unique() ?

[CC Zona]

In article <9bng4u$ftc$[EMAIL PROTECTED]>,
 [EMAIL PROTECTED] ("James, Yz") wrote:

> $sql = "SELECT * FROM table WHERE category LIKE 'Public House / Restaurant'
> OR description LIKE 'Public House / Restaurant'";
> 
> Surely that would bring the same row back twice

Surely not.  Have you tried it yet?  Unless there are duplicate rows in the 
table (which should *not* be the case), each row that is matched by that 
query should only be returned once per execution of the query.

>  Is there any way of
> selecting from the table just once, without having to restrict the search
> facility to something like:
> 
> "SELECT * FROM table WHERE category LIKE '%$searchtext%'";
> 
> as opposed to having the "OR" in as well?

I'm not sure what you're after.  If executing the top example is getting 
you unwanted rows, perhaps you could re-post with some sample data and 
pointing out which rows are being shown in duplicate.  (It might also be a 
good idea to re-check your data first if there's any possibility that there 
are duplicate rows existing in the db.  'Cuz that's the kind of thing 
that's gonna mis-lead you about how SQL queries normally work.)

-- 
CC

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Re: [PHP] SQL Select Unique() ?

[Anuradha Ratnaweera]

On Thu, 19 Apr 2001, James, Yz wrote:

> Is there a method of extracting rows from a MySQL table Uniquely (as in only
> once) ?. For example, when a user performs a search, using two words,
> it may return the same row twice if the search is spread over two or more
> SQL "selections".  An example:

I am not sure whether you can do it on mysql. On postgresql you can do a

select distinct ...

Anuradha


-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Re: [PHP] SQL Select Unique() ?

[CC Zona]

In article ,
 [EMAIL PROTECTED] (Anuradha Ratnaweera) wrote:

> > Is there a method of extracting rows from a MySQL table Uniquely (as in only
> > once) ?. For example, when a user performs a search, using two words,
> > it may return the same row twice if the search is spread over two or more
> > SQL "selections".  An example:
> 
> I am not sure whether you can do it on mysql. On postgresql you can do a
> 
> select distinct ...

The "distinct" keyword also works in mysql (the manual at mysql.com 
provides details).

-- 
CC

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]