mysql在一个Field中存储多个条目

Question

ok when a user logs in to my site he or she is taken to a page where they can preview mp3 that they might would like to download. when they click the download button my php script checks if they can download the mp3. each user can download 5 beats per month. if the user has 5 downloads left, the script updates the database like this

$q=mysql_query("UPDATE user_info SET mdr = '4' WHERE username = '$y'");

but if the user for some reason has to download the mp3 again he will lose a download. so to stop this each mp3 will have an ID number. so when the user downloads an mp3 its ID will be saved in a field called paidbeats. the script will look at the database and check if the user has downloaded the mp3 already; if so, the user will not be charged to download it again. how would i go about doing this, say, if the user paid for an mp3 with the id number 10015 and then the next day he buys 1526. how could i store multiple IDs in one field, or is there a better way of doing this?

Answer 1

Why not keep a table that tracks downloads/user (independent of beat usage). The table (eg user_downloads) could have user_id, track_id, paid_date (optional).

This would allow you to report to the user when they bought the track:

SELECT track_id,paid_date FROM user_downloads WHERE user_id=$userID

It allows you to track all the downloads of a particular track:

SELECT user_id,paid_date FROM user_downloads WHERE track_id=$trackID

And it allows you to track all past downloads, not just those from the last month.

Your download process would be:

1. Check a download-request against the download log (SELECT track_id FROM user_downloads WHERE user_id=$userID and track_id=$trackID) if found - let the user download again.
2. Allow the user to purchase the track and adjust their credits accordingly.

Maintain your existing user_info table to track download credits available. This is particularly important if you want to allow credit roll-over (each month +5 credits, so if they don't use them one month they get 10 the next), if you want to allow users to buy more credits, or if you have different account types that get more than 5 credits (rather than hard coding the 5 credit rule)

(Above SQLs are just examples, always escape your input vars ;))