Page 1 of 1

I'm guessing I need an "array" of some sort

Posted: Fri Jun 24, 2005 10:31 am
by Bac
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

Posted: Fri Jun 24, 2005 10:41 am
by phpScott
sounds like you need to read up on db normalization.

you should use a joining table between the 2

table_id_1, table_id_2

Posted: Fri Jun 24, 2005 10:43 am
by timvw
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).

Posted: Fri Jun 24, 2005 11:08 am
by Bac
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.

Posted: Fri Jun 24, 2005 11:56 am
by timvw
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)

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

Posted: Fri Jun 24, 2005 12:03 pm
by Bac
Gotcha, that makes sense. Unless there's a way to do it without a third table that's the route I'll most likely follow.