here is an example of MySQL code to pick a random record using some select criteria. i use it in a homepage for an online store where my client has random items from his catalog displayed as "featured" items (really nothing more than picking a random record from the 10,000+ items!)...
$mysql_query="SELECT UIN,EXTENDED_DESC,PIC_FILENAME,LOGO_ID,LIST_PRICE,UNIT from $items_database order by rand() limit 1"; --- Octavian Rasnita <[EMAIL PROTECTED]> wrote: > First I need to tell you that I am not a MySQL > specialist and my opinion > might be wrong butI think that: > > 1. You'll better use where day=$day and where > month=$month because it works > faster than using the "like" operator. > > 2. I think MySQL has a function for returning random > numbers, so you better > use that function instead. > It will return a single row that match a criteria > from the database and not > a lot of records that need to be processed by Perl > after that. > You might check MySQL documentation for finding out > this function. > > What you want, I think that it can be made with a > single SQL line and you > will need to get the data from a single row using > Perl. > > Teddy, > Teddy's Center: http://teddy.fcc.ro/ > Email: [EMAIL PROTECTED] > > ----- Original Message ----- > From: "Al Hospers" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Friday, November 22, 2002 11:03 PM > Subject: arrays & lists > > > hi > > I know I am missing a lot in my knowledge, but I'm > trying to figure > something out & seemingly am in a hole... > > the task is as follows: > > 1) query a mysql database for as many records as > match a criteria > (I can do this OK) > > 2) put the resulting records, how ever many there > are, into a list or an > array > > 3) count the number of records I have retrived > (I can do this OK) > > 4) choose a random record number > (I can do this) > > 5) get the record corresponding to the random record > number from the array > > 6) get a particular field from the stored record > > I am using DBI to get the data from the database. > here is what I have so > far: > > sub getRandomRecord{ > my $cgi = shift; > my $dbh = shift; > my $month = shift; > my $day = shift; > > my $searchResult; > my $returnValue; > > #prepare and execute SQL statement > $sqlstatement = "SELECT * FROM $TABLE WHERE > month like $month and day > like $day"; > $sth = executeSQLStatement($sqlstatement, $dbh); > > $counter = 0; > > # put the records returned in an array/list & > count how many > while ($searchResult = $sth->fetchrow_array() ) > { > # get the 4th field from the record in the array > & put it in the list > my @list = ($searchResult[3]); > ++$counter; > } > > # pass the counter to the random integer routine > & get a value back > my $randomNumber = getRandomNumber($counter); > > $returnValue = @list[$randomNumber]; > > # clean up the DBI > $sth->finish(); > > return $returnValue > } > > > > > -- > To unsubscribe, e-mail: > [EMAIL PROTECTED] > For additional commands, e-mail: > [EMAIL PROTECTED] > > > > > > -- > To unsubscribe, e-mail: > [EMAIL PROTECTED] > For additional commands, e-mail: > [EMAIL PROTECTED] > __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus – Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
