Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > PHP/MySQL > Beginning PHP
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Beginning PHP Beginning-level PHP discussions. More advanced coders should post to the Pro PHP forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Beginning PHP section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 21st, 2003, 10:32 PM
Authorized User
 
Join Date: Jun 2003
Location: San Jose, CA, USA.
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default Random MySQL Query

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
Reply With Quote
  #2 (permalink)  
Old August 22nd, 2003, 05:06 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 256
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 ;)
Reply With Quote
  #3 (permalink)  
Old August 22nd, 2003, 12:23 PM
Friend of Wrox
Points: 2,570, Level: 21
Points: 2,570, Level: 21 Points: 2,570, Level: 21 Points: 2,570, Level: 21
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: San Diego, CA, USA
Posts: 836
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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/
Reply With Quote
  #4 (permalink)  
Old August 22nd, 2003, 02:23 PM
Authorized User
 
Join Date: Jun 2003
Location: San Jose, CA, USA.
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #5 (permalink)  
Old August 22nd, 2003, 04:01 PM
Friend of Wrox
Points: 2,570, Level: 21
Points: 2,570, Level: 21 Points: 2,570, Level: 21 Points: 2,570, Level: 21
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: San Diego, CA, USA
Posts: 836
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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/
Reply With Quote
  #6 (permalink)  
Old November 18th, 2003, 12:44 AM
Registered User
 
Join Date: Nov 2003
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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()

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Random Number Function Query rsm42 ASP.NET 1.0 and 1.1 Basics 7 May 16th, 2007 05:04 AM
php and mysql query ghari PHP Databases 1 February 12th, 2006 06:43 PM
MySQL syntax in query Tachyon Beginning PHP 1 June 26th, 2004 08:42 PM
MySQL query junemo MySQL 14 June 10th, 2004 10:11 PM
Impossible MySQL Query? mrcornelia SQL Language 4 November 18th, 2003 03:08 AM



All times are GMT -4. The time now is 12:52 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.