Ravens PHP Scripts: Forums
 

 

View next topic
View previous topic
Post new topic   Reply to topic    Ravens PHP Scripts And Web Hosting Forum Index -> How To's
Author Message
CodyG
Life Cycles Becoming CPU Cycles


Joined: Jan 02, 2003
Posts: 712
Location: Vancouver Island

PostPosted: Tue May 11, 2004 11:59 am Reply with quote

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>   
 
View user's profile Send private message
sixonetonoffun
Spouse Contemplates Divorce


Joined: Jan 02, 2003
Posts: 2496

PostPosted: Tue May 11, 2004 2:21 pm Reply with quote

Tried ORDER BY RAND() ?
 
View user's profile Send private message
CodyG
PostPosted: Wed May 12, 2004 11:11 am Reply with quote

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.
 
sixonetonoffun
PostPosted: Wed May 12, 2004 2:37 pm Reply with quote

Shouldn't it be LIMIT $numberofstars or something like that?
 
CodyG
PostPosted: Wed May 12, 2004 2:57 pm Reply with quote

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? Only registered users can see links on this board! Get registered or login! 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.
 
sixonetonoffun
PostPosted: Wed May 12, 2004 3:32 pm Reply with quote

DOH!
Try sql_numrows instead of sql_num_rows

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


Joined: May 02, 2003
Posts: 1389
Location: Puerto Rico

PostPosted: Wed May 12, 2004 3:42 pm Reply with quote

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";
 
View user's profile Send private message Visit poster's website
CodyG
PostPosted: Wed May 12, 2004 3:43 pm Reply with quote

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
 
chatserv
PostPosted: Wed May 12, 2004 3:44 pm Reply with quote

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
 
CodyG
PostPosted: Wed May 12, 2004 3:54 pm Reply with quote

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 ?
 
chatserv
PostPosted: Wed May 12, 2004 4:47 pm Reply with quote

Sorry about the sex change, the nick had me fooled, did you try what i posted above?
 
CodyG
PostPosted: Wed May 12, 2004 5:02 pm Reply with 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
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
 
sixonetonoffun
PostPosted: Wed May 12, 2004 5:16 pm Reply with quote

4No Stars 10No Stars
I'd check into that looks like the limit and the variable are mixed there.
 
CodyG
PostPosted: Wed May 12, 2004 6:36 pm Reply with quote

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
 
CodyG
PostPosted: Wed May 12, 2004 10:53 pm Reply with quote

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
 
chatserv
PostPosted: Wed May 12, 2004 11:07 pm Reply with quote

Open sql_layer.php and around line 238 find:
Code:
        $rows=mysql_num_rows($res);

        return $rows;

After that add:
Code:
echo mysql_error();
 
CodyG
PostPosted: Thu May 13, 2004 2:00 am Reply with quote

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
 
sixonetonoffun
PostPosted: Thu May 13, 2004 5:23 am Reply with quote

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.
 
chatserv
PostPosted: Thu May 13, 2004 9:22 am Reply with quote

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;;
 
CodyG
PostPosted: Thu May 13, 2004 9:51 am Reply with quote

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
 
chatserv
PostPosted: Thu May 13, 2004 9:56 am Reply with quote

I have a feeling the ORDER BY RAND() is affecting the query
 
CodyG
PostPosted: Thu May 13, 2004 10:15 am Reply with quote

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...
 
chatserv
PostPosted: Thu May 13, 2004 10:27 am Reply with quote

Take out
Code:
, $numberofstars
 
CodyG
PostPosted: Thu May 13, 2004 12:22 pm Reply with quote

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: Only registered users can see links on this board! Get registered or login!

unfortunately, it hasn't said anything to me yet. Confused
 
chatserv
PostPosted: Thu May 13, 2004 1:07 pm Reply with quote

My guess is id should be prefixed according to the table it belongs to i.e. pr.id or ph.id
 
Display posts from previous:       
Post new topic   Reply to topic    Ravens PHP Scripts And Web Hosting Forum Index -> How To's

View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You can attach files in this forum
You can download files in this forum


Powered by phpBB © 2001-2007 phpBB Group
All times are GMT - 6 Hours
 
Forums ©