Can anyone tell me why this doesn't work?

SELECT name, idnum, icon, shortdesc, show FROM portfolio WHERE show = '1' ORDER BY RAND() LIMIT 1

This always returns the same item as long as the while show = '1' piece is there. However if I remove it, it works fine. However more than one listing have the show field with a value of 1. If I remove the LIMIT 1, it returns all the correct results, but always in the same order, thus ignoring the RAND() and probably the exact reason this isn't working the way I want it to.

Here's what it's supposed to do: It's supposed to retrieve a random record from the database, but that record has to have the show field with a value of 1. Am I crazy?

----------
~cmiller

__________________
----------
~cmiller

Well, as to being crazy: you work with computers don't you?

ORDER BY should always take a field name. "ORDER BY <some floating pouint value in between 0.0 and 1.0>" means about as much to me as it appears to mean to your database manager ;)

Not exactly, Dan! ORDER BY RAND() supposed to generate a random ordering of your result set.

I searched around the net and found that a lot of people reported that queries very similar to your own do not work properly in a specific environment, namely running MySQL and PHP on Windows.

Chris, if you're running this script on Windows via PHP, try upgrading MySQL to a newer (latest?) version. I am running Windows 2000 SP3, MySQL v. 3.23.43-nt, and PHP 4.32 and I cannot reproduce your problem; the query works as expected for me.


For more info, start with:

  http://lists.evolt.org/archive/Week-...30/131159.html


Take care,

Nik
http://www.bigaction.org/

I am actually running PHP 4.0.6 on a Linux machine. I am running such an old version purely because of a compatibility conflict with a program we purchased to assist us in billing clients. It seems that this is probably the culprit and I will double check the functionality on one of my other machines that is running a newer version.

Thanks for your info!

----------
~cmiller

Out of curiosity, what compatibility issue? For the most part, newer versions with PHP can be compatible with scripts written for older versions as long as the configurations in php.ini match. 99% of all upgrade conflicts were due to the change of the default values of register_globals to "off" and error_reporting to "E_ALL".

The only other hitch I had was with some of the early experimental extensions, most notable the XSLT/Sablotron extension. The entire extension was redesigned after 4.0.6, so much in fact that the main function, xslt_process, was rewritten to accept different parameters.

If you're having these difficulties, then you can write your scripts to work across versions by stealing a concept from the long-disgruntled JavaScript masses --

$cmp = version_compare(phpversion(), "4.1.0");
if($cmp < 0)
{
   // use old xslt extension functions
}
else
{
   // use new xslt extension functions
}


This is where writing wrapper classes to extensions is a major benefit. Your entire application uses the class to access the main functionality of the extension, and if the extension itself changes, you patch the class and your application continues to work as if nothing happened.


...but you already knew that! :)


Take care,

Nik
http://www.bigaction.org/

it's a bug with mysql apparently. a workaround, as screwed up as it may be that you need to use one, would be to do something like reverse(rand()) rather than simply rand()