This is definitely a newbie question. I have two mysql tables, call them table 1 and 2. What I'm looking to do is have one field in table 2 contain multiple values/an array (I guess separated by commas) are are ID numbers from table one.
So table 2 might have column X and within it are the values "1,3,5", representing three records from table 1.
The reason I need one field and an array is additional values from table 1 could be added into the array in the field in table 2 at any time. Then, I need a way to read the array in php. This is probably basic stuff but can anyone lend a hand? Thanks, Dan
I'm guessing I need an "array" of some sort
Moderator: General Moderators
Use varchar as storage type. Use http://www.php.net/explode and http://www.php.net/implode to group the values, fe use ; as a separator.
But i wouldn't recommend storing multiple values in one rows.
Simply add 3 rows to table2 (xref_to_table1, value1), (xref_to_table1, value2), (xref_to_table1, value3).
But i wouldn't recommend storing multiple values in one rows.
Simply add 3 rows to table2 (xref_to_table1, value1), (xref_to_table1, value2), (xref_to_table1, value3).
How would a joining table work here? I haven't dealt with table joins yet. I'm not sure the explode function will help much.
Let me put the problem in practical terms. One table holds "movie" records and the other "actors". So one movie can be associated wtih an unlimited number of actors and vice versa. What I'm looking to do in php is display a movie and pull the actor IDs that were in that movie based off the movie ID and then display the actors data from their record. All the mysql and php stuff I've done thus far has been pretty basic. This solution for how to connect the two tables without having fields for actor1, actor2, etc. in the movie table (I want to avoid constantly adding new fields and having a ton of unused records beacuse one movie has 20 actors and another 3) is escaping me.
Let me put the problem in practical terms. One table holds "movie" records and the other "actors". So one movie can be associated wtih an unlimited number of actors and vice versa. What I'm looking to do in php is display a movie and pull the actor IDs that were in that movie based off the movie ID and then display the actors data from their record. All the mysql and php stuff I've done thus far has been pretty basic. This solution for how to connect the two tables without having fields for actor1, actor2, etc. in the movie table (I want to avoid constantly adding new fields and having a ton of unused records beacuse one movie has 20 actors and another 3) is escaping me.
Assuming there is a n-m relationship between movie and actor. Understand this as: each move can have multiple actors, and each actor can play in multiple movies.
table movies (movie_id, ..., )
table actors (actor_id, ..., )
table movieactors (movie_id, actor_id)
table movies (movie_id, ..., )
table actors (actor_id, ..., )
table movieactors (movie_id, actor_id)
Code: Select all
SELECT actor.*
FROM actors AS a
INNER JOIN movieactors AS am ON a.actor_id=am.actor_id
WHERE am.movie_id=$someid