random pull from MySQL database

[hermes]

Can anyone help me with the php syntax for a single call to a MySQL database - takes a RANDOM entry from the MySQL database (the database only has one table, thousands of rows). Sets it as a variable. echos this variable.
Thanks so, so much.

[eelixduppy]

You can try something like this:

$rand = [url=http://us2.php.net/manual/en/function.rand.php]rand[/url](0,$num_of_rows); //you can query the database to get num of rows if you'd like
$query = "select * from table_name";
$result = [url=http://us2.php.net/manual/en/function.mysql-query.php]mysql_query[/url]($query) or [url=http://us2.php.net/manual/en/function.die.php]die[/url]([url=http://us2.php.net/manual/en/function.mysql-error.php]mysql_error[/url]());
$info = [url=http://us2.php.net/manual/en/function.mysql-result.php]mysql_result[/url]($result,$rand);
echo $info;

Good luck!

[eeek]

Selecting the entire table when all you want is one row is not something I'd call good practice. Instead you can use LIMIT and OFFSET on the select to pick out just one row.

[Nutter]

$rs = mysql_query("SELECT field1, field2 FROM table ORDER BY RAND() LIMIT 1");
$row = mysql_fetch_row($rs);
return $row['field1'];

[eeek]

Even better, Nutter.

[eelixduppy]

Thanks for correcting my solution. I'm not an expert with MySQL ;)

[hermes]

Will this random pull from MySQL database be fast?

Another way I was thinking, instead of having all the possibilities listed in a MySQL database and randomly plucking 1, was listing all the possibilities in the actual code and plucking 1. Like this:

<?php
switch(rand(1,9)) {
case 1:
$fork = 'bang'; break;
case 2:
$fork = 'dsfdg'; break;
case 3:
$fork = 'dffdg'; break;
case 4:
$fork = 'fdgg'; break;
case 5:
$fork = 'fgdfsg'; break;
case 6:
$fork = 'fdgg'; break;
case 7:
$fork = 'fdgg'; break;
case 8:
$fork = 'ggdgg'; break;
case 9:
$fork = 'gffdsg'; break;

}

print $fork;
?>

I have thousands of possibilities so the code will run to thousands of lines. I'm just writing here for clarification that the MySQL method should run faster than the method outlined here. I think I know the answer - but would like someone more experienced to confirm. if anyone would be so kind.

[physics]

I think you should look into storing the values in an array instead... Or do order by rand limit 1. I'm not really sure what you're trying to do though so maybe I'm off here.

[physics]

Oh, just realized. Probably the fastest way would be to query the table and ask how many rows, then get a rand in that range, then just query the db for that 1 row using limit.

[jatar_k]

>> for clarification that the MySQL method should run faster than the method outlined here.

yes the mysql way would be much more efficient

[hermes]

saw this on the web. Is this a better solution?

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

[jatar_k]

that's the same as Nutter's

that is the right way of doing it

[hermes]

This is my code - seems to work (doesn't return any error messages). How do I modify it to get it to print the random entry selected to the screen?

<?php
$dbh=mysql_connect ("localhost", "china_for", "password") or die
('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("china_random");
$rs = mysql_query("SELECT frampton FROM mikey ORDER BY RAND() LIMIT 1");
$row = mysql_fetch_row($rs);
return $row['field1'];
?>

[hermes]

am really stuck. i know its a stupid Q - but would be so grateful to anyone that can help. I guess what I really want to do:

1) Put the returned entry into a variable.
2) Print the output of this variable to the screen.

[edited by: hermes at 10:09 pm (utc) on June 29, 2006]

[Nutter]

It's the return $row['field1'] that's causing the problem - really 2 problems. One, you aren't querying for a field 1. And 2, return won't output anything the way you're doing it.

Make sure the field name you're using in $row[''] is a fieldname from your query. You can change return to echo in the last line to have it output the value.

[hermes]

Addressed these probs - but still not returning anything to the screen for some reason.

<?php
$dbh=mysql_connect ("localhost", "china_for", "password") or die
('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("china_random");
$rs = mysql_query("SELECT frampton FROM mikey ORDER BY RAND() LIMIT 1");
$row = mysql_fetch_row($rs);
echo $row['frampton'];
?>

[hermes]

does this code work for any1? It doesnt for me. Its galling.

[jatar_k]

does it echo anything at all?

you could try adding an or die to your query to see if that is dying

$rs = mysql_query("SELECT frampton FROM mikey ORDER BY RAND() LIMIT 1") or die('<p>query died: ' . mysql_error());

also are there any of the rows where the 'frampton' column might be empty?

<added>the other thing is it looks like mysql_fetch_row returns a numbered array so you might want to try

echo $row[0];

[proper_bo]

$row = mysql_fetch_assoc($rs);
is what your after
if you use mysql_fetch_array you get the results as $row[0] $row[1] $row[2]
if you use mysql_fetch_assoc you get the results as $row['field1'] $row['$thisisfield2'] $row['itsfield3']

[Nutter]

It should be mysql_fetch_array if you want to use $row['field']. You can also use mysql_fetch_row, but you'll need to access the field by $row[0].

[hermes]

Have got this working! Thanks so much!

<?php
$dbh=mysql_connect ("localhost", "china_for", "password") or die
('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("china_random");
$rs = mysql_query("SELECT frampton FROM mikey ORDER BY RAND() LIMIT 1");
$row = mysql_fetch_row($rs);
echo $row[0];
?>

I am unsure what the last 2 posts meant. WIll they offer any improvement on this working code?

[jatar_k]

no it would just be a different way, if it works don't touch it ;)

[hermes]

thanks everyone so much.