An alternative solution.....

[Mossman]

I am fairly new to php so I'm aware that I'm probably doing this in the most illogical way possible so I'd appreciate some alternative suggestions.  I basically have two tables 'users' and 'celebrities', the user table contains a dream team of celebrities which the idea is they get points for based on their weekly exploits.  I want to compare a users choices with the celebrity table to get the weekly points for that celebrity and store it in a variable which will then all be added together at the end to get a weekly total of points.

 

I use this code and repeat it 11 times to get the points for each celebrity in the team:

 

$query1 = sprintf("SELECT weekpoints FROM celebrities WHERE name = '" . $choice1 ."'");

$result1 = mysql_query($query1);

if (!$result1) {

    $message  = 'Invalid query: ' . mysql_error() . "\n";

    $message .= 'Whole query: ' . $query1;

    die($message);

}

while ($row = mysql_fetch_assoc($result1)) {

 

$value1 = $row ['weekpoints'];

 

At the end I then update the relevant user record with this code: -

 

$query12 = sprintf("SELECT * FROM users WHERE username='$username'",

"UPDATE users SET weekpoints = '$update', totalpoints = '$newpoints' WHERE username = '" . $username ."' ");

$result12 = mysql_query($query12);

if (!$result12) {

    $message  = 'Invalid query: ' . mysql_error() . "\n";

    $message .= 'Whole query: ' . $query12;

    die($message);

}

while ($row = mysql_fetch_assoc($result12)) {

 

echo $newpoints;

echo "<br>";

echo $totalpoints;

 

}

 

As it stands i'm getting the wrong result at the end so something is going wrong, and the whole page is about 250 odd lines of code!  I'm sure there is probably a better way about it!  I'd appreciate any suggestions!  (And please don't laugh  )

[Psycho]

Why are you using sprintf in that manner? You are not passing any arguments so it is doing nothing.

 

The best way to do this would be to NOT store the summed values in the database. I will explain that later, but here is a better method of accomplishing the first part you had above. This is not what I am suggesting you do - just showing it for learning purposes.

 

You can get the SUM of all the points for the user's celebrity choices with this:

 

$namelist = "'$choice1','$choice2','$choice3','$choice4','$choice5','$choice6','$choice7','$choice8','$choice9','$choice10','$choice11'";

$query = "SELECT SUM(weekpoints) FROM celebrities WHERE name IN ($namelist)";
$result = mysql_query($query);

if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query;
    die($message);
}

$newpoints = mysql_result($result, 0, 0);

 

Now what I suggest you do is this:

 

You should have four tables: users, celebrities, celeb_points, user_choices

 

The user table is for your users' profile information (no point totals)

The celebrity table is just for the celebrity profile information (no point totals)

The celeb_points table is where the activities/points for each celebrity would be entered. There would be a fileds for the date, points, and the activity if you want.

The user_choices would be where you identify the celebrity choices for each user.

 

You can then get the point totals for a week or the total points for a celebrity or for a user with a single query.

 

The following query, for example, would return a list of all the users and all the points for all their celebrity choices. You could easily get the points for a particular week by utilizing a WHERE clause on the date field in celeb_points

 

SELECT u.name, SUM (cp.points)

FROM users u
LEFT JOIN user_choices uc ON uc.user_id = u.id
LEFT JOIN celebrities c ON c.id = uc.celeb_id
Left JOIN celeb_points cp ON cp.celeb_id = c.id

GROUP BY u.id

 

However, if you allow users to change their celebrity choices periodically it does make this a little more difficult, but certainly doable.

 

[Mossman]

Your a legend mjdamato!  I'm re-working the tables as you suggested and it's making life a lot easier.  I was a little confused about the LEFT JOIN section of your query though?

 

Thanks very much for the help!

[revraz]

Left Join will show you what rows don't have values in them

[Psycho]

Left Join will show you what rows don't have values in them

 

Well not quite. It means that any data in the original table will be included in the result set regardless of wehter there is associated data in the joined table.

 

For example, if you had these two tables"

authors

ID | author
1  | Mark Twain
2  | Joseph Wambaugh
3  | Stephen King
4  | Stephen Hawking

 

titles

ID | title               | auth_id
1  | The Onion Field      | 2
2  | Huckleberry Finn     | 1
3  | Carrie               | 3
4  | Cujo                 | 3
5  | Red Badge of Courage | 

 

Then this query:

SELECT author, title
FROM authors
LEFT JOIN titles on titles.auth_id = authors.id

 

Would return:

Mark Twain      | Huckleberry Finn
Joseph Wambaugh | The Onion Field
Stephen King    | Carrie
Stephen King    | Cujo
Stephen Hawking | NULL

 

However a RIGHT JOIN would return records for all titles - regardless if there is an associated record

Mark Twain      | Huckleberry Finn
Joseph Wambaugh | The Onion Field
Stephen King    | Carrie
Stephen King    | Cujo
NULL            | Red Badge of Courage

 

After further though those JOINS may need to be RIGHT joins - it just depends on how you want the data returned.