[PHP-DB] rand()

2004-09-29 Thread blackwater dev
Hello,

I have the following query:

select * from clients order by RAND()

THe query runs fine on my local server but when I run it on my hosts
server, it works ok if there is only one record in the db but then
there is more than one...I get this error:

#1 - Can't create/write to file '/var/tmp/#sqlbe_1d99_0.MYI' (Errcode: 2)

What's going on?

Thanks!

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



[PHP-DB] Rand()

2004-10-07 Thread blackwater dev
I have the following query:

Select id, fname, lname from clients where featured=1 order by RAND()

At any time, there should only be three clients where featured =1. 
Problem is I have run this query several times yet it always returns
them in the same order.  Not sure it makes a difference but the
primary key is on the id column.  What am I doing wrong?


Thanks!

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] rand()

2004-09-29 Thread Jennifer Goodie
-- Original message from blackwater dev : -- 
> select * from clients order by RAND() 
> 
> THe query runs fine on my local server but when I run it on my hosts 
> server, it works ok if there is only one record in the db but then 
> there is more than one...I get this error: 
> 
> #1 - Can't create/write to file '/var/tmp/#sqlbe_1d99_0.MYI' (Errcode: 2) 
> 
> What's going on? 

perror 2 returns "Error code   2:  No such file or directory"

Does /var/tmp exist on the server?

If you run explain on your query in both cases (with one record and with more than one 
record) you will see that when there is more than one record it is probably using 
temporary tables and file sort, but is not when there's only one record.

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] rand()

2004-09-29 Thread blackwater dev
I have no idea of it is on the server or now...I just have access to
my little section of the server.  Is there another way to randomly
order?


On Wed, 29 Sep 2004 23:27:43 +, Jennifer Goodie <[EMAIL PROTECTED]> wrote:
> -- Original message from blackwater dev : --
> > select * from clients order by RAND()
> >
> > THe query runs fine on my local server but when I run it on my hosts
> > server, it works ok if there is only one record in the db but then
> > there is more than one...I get this error:
> >
> > #1 - Can't create/write to file '/var/tmp/#sqlbe_1d99_0.MYI' (Errcode: 2)
> >
> > What's going on?
> 
> perror 2 returns "Error code   2:  No such file or directory"
> 
> Does /var/tmp exist on the server?
> 
> If you run explain on your query in both cases (with one record and with more than 
> one record) you will see that when there is more than one record it is probably 
> using temporary tables and file sort, but is not when there's only one record.
>

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] rand()

2004-09-30 Thread Bastien Koert
The MYI file is the index file. Is there an index on that table?

From: [EMAIL PROTECTED] (Jennifer Goodie)
To: blackwater dev <[EMAIL PROTECTED]>
CC: [EMAIL PROTECTED], Subject: Re: [PHP-DB] rand()
Date: Wed, 29 Sep 2004 23:27:43 +
-- Original message from blackwater dev : --
> select * from clients order by RAND()
>
> THe query runs fine on my local server but when I run it on my hosts
> server, it works ok if there is only one record in the db but then
> there is more than one...I get this error:
>
> #1 - Can't create/write to file '/var/tmp/#sqlbe_1d99_0.MYI' (Errcode: 
2)
>
> What's going on?

perror 2 returns "Error code   2:  No such file or directory"
Does /var/tmp exist on the server?
If you run explain on your query in both cases (with one record and with 
more than one record) you will see that when there is more than one record 
it is probably using temporary tables and file sort, but is not when 
there's only one record.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
_
Take advantage of powerful junk e-mail filters built on patented Microsoft® 
SmartScreen Technology. 
http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotmail.com/enca&HL=Market_MSNIS_Taglines 
 Start enjoying all the benefits of MSN® Premium right now and get the 
first two months FREE*.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


RE: [PHP-DB] Rand()

2004-10-07 Thread Ed Lazor
select id, fname, lname, RAND() as temp from clients where featured=1 order
by temp



