Mossman Posted December 5, 2007 Share Posted December 5, 2007 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 ) Quote Link to comment Share on other sites More sharing options...
Psycho Posted December 5, 2007 Share Posted December 5, 2007 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. Quote Link to comment Share on other sites More sharing options...
Mossman Posted December 5, 2007 Author Share Posted December 5, 2007 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! Quote Link to comment Share on other sites More sharing options...
revraz Posted December 5, 2007 Share Posted December 5, 2007 Left Join will show you what rows don't have values in them Quote Link to comment Share on other sites More sharing options...
Psycho Posted December 5, 2007 Share Posted December 5, 2007 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.