franzwarning Posted March 17, 2011 Share Posted March 17, 2011 How should I go about keeping track of photos that a user has rated, since I only want the user to rate the photo once? Can I store arrays in a mysql database? thanks, George Quote Link to comment Share on other sites More sharing options...
gizmola Posted March 17, 2011 Share Posted March 17, 2011 Assuming you have two tables, photo and user, and each has an id (photo_id, user_id) as the primary key, you put a table between the two. Let's assume you call this table "photorating" photorating -------------- photorating_id (primary key, autoincrement) users_id (fk to users table) photo_id (fk to photo table) rating tinyint created_on timestamp You stick a row into this table for a rating. Add a unique index on user_id, photo_id and it becomes impossible for a user to have more than one rating row in the table. You have a variety of options for how you want to deal with the rating ... you can look up the rating, or simply try the update and handle the failure/exception that will occur due to the unique index. Quote Link to comment Share on other sites More sharing options...
franzwarning Posted March 17, 2011 Author Share Posted March 17, 2011 excellent thanks! Quote Link to comment Share on other sites More sharing options...
franzwarning Posted March 17, 2011 Author Share Posted March 17, 2011 what does fk mean? Quote Link to comment Share on other sites More sharing options...
aabid Posted March 17, 2011 Share Posted March 17, 2011 what does fk mean? foreign key i guess Quote Link to comment Share on other sites More sharing options...
franzwarning Posted March 17, 2011 Author Share Posted March 17, 2011 Quote Link to comment Share on other sites More sharing options...
Zane Posted March 17, 2011 Share Posted March 17, 2011 a foreign key is just that.. a foreign key. Foreign as in, the key doesn't exist in the current table. a fk to users would just be a one of the primary keys from the users table. Quote Link to comment Share on other sites More sharing options...
gizmola Posted March 17, 2011 Share Posted March 17, 2011 It was just shorthand for me pointing out that the datatype and name should match the name of the primary key in each of the associated tables. There are other benefits to defining a true foreign key constraint, but with mysql many people use the default myisam engine which has no support for constraints or foreign keys. Often people will do this: mytable id (primary key) anothertable ----------------- id mytable_id (foreign key to mytable) So the actual name doesn't have to be the same in each table. Whatever name you use for it, the important thing is that the datatype of the column should be exactly the same. Since you will also be joining on that table, it's important to have a non-unique index on it as well. 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.