change select statement to random?

Posted: Thu May 13, 2004 1:27 pm

All I've really ever done with RAND() was like this
$sql = "SELECT lid, title,url, urlban, description FROM ".$prefix."_Top_Sites ORDER BY rand() DESC";

Hope I haven't really thrown you off with that.

If was me I'd have just used ORDER BY rand(id) LIMIT 0, 10
But I think the way your going about it is trying to generate a better randomness?

Posted: Thu May 13, 2004 1:35 pm

oh my lucky supernovas and cs great help!! I put a pr. in front of the id= and I got a picture!!! But just one picture won't fly. So I'll put $numberofstars back in there ... (whaw!) it broke again. I need at least 5 random pics, cause one is going to be worse than the meatmarket the top 10 are perceived to be.

So how do I get $numberofstars back into the select? And why is it selecting profiles without pictures? Like the random thing isn't connecting with the userphotos. hmm

Code:

$min=1;


$max=200;


$random_id=RAND($min,$max);


        $sql = "select pr.ffusername,pr.birth, 


pr.sex,pr.photo,pr.photopath from ".$user_prefix."_user_profile1 as pr,".$user_prefix."_user_photo as ph where pr.ffusername=ph.ffusername AND pr.approved=1 AND ph.approved=1 AND pr.id=$random_id";


        $result = sql_query($sql, $dbi);


        $nrows  = sql_num_rows($result,$dbi);


        echo "$sql";

no worries six ... I expect starts and stops and go here and go there ... seems to be nominal.

Posted: Thu May 13, 2004 1:43 pm

Code:

$min=1; 


$max=200; 


$random_id=RAND($min,$max); 


        $sql = "select pr.ffusername,pr.birth, 


pr.sex,pr.photo,pr.photopath from ".$user_prefix."_user_profile1 as pr,".$user_prefix."_user_photo as ph where pr.ffusername=ph.ffusername AND pr.approved=1 AND ph.approved=1 AND pr.id='$random_id' ORDER BY rand()  LIMIT 0,$numberofstars"; 


        $result = sql_query($sql, $dbi); 


        $nrows  = sql_num_rows($result,$dbi); 


        echo "$sql";

Edited it to include the order and limit to see if they work now.

Posted: Thu May 13, 2004 2:27 pm

it didn't change anything ... sometimes it does show one photo, or it doesn't show any.

the echo sql says:

Quote:

select pr.ffusername,pr.birth, pr.sex,pr.photo,pr.photopath from nuke_user_profile1 as pr,nuke_user_photo as ph where pr.ffusername=ph.ffusername AND pr.approved=1 AND ph.approved=1 AND pr.id='139' ORDER BY rand() LIMIT 0,10No Stars

there is that 10 from $numberofstars, but there are no pictures.

Posted: Thu May 13, 2004 2:37 pm

Any change if you replace 10No Stars with just 10 ?

I get this error:
corresponds to your MySQL server version for the right syntax to use near '10No Stars LIMIT 0, 30' at line 5

Posted: Thu May 13, 2004 2:47 pm

I couldn't really test this I only have one Friend lol
But this seems to work

SELECT pr.ffusername, pr.birth, pr.sex, pr.photo, pr.photopath
FROM nuke_user_profile1 AS pr, nuke_user_photo AS ph
WHERE pr.ffusername = ph.ffusername AND pr.approved = 1 AND ph.approved = 1
ORDER BY rand( pr.id )
LIMIT 0 , 10

Posted: Thu May 13, 2004 3:16 pm

you mean replace the $numberofstars variable, with just 10? hmm... I'll try.

nope, no difference.

I changed the rand() to RAND() (does it make a difference?) but it didn't change anything either.

It's just calling one random_id. I need it to call at least five. And all of them need to have photos.

Posted: Thu May 13, 2004 5:36 pm

Try it in phpmyadmin if it works out there then its just a matter of getting the rest to come out right.

Why do you need
pr.ffusername = ph.ffusername in the where statement?

Wouldn't approved be enough?
Something like this work ok?
SELECT pr.ffusername, pr.birth, pr.sex, pr.photo, pr.photopath
FROM nuke_user_profile1 AS pr, nuke_user_photo AS ph
WHERE pr.approved = 1 AND ph.approved = 1
ORDER BY rand( pr.id )
LIMIT 0 , 10

It should return the same random list of 10 users and their info.

Posted: Thu May 13, 2004 7:34 pm

Getting close! now I just need some pictures and some randomness

Here is the current code:

Code:

$min=1;


$max=5;


$random_id=RAND($min,$max);


        $sql = "SELECT pr.ffusername, pr.birth, pr.sex, pr.photo, pr.photopath FROM nuke_user_profile1 AS pr, nuke_user_photo AS ph WHERE pr.approved = 1 AND ph.approved = 1 ORDER BY rand( pr.id ) LIMIT 0 , 5";


        $result = sql_query($sql, $dbi);


        $nrows  = sql_num_rows($result,$dbi);


        echo "$sql";

Because one picture helps, here is what that code produces. And it produces the same five pics refresh after refresh and is not ignoring the profiles without a picture. How come?

Posted: Thu May 13, 2004 7:55 pm

I put the AS ph WHERE pr.ffusername=ph.ffusername back into it and now I've got profiles with pictures... but how come it isn't random? How come it's the same pictures refresh after refresh?

