I'm moving this thread to the MySQL forum 'cause that's what it's turning into!
Welcome to database normalization 101.
Alrightey. So you have concerts, and you have Users. Users can save as many events as they want, and more than one User can save any given concert. You have what we like to call a "many-to-many" relationship.
Many-to-many relationships between two tables actually requires
three tables:
- Data_Concerts
- Data_Users
- Map_UserConcert
Map_UserConcert tells you what Concerts each User has saved:
-
mysql> SELECT * FROM `Map_UserConcert` LIMIT 5;
-
+--------+-----------+
-
| userid | concertid |
-
+--------+-----------+
-
| 1| 158|
-
| 8| 32|
-
| 2| 60|
-
| 12| 204|
-
| 1| 1|
-
+--------+-----------+
-
5 rows in set (0.0000 sec)
-
As you can see (at least by this sample of 5 rows), User #1 saved concerts 1 & 158; User #2 saved concert 60; User #8 saved concert 32 and User #12 saved concert 204.
For best results, you'll be wanting to create a view:
-
CREATE VIEW `View_SavedConcerts` AS SELECT * FROM (`Data_Users` LEFT JOIN `Map_UserConcert` USING(`userid`) LEFT JOIN `Data_Concerts` USING(`concertid`)) ORDER BY `userid`, `concertid` ASC;
-
Which would give you something like this:
-
mysql> SELECT * FROM `View_SavedConcerts` WHERE `userid` = '1' AND `Date_Input` > NOW();
-
+--------+------+-----------+---------+-------+---------+
-
| userid | Name | concertid | City | State | etc.... |
-
+--------+------+-----------+---------+-------+---------+
-
| 1| John | 158 | Chicago | IL |
-
+--------+------+-----------+---------+-------+
-
| etc... |
-
+--------+
-
And boom; all in one table, you have your User and concert data.
12
