change select statement to random?

Posted: Tue May 11, 2004 11:59 am

gurus of the mysql select universe... please listen to my quest. I will throw money the next time it comes my way... Confused

I need to change a result from top ten to random in the stars function of Friendfinder on my Nuke 6.9. I can turn the function off, but then the page becomes just text, and I want to keep the pictures.

From comments I found in the mysql manual, I rigged this....

Code:

$min=1;


$max=10;


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


        $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 AND id='$random_id',$numberofstars", $dbi);

It didn't work, and this was the error: (I wonder about that id= ... but is an id field in the db table.. hmm. confuddles)

Quote:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/thisday/public_html/includes/sql_layer.php on line 238
No Stars

Here is part of the original code which selects the top ten. Instead of top ten I need a random selection.

One more quick question...
I also want to divide the pic selection into two rows with 5 pics, not 1 row with 10 pics. Is that done in the html? I tried and failed. Or is there some php required?

Thanks for any help.

Code:

function stars(){


        global $dbi,$module_name,$thisyear,$currentlang,


$bgcolor1,$user_prefix,$numberofstars,$thisyear,


$userinfo,$user;


        require("modules/$module_name/data/data-$currentlang.php");


        getusrinfo($user);


        $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 click DESC limit 0,$numberofstars", $dbi);


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


        if($nrows>0){


             echo "<font color=\"centent\">";


             echo "<table align=center><tr>";


             while(list($ffusername,$birth,$sex,$photo,$photopath) = 


sql_fetch_row($result, $dbi))


             {


                  <snip rest of function>

Posted: Tue May 11, 2004 2:21 pm

Tried ORDER BY RAND() ?

Posted: Wed May 12, 2004 11:11 am

Tried this ... without the line breaks posted here.

Code:

$min=1;


$max=10;


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


        $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 AND id='$random_id', 


ORDER BY rand(), $numberofstars", $dbi);

got

Quote:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/thisday/public_html/includes/sql_layer.php on line 238

hmm, functions and variable, variables and functions .. I kind of get it, but I can't get it to work.

Thanks for helping.

Posted: Wed May 12, 2004 2:37 pm

Shouldn't it be LIMIT $numberofstars or something like that?

Posted: Wed May 12, 2004 2:57 pm

Code:

$min=1;


$max=10;


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


        $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 AND id='$random_id', ORDER BY RAND() limit 1, $numberofstars", $dbi);


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

hmmm... something like that should work? Maybe I've missed something else? http://dev.mysql.com/doc/mysql/en/SELECT.html is the page in the manual where I'm trying to get a clue. I've tried uppercase RAND(), lowercase rand() and limit by 0 and 1. I get the same error as before.

Posted: Wed May 12, 2004 3:32 pm

DOH!
Try sql_numrows instead of sql_num_rows

Somedays it just doesn't pay to get out of bed!

Posted: Wed May 12, 2004 3:42 pm

Try changing that to the following to at least find out what is getting passed through the query, often it helps detect blank variables:

Code:

$min=1; 


$max=10; 


$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 id='$random_id', ORDER BY RAND() limit 1, $numberofstars";


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


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


        echo "$sql";

Posted: Wed May 12, 2004 3:43 pm

Ah new error! Is that progress? This is the beginning of the function now...

Code:

function stars(){


        global $dbi,$module_name,$thisyear,$currentlang,$bgcolor1,


$user_prefix,$numberofstars,$thisyear,$userinfo,$user;


        require("modules/$module_name/data/data-$currentlang.php");


        getusrinfo($user);


$min=1;


$max=10;


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


        $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 AND id='$random_id', ORDER BY RAND() limit 1, $numberofstars", $dbi);


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


        if($nrows>0){


             echo "<font color=\"centent\">";


             echo "<table align=center><tr>";

Quote:

Fatal error: Call to undefined function: sql_numrows() in /home/thisday/public_html/modules/FriendFinder/functions.php on line 264

Posted: Wed May 12, 2004 3:44 pm

sixonetonoffun wrote:

DOH!
Try sql_numrows instead of sql_num_rows

Somedays it just doesn't pay to get out of bed!

In the way the code is structured it will be best if he keeps it as sql_num_rows

Posted: Wed May 12, 2004 3:54 pm

ah ... cs ... I is a she (not that it matters) and I just can't figure out why I can't figure this out. not top ten select, but random ten select... is not rocket science, right?

There is RAND() and RAND(NOW()) ... that's not going to make a difference is it? I tried both.

hmm... something about the variable $max and $min ?

Posted: Wed May 12, 2004 4:47 pm

Sorry about the sex change, the nick had me fooled, did you try what i posted above?

Posted: Wed May 12, 2004 5:02 pm

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/thisday/public_html/includes/sql_layer.php on line 238
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 id='4', ORDER BY RAND() limit 1, 4No Stars

I'm testing this on a site without 10 members in the database. I'll test it on a site with more than 10 ...

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/lovica/public_html/includes/sql_layer.php on line 238
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 id='3', ORDER BY RAND() limit 1, 10No Stars

Posted: Wed May 12, 2004 5:16 pm

4No Stars 10No Stars
I'd check into that looks like the limit and the variable are mixed there.

Posted: Wed May 12, 2004 6:36 pm

Mixed variables? Blank variables? My head is suddenly spinning...

The Nostars comes from the end of the function: echo ""._NOSTARS."<br>";

10NoStars is the result of a db table with 114 records.

4NoStars is the result of my test site, where there couldn't be more than 4 test records.

There is a post on the mysql site saying try something like this:

Code:

$min=1;


$row=mysql_fetch_assoc(mysql_query("SHOW TABLE STATUS LIKE 'table';"));


$max=$row["Auto_increment"];





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


$row=mysql_fetch_assoc(mysql_query("SELECT * FROM table WHERE id='$random_id'");

There isn't an ORDER BY or LIMIT?

So I tried

Code:

$min=1;


$max=10;


$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 id='$random_id', $numberofstars";


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


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


        echo "$sql";

But that didn't work for me either.

I'm out for the evening at my "nuke till we drop" group. Wink

so will take up this pesky result sort tomorrow am pacific time. Thanks for helping.

Cody (Catherine) G

it must have been the fringes and boots that stuck me with that nick 20 years ago

Posted: Wed May 12, 2004 10:53 pm

okies... so I can't sleep until I've worked on this some more:
This is the script bit now:

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 id='$random_id', ORDER BY RAND()LIMIT 1, $numberofstars";


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


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


        echo "$sql";


        if($nrows>0){


             echo "<font color=\"centent\">";


             echo "<table align=center><tr>";

I've been playing around with the $max, setting it at 10, 20, 200. And this is the error: Note the id="39" ??? It's like it's looking at 1 record, but should be pulling $numberofstars, which in this case should be 10. (set in FF admin)

I'm still confused.

Code:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/lovica/public_html/includes/sql_layer.php on line 238


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 id='39', ORDER BY RAND()LIMIT 1, 10No Stars

Posted: Wed May 12, 2004 11:07 pm

Open sql_layer.php and around line 238 find:

Code:

        $rows=mysql_num_rows($res);


        return $rows;

After that add:

Code:

echo mysql_error();

Posted: Thu May 13, 2004 2:00 am

Did it. From sql_layer.php

Code:

case "MySQL":


        $rows=mysql_num_rows($res);


        return $rows;


echo mysql_error();


    break;;

that was suppose to change something cs? that random id again ... 57 this time, is it getting in the way of anything?

Quote:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/lovica/public_html/includes/sql_layer.php on line 238
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 id='57', ORDER BY RAND()LIMIT 1, 10No Stars

Posted: Thu May 13, 2004 5:23 am

I'd try it without the AND id='$random_id' and change the $numberofstars to a 10 or however many results you want to get. See if it works that way make it a little simpler then add the extra stuff once your basic select statments running fine and returning a valid result.

Posted: Thu May 13, 2004 9:22 am

Retry the sql_layer.php code one more time but this time make it:

Code:

        $rows=mysql_num_rows($res);


        echo mysql_error();


        return $rows;

If that doesn't make the error change to a more descriptive one then try changing:

Code:

function sql_num_rows($res)


{


global $dbtype;


switch ($dbtype) {


 


    case "MySQL":


        $rows=mysql_num_rows($res);


        return $rows;


    break;;

to:

Code:

function sql_num_rows($res)


{


global $dbtype;


switch ($dbtype) {


 


    case "MySQL":


        if($rows=mysql_num_rows($res)) {


        return $rows;


        } else {


        print (mysql_error());


}


    break;;

Posted: Thu May 13, 2004 9:51 am

Okay ... I change the sql_layer.php and did get a different error/1

This is the select code:

Code:

$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 id='$random_id', ORDER BY RAND()LIMIT 1, $numberofstars";


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


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


        echo "$sql";

And this is the error ... syntax! yikes.

Code:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/lovica/public_html/includes/sql_layer.php on line 239


You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ' ORDER BY RAND()LIMIT 1, 10' at line 1select 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 id='101', ORDER BY RAND()LIMIT 1, 10No Stars

Posted: Thu May 13, 2004 9:56 am

I have a feeling the ORDER BY RAND() is affecting the query

Posted: Thu May 13, 2004 10:15 am

From different random member block ... maybe I can get a clue from it?? this one doesn't use any order or RAND().

Code:

$randomuser = rand(2 , $lastuser);   


$result2 = sql_query("SELECT uid, uname, user_avatar, 


user_regdate, user_posts, user_rank, url, email, 


user_viewemail FROM ".$prefix."_users WHERE uid=$randomuser", $dbi);

My current code: (there is an example in the manual comments that doesn't use the RAND()

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 id=$random_id, $numberofstars";

And my current error:

Quote:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/lovica/public_html/includes/sql_layer.php on line 239
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ' 10' at line 1select 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 id=153, 10No Stars

still that syntax error...

Posted: Thu May 13, 2004 10:27 am

Take out

Code:

, $numberofstars

Posted: Thu May 13, 2004 12:22 pm

ambiguous clause??? ... that is very cool error reporting, but what the heck does it mean.. is there a blank variable somewhere? or something not agreeing with something else? Pairing the code down to something basic is good, process of elimination.

Quote:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/lovica/public_html/includes/sql_layer.php on line 239
Column: 'id' in where clause is ambiguousselect 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 id=200No Stars

This is the code now:

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 id=$random_id";


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


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


        echo "$sql";

A response to a search is here: http://forums.devshed.com/archive/t-128713

unfortunately, it hasn't said anything to me yet. Confused

Posted: Thu May 13, 2004 1:07 pm

My guess is id should be prefixed according to the table it belongs to i.e. pr.id or ph.id