question for some experts..

motoman

Hi all,

Let me start by saying im barley a php developer.

I am developing a a php app where users pick an order of 10 items before the order of items is determined.

I have tables built user_picks, and race_results.
user_pick has columns for the 1st - 10th and points columns for each set to zero (1st |1stp, 2nd|2ndp ) when a user picks 1-10 is entered into the 1st 2nd 3rd "" "" columns points remaining at zero until the results of the race

race_results has the columns 1st-10th results.

once the results are in how would i check to see if the user_picks match the race_results table and award point to the points column in the user_pick table

array_udiff_assoc() ??

sorry if this does not make sense!
any ideas would be greatly appreciated.

motoman

Hi again,

As i cant edit yet. one more thing.. How would i go about this for each user record in the user_pick table

Stay thirsty my friends!!!
Cheers

bradgrafelman

EDIT: Just re-read your post and realized I missed the whole "points column" business. I would suggest one of two routes:

Re-design your DB schema such that both the user_picks and race_results tables only contain a single "pick" per row. You'd have columns like:
```
user_picks: user_id (FK), race_id (FK), pick_order, pick_item, pick_point
PK on (user_id, race_id)

race_picks: race_id, pick_order, pick_item
PK on (race_id, pick_order)
```
The beauty of that design is that you could design a single UPDATE statement that would process your entire user_picks table, updating all of the "pick_point" columns for every item in a given race (or even all races at once, if you wanted). You'd simply set pick_point to DEFAULT to 0, and then UPDATE it to be 1 if there EXISTS a row in race_picks where the race_id, pick_order, and pick_item match the user's selection.
Leave your current schema, but do all of the processing in PHP. Namely:
1. Retrieve the results for the given race and store them in a PHP array. For the array, use the pick_order as the key name and pick_item as the value.
2. Loop through all of the users who picked items for that race, and retrieve their picks in a PHP array. The array should be formed similarly as in #1 above.
3. For each user, use [man]array_diff_assoc/man to compare their picks with the actual results you stored in the array in #1. The resulting array, if non-empty, will give you which items they picked correctly. Use [man]array_keys/man on that resulting array to determine which "point" columns you need to UPDATE.

motoman

Studying CROSS JOIN to see if it will work.. My schema has many races and will have 100s more. im just trying to come up with way to score users after the have set their picks.

to be more clear

p = points in columns
user_picks columns || pick_id | race_id | user_id | 1st | 1stp(set to Zero) | 2nd | 2ndp |continues threw | 10th | 10thp |

race_results columns || result_id | race_id | 1st | 1stp(the points the racer receives in the race) | 2nd | 2ndp | 3rd | 3rdp | 4th | 4thp |continues threw | 40th |40thp

user_pick points columns are set to zero by default

what im trying to do is

compare the race_results to the user_picks and if any of the user_picks 1-10 = race_results 1-10 award the point from the racer to the user in the user_picks point column

I may need to change my schema im open to any suggestions.

Stay thirsty

bradgrafelman

Sorry, I think you might have been reading the original version of my reply above (before I did the 'EDIT:'). If so, re-read it and see if it makes any more sense.

If the number of picks could ever possibly become variable or simply change from 10 to some other value, option #1 (the schema redesign) would then be your best bet. Even if you don't forsee this happening, I still personally like it best since it makes the model of your data lend itself to be more readily manipulated in SQL (hence the reason you could do what you're trying to do with a single UPDATE query).

motoman

Bradgrafelman, your the best! it seems so clear now.. I'll give it a try tonight and see what i come up with. i'd much rather mysql do the work then code it with arrays.

Thanks again