Code:

$min=1;


$max=5;


$random_id=RAND($min,$max);


        $sql = "SELECT pr.ffusername, pr.birth, 


pr.sex, pr.photo, pr.photopath FROM nuke_user_profile1 AS pr,


 nuke_user_photo AS ph WHERE pr.ffusername=ph.ffusername AND pr.approved=1 AND ph.approved=1 ORDER BY rand(pr.id) 


LIMIT 0 , 5";


        $result = sql_query($sql, $dbi);


        $nrows  = sql_num_rows($result,$dbi);


        echo "$sql";

Posted: Thu May 13, 2004 8:38 pm

All we are doing so far is making the sort order random. Now you need to make the selection random Mr. Green

Which I think your original WHERE ......... code should work now for grabbing the random userid.

Posted: Thu May 13, 2004 9:18 pm

Code:

$min=1;


$max=141;


$random_id=RAND($min,$max);


        $sql = "SELECT pr.ffusername, pr.birth, 


pr.sex, pr.photo, pr.photopath FROM nuke_user_profile1 AS pr, nuke_user_photo AS 


ph WHERE pr.ffusername=ph.ffusername AND pr.approved=1 AND ph.approved=1 AND pr.id='$random_id' ORDER BY rand(pr.id) LIMIT 0 , 6";

I added AND pr.id='$random_id' back into the WHERE statement. Now I'm getting only one picture again, and random no pics.

I have to create a separate where statement?

Posted: Thu May 13, 2004 10:29 pm

No but somehow that didn't work like I expected it to.
Maybe try removing the pr.id from ORDER BY rand(pr.id)

I was just looking over some other examples simular to this and can't find why there should be any reason for this not to be ticking way like a clock.

Like this phpbb hack for random user

function random_user()
{
global $db;

$sql = "SELECT user_id, username
FROM " . USERS_TABLE . "
WHERE user_active = '1'
AND user_id <> " . ANONYMOUS . " AND user_id <> '$newest_uid'
ORDER BY RAND() LIMIT 1";
if ( !($result = $db->sql_query($sql)) )
{
message_die(GENERAL_ERROR, 'Could not query random user data.', '', __LINE__, __FILE__, $sql);
}
return ( $row = $db->sql_fetchrow($result) ) ? $row : false;
}

The only thing I see they did any different was exclude anonymous and most recent registered. Another Ugh! Its about lights out here for tonight.

Posted: Fri May 14, 2004 7:11 pm

I had a wonderful day at the beach and tonight it's movie night ... I'll get back to this later. I would really like to see an example select for more than one random item. But that is where the $min and $max and $numberofstars are suppose to work?

Posted: Fri May 14, 2004 8:13 pm

We'll have to hunt around seems like there should be one for a book reviews or amazon center block around.

But hey I'll take the night off as long as your offering!

Posted: Sat May 15, 2004 1:22 am

Back to work for a half day tomorrow.

I've looked at the random image code in the Nuke Manual and it doesn't look like anything close to what I'm trying to do... right? [ Only registered users can see links on this board! Get registered or login! ]

It's just taking pics from the dir. I don't think that would be really helpful for me, I need to attach user id etc. to those images.

center amazon block? got one, [ Only registered users can see links on this board! Get registered or login! ]
I still don't get it.

Posted: Mon May 17, 2004 10:47 am

It's Monday morning ... back to the solution. Sort of ...

Do I need to rewrite the entire function and use an array?

Posted: Mon May 17, 2004 11:29 am

I think you should be able to do something like this (Your own layout of course)

while ($r = $db->sql_fetchrow($result)) {
echo "<tr>
<td>$r[0]</td>
<td>$r[1]</td>
<td>$r[2]</td>
<td>$r[3]</td>
<td>$r[4]</td>
<td>$r[5]</td>
<td>$r[6]</td>
<td>$r[7]</td></tr>";
}

Posted: Mon May 17, 2004 11:36 am

You know when your done with this your going to have a hot little number there? Also you've gotten so far into it you can probably make your own photobrowser mod too!

Posted: Tue May 18, 2004 12:08 am

wow !!! now I'm inspired!! Wink

Will get to work on it as soon as I get some sleep.

darn Monday got all out of control with family stuff .. then dinner meeting, then it was chatroom trivia night, then it was new members, lost passwords, Cody make me an avatar, Cody I can't get the calendar to work, Cody how come my IMs won't work ... what a day, like so many others.

Site Admin/Owner Joined: Aug 27, 2002 Posts: 17088

So? Welcome to OUR world Laughing

Posted: Sat Jun 26, 2004 10:27 pm

Thank you for the help sixonetonoffin ...
It works.

$result = sql_query("select pr.ffusername,pr.birth, pr.sex,pr.photo,pr.photopath from ".$user_prefix."_user_profile1 as pr,".$user_prefix."_user_photo as ph where pr.ffusername=ph.ffusername AND pr.approved=1 AND ph.approved=1 order by rand() DESC limit 0,$numberofstars", $dbi);

Regular Joined: Aug 23, 2003 Posts: 77

i was wondering, why you used $dbi but $db is more safer.

Posted: Sun Jun 27, 2004 6:38 am

It was in the original select statement for FF ver 2.5. The same $dbi method is also used in the new FF for ver nuke 7.2 (sigh)