tears out hair* please help!!!

[satch333]

ok, here is my problem: (well, at least my coding problem 8-) )

I have a mysql table called "facts", in this table there are two feilds, entry_id (primary key, auto increment) and fact (text). What I need to do is randomly select a fact out of the table. Now I know I could just count the rows, get a random number between 1 and $num_rows, and get that row WHERE entry_id = $random_num. BUT, I have deleted some entries, so there are some holes in the entry_id's. I find that once in a while, the random number is an entry that is gone... PLEASE HELP!! is there a way to get a random row (fact) and be sure that there is a fact there?? I am ok at this stuff but still a novice..

[Anon]

to quote the manual...

"In MySQL Version 3.23, you can, however, do: SELECT * FROM table_name ORDER BY RAND() "

so I'm guessing you could do...

select * from facts order by rand() limit 1

next time, before you panic, check the manual:

http://www.mysql.com/

[tomhath]

Use the random number from 0 to the number of rows-1 as the offset of a LIMIT

select entry from facts limit $random_num,1

[Anon]

read this:

http://www.hvt-automation.nl/yapf/faq.php?cmd=viewitem&itemid=68

[tomhath]

Craig's way is better. But somehow, ordering by a random number seems like an oxymoron...

[satch333]

I have tried that a bunch of times and I get:

Warning: Supplied argument is not a valid MySQL result resource in /my_dir/my_file

I have checked my database connection and table name and all is good. That is why I posted, it does seem to work?

here is my code:

$host = "my_host";
$db_user = "my_user";
$db_pass = "my_pass";
$db_used = "My_db";

$connection = @mysql_connect("$host","$db_user","$db_pass")
or die ("could not connect.");

$db = @mysql_select_db($db_used, $connection)
or die ("could not connect.");

//-----------------------------------------------------

$result=MYSQL_QUERY("select * from random_facts order by rand() limit 1 ");

$row=MYSQL_FETCH_ARRAY($result);

print( "$result[0]");

mysql_close($connection);

I know the rule of checking the board and the manual first, but it seems to me like I am doing it right.. sorry, like I said.. I am kinda new at this..

and THANK YOU for your reply! 🙂

[satch333]

I dont't think the random number is the problem, I have this script that gets a random number from the totale number of rows:
#------------------------------------
$result=MYSQL_QUERY("SELECT fact FROM random_facts");

$num_rows = MYSQL_NUM_ROWS($result);

$random_num = rand(0,$num_rows);

print("$num_rows<br>");
print("$random_num");
#--------------------------------------

when I refresh the page it gives a random number, but when I tell it to get that row in the table in regards to entry_id, (select fact WHERE entry_id = $random_num) sometimes that entry_id is gone and it returns nothing.. that (I think) is my problem.. is there a way to tell if that result is emtpy and look for a new one until I find a row with content? and BTW: THANKS for the response

[satch333]

and as a correction to my first post, the table is in fact called "random_facts", not just fact... sorry

[Anon]

first of all, eliminate the @ signs in your code. That is why you don't know what the problem is. The SQL isn't the problem. I'm guessing you are not getting the information out of the result set properly. You might check in to that.

craig

[Anon]

<?php
$numsql = mysql_query("SELECT FROM random_facts");
$num = mysql_num_rows($numsql);
$randnum = rand(1, $num);
$result = mysql_query("SELECT FROM random_facts WHERE entry_id = '$randnum'");
if (mysql_num_rows($result) < 1) {
$randnum = rand(1, $num);
$result = mysql_query("SELECT * FROM random_facts WHERE entry_id = '$randnum'");
}
?>

[Anon]

oops, i made a mistake

<?php
$numsql = mysql_query("SELECT FROM random_facts");
$num = mysql_num_rows($numsql);
$randnum = rand(1, $num);
$result = mysql_query("SELECT FROM random_facts WHERE entry_id = '$randnum'");
if (mysql_num_rows($result) < 1) {
do {
$randnum = rand(1, $num);
$result = mysql_query("SELECT * FROM random_facts WHERE entry_id = '$randnum'");
} while (mysql_num_rows($result) => 1);
}
?>

[satch333]

YOU RULE!!!! I made it work with the one modification:
On you line
#----------------
while (mysql_num_rows($result) => 1);

I made it:

#----------------------
while (mysql_num_rows($result) <= 1);

and it returns a result everytime! THANK YOU!!!!

[satch333]

no, I am not sure why.... but it does,, I've tesed it like twenty times....

[robertjackson]

Given all the replies, I'm probably going to say something to embarrass myself (given that I'm already making coding mistakes in my own work from lack of sleep)...

Shouldn't your line
print( "$result[0]");
be
print( "$row[0]"); // or $row[1]
?

Rob

[Anon]

Yeah, so it works. You are also hitting the database three times (potentially) for something you can do with one. What happens when the facts table gets so big you really can't take the time to "select *"...

Read the YAPF article (in response to this thread "plus EVEN MORE!"). You are really going about this the hard way.

[radley]

Well, i dunno, you tell me a better way.

[Anon]

read the damn article. It tells you a better way.