Page 1 of 1

Need help finding max.

Posted: Fri Dec 18, 2009 3:05 pm
by Chinclub
I am writing a contest script and its all done but the winner part. I am trying to create a script that will run with cron jobs to find the highest voted item, get the user of the item, then find that user in a different table, get his email, and finally write the user and email in a new table. Here is my code. When I run it nothing happens. I'm still new to PHP so I'm sure I am missing a stupid mistake. Can anyone help?

Code: Select all

$sql = mysql_query("SELECT max(points) as maxpoints from pets");
            while($row = mysql_fetch_array($sql)) {
            $user = $row["user"];
            }
 
 
$newsql = mysql_query("SELECT * FROM `members` WHERE `username`='$user'");
            while($newrow = mysql_fetch_array($newsql)) {
            $email = $newrow["email"];
            }
 
$insert = mysql_query("INSERT INTO `winners` (`name`, `email`) VALUES(\"$user\",  \"$email\")");

Re: Need help finding max.

Posted: Fri Dec 18, 2009 3:44 pm
by Elven6
Hi,

Have you tried using the max() function?

http://ca3.php.net/manual/en/function.max.php

Re: Need help finding max.

Posted: Fri Dec 18, 2009 4:58 pm
by AbraCadaver
Elven6 wrote:Hi,
Have you tried using the max() function?

http://ca3.php.net/manual/en/function.max.php
Well, if you look at the code Chinclub posted, max(points) was used, so I would say, Yes.

Chinclub,

1. In your first query you don't select username, so it's not returned in the $row array.
2. Are you sure user and username are the same? Normally I would expect username to be an actual name and user to be an id of the user, maybe.

What are the results of the following (this will help identify the issue):

Code: Select all

// This is VERY important when developing/debugging
error_reporting(E_ALL);
ini_set('display_errors', '1');
 
$sql = mysql_query("SELECT user, max(points) as maxpoints from pets");
            while($row = mysql_fetch_array($sql)) {
                echo $row['user'];
            }
 
$newsql = mysql_query("SELECT username FROM `members` LIMIT 1");
            while($newrow = mysql_fetch_array($newsql)) {
                echo $row['username'];
            }
 
 

Re: Need help finding max.

Posted: Fri Dec 18, 2009 5:01 pm
by daedalus__
well what part isn't working? is there an error message?

the first query selects the column points from the row where points is the highest. but then takes that value and assigns it to the variable $user. $user is used to identify the username in the next query. i don't suppose that's what you actually wanted to do is it?

and i thought you are supposed to group items when you use an aggregate function on them?

Re: Need help finding max.

Posted: Fri Dec 18, 2009 5:03 pm
by Elven6
AbraCadaver wrote:
Elven6 wrote:Hi,
Have you tried using the max() function?

http://ca3.php.net/manual/en/function.max.php
Well, if you look at the code Chinclub posted, max(points) was used, so I would say, Yes.

Chinclub,

1. In your first query you don't select username, so it's not returned in the $row array.
2. Are you sure user and username are the same? Normally I would expect username to be an actual name and user to be an id of the user, maybe.

What are the results of the following (this will help identify the issue:

Code: Select all

$sql = mysql_query("SELECT user, max(points) as maxpoints from pets");
            while($row = mysql_fetch_array($sql)) {
                echo $row['user'];
            }
 
$newsql = mysql_query("SELECT username FROM `members` LIMIT 1");
            while($newrow = mysql_fetch_array($newsql)) {
                echo $row['username'];
            }
 
 
Arg! Don't know how I missed that! :lol: