CODEWITHSUNDEEP
phpmysql
acrs
acrs

Reputation: 563

mysql storing multiple entries in one Field


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?

Upvotes: 2

Views: 3254

Answers (4)

Rudu
Rudu

Reputation: 15892

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 ;))

Upvotes: 1

mfonda
mfonda

Reputation: 7993

I would create a table to keep track of all downloads, with a schema similar to the following:

user_downloads(downloadID, userID, download_date).

Then, whenever a user downloads a song, you insert a new entry into user_downloads for that download. That way, if they want to download a song again, you can just check if it is already in the download table, and if you want to check their limit, make sure the same user doesn't have five entries within the table for the same month.

Upvotes: 1

Justin Ethier
Justin Ethier

Reputation: 134167

You should use a separate table to store which MP3's a user has downloaded. The table needs to contain a user ID, an MP3 ID, and a time stamp.

You can insert an entry in the table each time a song is downloaded. To determine if a user has exceeded their monthly quota of 5, query the database to SELECT COUNT(1) for the user ID and for all timestamps in the current month.

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 452998

Storing multiple entries in one Field is a clear violation of first normal form.

Create a new table called (say) user_purchases with two columns user_id and item_id (and maybe other columns for date, price etc).

Each row would represent a particular purchase.

Upvotes: 4

Related Questions