RE: [PHP-DB] Rand()
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()
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()
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
Re: [PHP-DB] rand()
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-capage=byoa/premxAPID=1994DI=1034SU=http://hotmail.com/encaHL=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()
-- 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() Emulation
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 quote 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). /quote 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
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
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
An incredible interpretation of quote 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). /quote 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
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 quote 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). /quote Doug -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Rand() Emulation
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 quote 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). /quote 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
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
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 quote 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). /quote Doug -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Rand() Emulation
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
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
