how to break if something doesn't exist (This should be easy)

[Lucky2710]

I guess I'm just stupid cause it seems like every time i get stuck and i post on here and its gets answered in less than 30 min.'s and i keep getting stuck!

 

Here my script...

function scoring($user){
$query = "SELECT Game_ID, Pick FROM Cfb WHERE User_ID= 1";
$result = mysql_query($query);

$query1 = "SELECT Game_ID, Pick FROM Cfb WHERE User_ID= $user";
$result1 = mysql_query($query1);

$affected = mysql_num_rows($result);

for($i=0; $i< $affected; $i++) {
$scores =mysql_fetch_assoc($result);
$scores1 =mysql_fetch_assoc($result1);

$main = array_diff_assoc($scores,$scores);
$me = array_diff_assoc($scores, $scores1);

$m = count($me);
$m1 = count($main);

if ($m == $m1){
$points += 1;
}else{
$points += 0;
}
}

echo '<br />';
echo '<br />';
echo $user;
echo '<br />';
echo $points;
echo '<hr />';
}

$sql="SELECT  id FROM users WHERE approved=1 AND user_level !=10";
    $result=mysql_query($sql);	
while($row = mysql_fetch_object($result))
   {
    $ids[] = $row->id;
   }
   
print_r($ids);

 

Alright heres what this script does...

The script has a function that scores 1 individual user for his/her choices.

Then I'm getting the user_ids from the users table at the bottom. Taking those and plugging them back into the function to score all the users at one time.

 

Problem...

Not all users will make picks. So how do i break the script in the function if that user_id is not is the CFB table attached to picks

 

I have 2 tables im using Cfb and users

 

users controls user info such as id # name username ect..

Cfb controls the picks. When a user makes there selection it stores there User_ID, Game_ID, and Pick

 

So not all user ids are in the Cfb table. So if the script tries to input a user id thats not in the Cfb table it needs to break. Right now it just screws everything up!

 

So how can i make it break that way?

 

Thanks for any help

[RussellReal]

try this..

 

SELECT * FROM users a JOIN Cfb b ON (b.user_id = a.id) WHERE a.approved = 1 AND a.user_level != 10

[Lucky2710]

That does eliminate the extra but now im left with another problem.

 

In the Cfb table each user_id appears multiple times and even though its comparing it to users it still pulls each user_id how ever many times it exists (currently 12 times) (and that #number will be higher)

 

How can i eliminate the doubles?

[spfoonnewb]

Why does the user appear multiple times? Is there something else that can be joined on?

[Lucky2710]

In the cfb table the fields are id, User_ID, Game_ID, and Pick

In the users table the fields are id, username, full_name ect.

 

Each user has one row in the users table.

But in the Cfb table each user has a row for each pick they make.

 

I need the join because not all users are listed in the picks table and when it gets to one thats not it screws everything up. So im using a join to get only the user_id's that have made picks. But when i join them (like i expect) only users who have made picks show up, (but i didn't expect) for each user_id to show up multiple times. (its like its showing up once per entry from Cfb)

[TOA]

That does eliminate the extra but now im left with another problem.

 

In the Cfb table each user_id appears multiple times and even though its comparing it to users it still pulls each user_id how ever many times it exists (currently 12 times) (and that #number will be higher)

 

How can i eliminate the doubles?

 

Did you try DISTINCT?

[Lucky2710]

No what is DISTINCT???

[TOA]

http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html

 

Selects only distinct values. May or may not help your issue, but it's worth a try

[Lucky2710]

Thanks man that works perfectly!

 

So my final script ends up being this...  (if anyone was wondering)

$query = "SELECT DISTINCT users.id, Cfb.User_ID 
FROM users, Cfb 
WHERE users.approved = 1 AND users.user_level != 1 AND users.id = Cfb.User_ID";

$result = mysql_query($query);
$affected = mysql_num_rows($result);

for($i=0; $i< $affected; $i++) {
$Pu =mysql_fetch_array($result);

print_r($Pu);
}

[TOA]

Sweeeeeeeet 

 

Don't forget to mark it solved

[PFMaBiSmAd]

Joining your user table with the cfb table, only to get a list of users that have picks so that you can use that list to re-query your cfb table inside of a loop/function is a huge waste of time and will take three times more code than you need.

 

Someone already showed you in one of your other threads on this problem how to get the count of correct picks for each user. All you really need to do is join that with your user table to get the corresponding user names. One query and a little code to iterate over the results of the query.