> -Original Message-
> I have the following query:
> 
> Select id, fname, lname from clients where featured=1 order by RAND()
> 
> At any time, there should only be three clients where featured =1.
> Problem is I have run this query several times yet it always returns
> them in the same order.  Not sure it makes a difference but the
> primary key is on the id column.  What am I doing wrong?
> 
> 
> Thanks!
> 
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Rand()

2004-10-07 Thread blackwater dev
Yeah, I had tried that earlier but it doesn't work.  It does generate
a new value for temp each time but they are still in the same order as
the first one always has a lower temp value


On Thu, 7 Oct 2004 06:40:23 -0700, Ed Lazor <[EMAIL PROTECTED]> wrote:
> 
> 
> select id, fname, lname, RAND() as temp from clients where featured=1 order
> by temp
> 
> > -Original Message-
> > I have the following query:
> >
> > Select id, fname, lname from clients where featured=1 order by RAND()
> >
> > At any time, there should only be three clients where featured =1.
> > Problem is I have run this query several times yet it always returns
> > them in the same order.  Not sure it makes a difference but the
> > primary key is on the id column.  What am I doing wrong?
> >
> >
> > Thanks!
> >
> > --
> > PHP Database Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
> 
> 
> 
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 
>

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



RE: [PHP-DB] Rand()

2004-10-07 Thread Ed Lazor
Hrm.  Dunno then.  I just manually tested it in MySQL and both approaches
worked.  Are you able to test directly in MySQL?  Might have to go to the
mysql list for this.

-Ed


> -Original Message-
> 
> Yeah, I had tried that earlier but it doesn't work.  It does generate
> a new value for temp each time but they are still in the same order as
> the first one always has a lower temp value
> 
> 
> On Thu, 7 Oct 2004 06:40:23 -0700, Ed Lazor <[EMAIL PROTECTED]> wrote:
> >
> >
> > select id, fname, lname, RAND() as temp from clients where featured=1
> order
> > by temp
> >
> > > -Original Message-
> > > I have the following query:
> > >
> > > Select id, fname, lname from clients where featured=1 order by RAND()
> > >
> > > At any time, there should only be three clients where featured =1.
> > > Problem is I have run this query several times yet it always returns
> > > them in the same order.  Not sure it makes a difference but the
> > > primary key is on the id column.  What am I doing wrong?

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



[PHP-DB] Rand() Emulation

2003-06-24 Thread Gerard Samuel
Im trying to figure out a way to emulate mysql's RAND() function to be
cross database compatible via php.
Has anyone done anything similar to this???
Thanks

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


Re: [PHP-DB] Rand() Emulation

2003-06-24 Thread Becoming Digital
> Im trying to figure out a way to emulate mysql's RAND() function to be
> cross database compatible via php.
> Has anyone done anything similar to this???

How about PHP's rand() function?
http://us2.php.net/manual/en/function.rand.php

Edward Dudlik
Becoming Digital
www.becomingdigital.com

Did I help you?  Want to show your thanks?
www.amazon.com/o/registry/EGDXEBBWTYUU 


- Original Message - 
From: "Gerard Samuel" <[EMAIL PROTECTED]>
To: "PHP-DB" <[EMAIL PROTECTED]>
Sent: Tuesday, 24 June, 2003 02:56
Subject: [PHP-DB] Rand() Emulation


Im trying to figure out a way to emulate mysql's RAND() function to be
cross database compatible via php.
Has anyone done anything similar to this???

Thanks


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php





-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Rand() Emulation

2003-06-24 Thread Gerard Samuel
Doesn't really cut it when you do not know a min and max value, or want 
to extract a random range of numbers.

Becoming Digital wrote:

Im trying to figure out a way to emulate mysql's RAND() function to be
cross database compatible via php.
Has anyone done anything similar to this???
   

How about PHP's rand() function?
http://us2.php.net/manual/en/function.rand.php
Edward Dudlik
Becoming Digital
www.becomingdigital.com
Did I help you?  Want to show your thanks?
www.amazon.com/o/registry/EGDXEBBWTYUU 



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


Re: [PHP-DB] Rand() Emulation

2003-06-24 Thread Doug Thompson
An incredible interpretation of


