Selecting data in random order

[Anon]

Hi
I'm trying to retrieve data from a MYSQL database in random order. Here is the code I came up with after looking at some others. However this still gives me an error
"Supplied argument is not a valid MySQL result resource"

$cresult = mysql_query("SELECT * FROM dept, job, type where dept.dept_id=job.job_id AND type.type_id=job.job_id and jobtype='$typeofjob' and approve='yes'", $db);
$count = mysql_num_rows($cresult);

srand((double)microtime()*1000000);
for ($k=0; $k<5; $k++)
{
$random[$k]=(rand (1,$count));
echo $random[$k];
}

$sql = "SELECT * FROM dept, job, type where dept.dept_id=job.job_id AND type.type_id=job.job_id and jobtype='$typeofjob' and approve='yes' limit 5";
$result = mysql_query($sql,$db);

while ($myrow = mysql_fetch_array($result))

I'd greatly appreciate any help with this or any other alternative suggestion.
Thanks
Tejas

(I also tried the rand function, didnt seem to work)

[studot]

I encountered a message like this when I was using AND in SQL statements. I got around it by replacing the first AND with HAVING.

Hope this helps,

StuG

[Anon]

No luck with 'HAVING'

[studot]

Have you tried executing the SQL statements in isolation on the server or through something like phpMyAdmin?

StuG

[Anon]

Thanks a lot Stuart for your assistance but I figured another way of doing it. I first pulled all the id into an array and then randomly picked 5. And then using 'IN' I picked out the information corresponding to the 5 ids'.
I'll paste it below for anyone to refer to it.

---

$cresult = mysql_query("SELECT job_id FROM dept, job, type where dept.dept_id=job.job_id AND type.type_id=job.job_id and jobtype='$typeofjob' and approve='yes'", $db);

$count = mysql_num_rows($cresult);

while ($myrow = mysql_fetch_array($cresult))
{
$id[] = $myrow["job_id"];
}

srand((double)microtime()*1000000);
for ($k=0; $k<5; $k++)
{
$random[$k]=(rand (1,$count));
$temp[$k] = $id[$random[$k]];
}

$temp = implode (",", $temp);

$sql = "SELECT * FROM dept, job, type WHERE dept.dept_id=job.job_id AND type.type_id=job.job_id AND jobtype='$typeofjob' AND approve='yes' AND job_id IN ($temp)";
$result = mysql_query($sql,$db);

while ($myrow = mysql_fetch_array($result))

---

Have fun
Tejas

[Anon]

Just FYI.

Even though you seem to have figured out what you were looking for... since you're using mysql and it's a good idea to keep current 🙂 ... Starting with version 3.23 (I believe) they added a rand function. Ex:

select name from people order by rand() limit 1;

I've found it to be quite useful.

Nick

[Anon]

At the bottom of my post I did mention that I tried it, I would love to use it but it kept giving me the same error.

I found the following in some documentation which made me look for an alternate solution

"You can't use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times."

Tejas

[Anon]

try this:

SELECT , Sno0+rand() as rand_col FROM table_name WHERE _ ORDER BY rand_col