Re: arrays & lists
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]
Re: arrays & lists
> I know I am missing a lot in my knowledge, but I'm trying to figure
> something out & seemingly am in a hole...
MySQL can do a lot of this for you, I believe...
You want a random record from the database and retrieve a particular field
from that record:
# Untested, but you would call this subroutine like so:
# getRandomRecord( $dbh, "SELECT field_Im_interested_in FROM $TABLE
# WHERE month like $month and day like $day" );
# NOTE: $sql (2nd parameter) can't already have a LIMIT in it, or any SQL
# statement which a LIMIT suffix would result in an invalid SQL statement.
sub getRandomRecord {
my( $dbh, $sql ) = @_;
my ( $sth, $randomNumber, $myValue );
$sth = $dbh->prepare( $sql );
$sth->execute;
$randomNumber = int(rand( $sth->rows ) );
$sth->finish;
# $randomNumber will be 0 <= $randomNumber < $sth->rows, which
# fits nicely with MySQL's LIMIT clause
( $myValue ) = $dbh->selectrow_array( $sql . " LIMIT $randomNumber,
1" );
return $myValue;
}
HTH & Have Fun!
Jason
> 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]
Re: arrays & lists
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]
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]