If called without the optional min, max arguments rand() returns a pseudo-random value 
between 0 and RAND_MAX. If you want a random number between 5 and 15 (inclusive), for 
example, use rand (5, 15). 


Doug


On Tue, 24 Jun 2003 11:14:55 -0400, Gerard Samuel wrote:

>Doesn't really cut it when you do not know a min and max value, or want 
>to extract a random range of numbers.
>
>Becoming Digital wrote:
>
>>>Im trying to figure out a way to emulate mysql's RAND() function to be
>>>cross database compatible via php.
>>>Has anyone done anything similar to this???
>>>
>>>
>>
>>How about PHP's rand() function?
>>http://us2.php.net/manual/en/function.rand.php
>>
>>Edward Dudlik
>>Becoming Digital
>>www.becomingdigital.com
>>
>>Did I help you?  Want to show your thanks?
>>www.amazon.com/o/registry/EGDXEBBWTYUU 
>>
>
>
>-- 
>PHP Database Mailing List (http://www.php.net/)
>To unsubscribe, visit: http://www.php.net/unsub.php
>



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Rand() Emulation

2003-06-24 Thread Gerard Samuel
Im probably not making myself clear.
Ultimately, my goal is to emulate mysql's ->
SELECT * FROM TABLE ORDER BY RAND() LIMIT (X)
for other databases that do not support RAND().  So using variations of 
php's rand(), wouldn't make sense,
as it only picks one value out of a range of values, that are numerical 
in nature.

But here is an idea, that Im thinking about, but haven't gotten to the 
code as yet.
1.  In the tables that I want random values from, create a "rand" 
column, that contains incremental numerical values for each row.
2.  select the the maximum number from the rand column.
3.  Assume that there are no gaps between 0 and this max number.
4.  Create an array of numbers with values between 0 and max number.
5.  Use array_rand() to "randomly" choose (x) values from the array 
created in step 4.  (I may choose maybe
a 1 or 2 values more than whats required, just in case of gaps between 0 
and max number in step 3).
6.  Use these randomly choosen values to select from the database as 
random rows.

So hopefully its a bit clearer what Im striving for.
To me the idea above would work, but it hinges on if that rand column, 
doesn't have gaps.
If you see room for improvement, or have another idea, or if Im talking 
gibberish then by all means.

Thanks.

Doug Thompson wrote:

An incredible interpretation of


If called without the optional min, max arguments rand() returns a pseudo-random value between 0 and RAND_MAX. If you want a random number between 5 and 15 (inclusive), for example, use rand (5, 15). 


Doug



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


RE: [PHP-DB] Rand() Emulation

2003-06-24 Thread Roedel, Mark

Might it be simpler, since you're assuming the presence of PHP anyway,
to just read your entire result set into an array and then shuffle() it
and then pick off the top however-many entries?  

Of course, that won't be very memory-efficient for particularly large
data sets...


---
Mark Roedel   | "Blessed is he who has learned to laugh
Systems Programmer|  at himself, for he shall never cease
LeTourneau University |  to be entertained."
Longview, Texas, USA  |  -- John Powell


> -Original Message-
> From: Gerard Samuel [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, June 24, 2003 11:19 AM
> To: Doug Thompson
> Cc: Becoming Digital; PHP-DB
> Subject: Re: [PHP-DB] Rand() Emulation
> 
> 
> Im probably not making myself clear.
> Ultimately, my goal is to emulate mysql's ->
> SELECT * FROM TABLE ORDER BY RAND() LIMIT (X)
> 
> for other databases that do not support RAND().  So using 
> variations of php's rand(), wouldn't make sense, as it only
> picks one value out of a range of values, that are numerical 
> in nature.
> 
> But here is an idea, that Im thinking about, but haven't 
> gotten to the code as yet.
> 1.  In the tables that I want random values from, create a "rand" 
> column, that contains incremental numerical values for each row.
> 2.  select the the maximum number from the rand column.
> 3.  Assume that there are no gaps between 0 and this max number.
> 4.  Create an array of numbers with values between 0 and max number.
> 5.  Use array_rand() to "randomly" choose (x) values from the array 
> created in step 4.  (I may choose maybe a 1 or 2 values more than
> whats required, just in case of gaps between 0 and max number in
> step 3).
> 6.  Use these randomly choosen values to select from the database as 
> random rows.
> 
> So hopefully its a bit clearer what Im striving for.
> To me the idea above would work, but it hinges on if that 
> rand column, doesn't have gaps.
> If you see room for improvement, or have another idea, or if 
> Im talking 
> gibberish then by all means.
> 
> Thanks.
> 
> Doug Thompson wrote:
> 
> >An incredible interpretation of
> >
> >
> >If called without the optional min, max arguments rand() 
> returns a pseudo-random value between 0 and RAND_MAX. If you 
> want a random number between 5 and 15 (inclusive), for 
> example, use rand (5, 15). 
> >
> >
> >Doug
> >
> 
> 
> -- 
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 
> 

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Rand() Emulation

2003-06-24 Thread Gerard Samuel
Yes its more than possible to do as you suggested, but as you implied, 
not very friendly,
when it comes to large result sets...

Roedel, Mark wrote:

Might it be simpler, since you're assuming the presence of PHP anyway,
to just read your entire result set into an array and then shuffle() it
and then pick off the top however-many entries?  

Of course, that won't be very memory-efficient for particularly large
data sets...


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


Re: [PHP-DB] Rand() Emulation

2003-06-24 Thread Becoming Digital
> Ultimately, my goal is to emulate mysql's ->
> SELECT * FROM TABLE ORDER BY RAND() LIMIT (X)
>
> for other databases that do not support RAND().

Which are those?

> To me the idea above would work, but it hinges on if that rand column,
> doesn't have gaps.

Use your table's primary key, assuming there is one.  This saves creating what
is otherwise unncessary data, but you'll still face "gaps" if records are
deleted.

Edward Dudlik
Becoming Digital
www.becomingdigital.com

Did I help you?  Want to show your thanks?
www.amazon.com/o/registry/EGDXEBBWTYUU


- Original Message -
From: "Gerard Samuel" <[EMAIL PROTECTED]>
To: "Doug Thompson" <[EMAIL PROTECTED]>
Cc: "Becoming Digital" <[EMAIL PROTECTED]>; "PHP-DB"
<[EMAIL PROTECTED]>
Sent: Tuesday, 24 June, 2003 12:19
Subject: Re: [PHP-DB] Rand() Emulation


Im probably not making myself clear.
Ultimately, my goal is to emulate mysql's ->
SELECT * FROM TABLE ORDER BY RAND() LIMIT (X)

for other databases that do not support RAND().  So using variations of
php's rand(), wouldn't make sense,
as it only picks one value out of a range of values, that are numerical
in nature.

But here is an idea, that Im thinking about, but haven't gotten to the
code as yet.
1.  In the tables that I want random values from, create a "rand"
column, that contains incremental numerical values for each row.
2.  select the the maximum number from the rand column.
3.  Assume that there are no gaps between 0 and this max number.
4.  Create an array of numbers with values between 0 and max number.
5.  Use array_rand() to "randomly" choose (x) values from the array
created in step 4.  (I may choose maybe
a 1 or 2 values more than whats required, just in case of gaps between 0
and max number in step 3).
6.  Use these randomly choosen values to select from the database as
random rows.

So hopefully its a bit clearer what Im striving for.
To me the idea above would work, but it hinges on if that rand column,
doesn't have gaps.
If you see room for improvement, or have another idea, or if Im talking
gibberish then by all means.

Thanks.

Doug Thompson wrote:

>An incredible interpretation of
>
>
>If called without the optional min, max arguments rand() returns a
pseudo-random value between 0 and RAND_MAX. If you want a random number between
5 and 15 (inclusive), for example, use rand (5, 15).
>
>
>Doug
>





-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Rand() Emulation

2003-06-24 Thread Jason Wong
On Wednesday 25 June 2003 00:19, Gerard Samuel wrote:
> Im probably not making myself clear.
> Ultimately, my goal is to emulate mysql's ->
> SELECT * FROM TABLE ORDER BY RAND() LIMIT (X)
>
> for other databases that do not support RAND().  So using variations of
> php's rand(), wouldn't make sense,
> as it only picks one value out of a range of values, that are numerical
> in nature.
>
> But here is an idea, that Im thinking about, but haven't gotten to the
> code as yet.
> 1.  In the tables that I want random values from, create a "rand"
> column, that contains incremental numerical values for each row.
> 2.  select the the maximum number from the rand column.
> 3.  Assume that there are no gaps between 0 and this max number.
> 4.  Create an array of numbers with values between 0 and max number.
> 5.  Use array_rand() to "randomly" choose (x) values from the array
> created in step 4.  (I may choose maybe
> a 1 or 2 values more than whats required, just in case of gaps between 0
> and max number in step 3).
> 6.  Use these randomly choosen values to select from the database as
> random rows.

Here's a trick taken from Paul DuBois' MySQL book:

 SELECT ..., some_column * 0 + RAND() as rand_col FROM ... ORDER BY rand_col;

I've not tried it myself so YMMV.

-- 
Jason Wong -> Gremlins Associates -> www.gremlins.biz
Open Source Software Systems Integrators
* Web Design & Hosting * Internet & Intranet Applications Development *
--
Search the list archives before you post
http://marc.theaimsgroup.com/?l=php-db
--

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Rand() Emulation

2003-06-24 Thread Gerard Samuel
Becoming Digital wrote:

Ultimately, my goal is to emulate mysql's ->
SELECT * FROM TABLE ORDER BY RAND() LIMIT (X)
for other databases that do not support RAND().
   

Which are those?

Currently mySQL, PostgreSQL, and MSSQL.

 

To me the idea above would work, but it hinges on if that rand column,
doesn't have gaps.
   

Use your table's primary key, assuming there is one.  This saves creating what
is otherwise unncessary data, but you'll still face "gaps" if records are
deleted.
Unfortunately, my primary keys are not numerical, thus the creation of a 
column with numerical values.

Hopefully by next week, I should be ready to try out my psuedo code, and 
see how it plays out.
If it doesn't work out, I guess no random selection happening in my code :)

Thanks

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


RE: [PHP-DB] Rand() Emulation

2003-06-24 Thread Boaz Yahav
ORDER BY RAND() is not very performance wise.
Try to do the rand outside of the DB and get your random record 
with that number.

Sincerely

berber

Visit http://www.weberdev.com/ Today!!!
To see where PHP might take you tomorrow.


-Original Message-
From: Gerard Samuel [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 24, 2003 6:19 PM
To: Doug Thompson
Cc: Becoming Digital; PHP-DB
Subject: Re: [PHP-DB] Rand() Emulation


Im probably not making myself clear.
Ultimately, my goal is to emulate mysql's ->
SELECT * FROM TABLE ORDER BY RAND() LIMIT (X)

for other databases that do not support RAND().  So using variations of 
php's rand(), wouldn't make sense,
as it only picks one value out of a range of values, that are numerical 
in nature.

But here is an idea, that Im thinking about, but haven't gotten to the 
code as yet.
1.  In the tables that I want random values from, create a "rand" 
column, that contains incremental numerical values for each row. 2.
select the the maximum number from the rand column. 3.  Assume that
there are no gaps between 0 and this max number. 4.  Create an array of
numbers with values between 0 and max number. 5.  Use array_rand() to
"randomly" choose (x) values from the array 
created in step 4.  (I may choose maybe
a 1 or 2 values more than whats required, just in case of gaps between 0

and max number in step 3).
6.  Use these randomly choosen values to select from the database as 
random rows.

So hopefully its a bit clearer what Im striving for.
To me the idea above would work, but it hinges on if that rand column, 
doesn't have gaps.
If you see room for improvement, or have another idea, or if Im talking 
gibberish then by all means.

Thanks.

Doug Thompson wrote:

>An incredible interpretation of
>
>
>If called without the optional min, max arguments rand() returns a 
>pseudo-random value between 0 and RAND_MAX. If you want a random number
between 5 and 15 (inclusive), for example, use rand (5, 15).
>
>
>Doug
>


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php