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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Bac
Forum Newbie
Posts: 3
Joined: Fri Jun 24, 2005 10:23 am

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

Post 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
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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).
Bac
Forum Newbie
Posts: 3
Joined: Fri Jun 24, 2005 10:23 am

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
Bac
Forum Newbie
Posts: 3
Joined: Fri Jun 24, 2005 10:23 am

Post 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.
Post Reply