Adding data - more than one piece in a column?

Brook · 01-25-2008, 06:26 AM

Hi All

I was just wondering what the best way of doing this would be.

Say I had a database of Actors (each one gets an id as the primary key in the actors table: So would be Actor_id, First Name, Last Name, etc).

Then say I had a page of info (say a movie info page) and I wanted to list the cast on that page. Would I simply have a column called 'actors', then just put the actor_ids in there seperated by commas?

Eg:

Actors: 124,366,697,125,123

But how would I retrieve their names on the page when it is shows to users? I guess I need the select query to know that the comma acts as a marker of sorts?

For a real life example take a look at this page: Play.com (UK) : Spider-Man 1, 2 And 3 (Blu-ray) : Blu-ray - Free Delivery towards the bottom under technical details it lists the actors.

I know it's probably a really simple silly nube question! (Sorry!) Appreciate your help!

Orc · 01-25-2008, 06:59 AM

To display the values from the MySQL rows from the columns you would do:

PHP Code:


			
 while ($row = mysql_fetch_array($query) )
 {
    echo $row['actorfirstname'];
    echo $row['actorlastname'];
 }

Also the id column would be set to null considering it increments whenever there are new values put inside the other columns, so when you put a new actor's first and last name in, and submit the form inputs
It will put them into the database along with an id knowing already that it increments +1.

You could get the $_GET global for use of the ID column doing:

PHP Code:


			
 $id = $row['actor_id'];
 $_GET['actid'] = $id;

Brook · 01-25-2008, 07:22 AM

Hi Orc - thanks for the reply.

I probably didn't explain myself properly, but I was specifically referring the page where I want to list the actors associated with that movie. So for eg, on the link I posted above, (once I already have the database of actors set-up), how do I add them to movies they are in? And then how do I get mysql to know how to 'decode' the actor_ids?

EG:

Actors Column/field on Movie Page: 124,366,697,125,123

These are added to the movie page when the page is being created. So the person writing the page will simply enter the actor_ids of all the actors in that movie.

I need to know how to transform all those ids that are seperated by a comma into individually ids that I can feed into mysql via a query.

(Am I making any more sense?)

Orc · 01-25-2008, 07:23 AM

Quote:

Originally Posted by Brook

Hi Orc - thanks for the reply.

I probably didn't explain myself properly, but I was specifically referring the page where I want to list the actors associated with that movie. So for eg, on the link I posted above, (once I already have the database of actors set-up), how do I add them to movies they are in? And then how do I get mysql to know how to 'decode' the actor_ids?

EG:

Actors Column/field on Movie Page: 124,366,697,125,123

These are added to the movie page when the page is being created. So the person writing the page will simply enter the actor_ids of all the actors in that movie.

I need to know how to transform all those ids that are seperated by a comma into individually ids that I can feed into mysql via a query.

(Am I making any more sense?)

Hmm, I just started learning more MySQL, so I don't really know how to tell you this :[ Sorry..

Tanax · 01-25-2008, 10:48 AM

You will want to use the explode function to seperate the commas. I can't explain exactly how it works, because I don't know.

But the SQL later would look like:

PHP Code:


			
$aActors = array();
foreach($aIds as $iId) {
$sql = "SELECT * FROM `actors` WHERE `id` = '".$iId."' LIMIT 1";
$query = mysql_query($sql);

if($query) {
$aActors[] = mysql_fetch_array($query);
}
else {
echo 'Could not fetch data for actor id: ' .$iId;
}
}

foreach($aActors as $aActor => $aInfo) {
echo $aInfo['First Name'];
}

That would fetch all the data of the actors in the array of numbers.
That's where explode comes in, which I'm sure that someone else can explain.

Then it places all the data in a multi-dimensional array.

Code:

$array = array(

'0' => array('id' => 3, 'first name' => 'Jessica', 'last name' => 'Alba'),
'1' => array('id' => 4, 'first name' => 'Arnold', 'last name' => 'Schwarzenegger')

);

And then we foreach that array, and echo out the names :)

sketchMedia · 01-25-2008, 11:03 AM

PHP Code:


			
$seperated = explode(',', '124,366,697,125,123');

gives you an array of

Code:

 Array
(
    [0] => 124
    [1] => 366
    [2] => 697
    [3] => 125
    [4] => 123
)

explode, explodes an array at a given identifier, in this case ',' and essentially splits it up into an array

Tanax · 01-25-2008, 11:12 AM

Yah, so in your case, you would want to do:

PHP Code:


			
$aSep = explode(',', $aQuery['actors']);

$aActors = array();
foreach($aSep as $iId) {
$sql = "SELECT * FROM `actors` WHERE `id` = '".$iId."' LIMIT 1";
$query = mysql_query($sql);

if($query) {
$aActors[] = mysql_fetch_array($query);
}
else {
echo 'Could not fetch data for actor id: ' .$iId;
}
}

foreach($aActors as $aActor => $aInfo) {
echo $aInfo['First Name'];
}

Where $aQuery is an array of the movie info..

EyeDentify · 01-25-2008, 01:03 PM

Why not use a relations table to hook up actors with a movie.

Lets say you have your actor table with:

ID | firstname | lastname | info

And your movie table:

ID | title | info

And your relations table where the actors get related to a movie.
ID | movie_ID | actor_ID

so for every actor you would want to link to a movie you just make a row entry in the relation table containing the "movie_ID" of the movie and the "actor_ID" of the actor who should be related to that movie.

And ofcourse you would have to construct a mechanism that would take the movie ID when the movie details page is called and use that movie_ID to query the relation table for all the rows containg a matching movie_ID.
Then take all the actor_ID:s that would be the outcome from that query and then list the actors getting the individual actors Info from the actor_ID itself while listing them.

Did that sound complicated ?, well at first i would say it sounds scary. but i think this approach would bring more flexibility to the handling of information.

Thats my opinion anyway.

Good Luck to ya.

/EyeDentify

ETbyrne · 01-25-2008, 01:58 PM

EyeDentify's idea makes the most sense. You should have different tables for the main movie information and actor information.

Village Idiot · 01-25-2008, 04:03 PM

It is never a good idea to store data like that. In this instance, you will want to make a relational table, Evy was spot on with how to do it.

Brook · 01-25-2008, 08:12 PM

Thanks all!

Yes the relations table makes sense (I don't think I have seen it mentioned before).

I guess I can still have an input field where the person creating the page puts the actor ids seperated by comma and then use the $seperated = explode(',', '124,366,697,125,123'); to create an array which is then used to enter the actor ids into the relations table (?).

Is there any time when I should skip the relations table and just use the 'explode' method?

What if I simply want to add 'related actors' to a news item or a feature - would the relations table be overkill?

Village Idiot · 01-25-2008, 08:22 PM

Quote:

Originally Posted by Brook

Thanks all!

Yes the relations table makes sense (I don't think I have seen it mentioned before).

I guess I can still have an input field where the person creating the page puts the actor ids seperated by comma and then use the $seperated = explode(',', '124,366,697,125,123'); to create an array which is then used to enter the actor ids into the relations table (?).

Is there any time when I should skip the relations table and just use the 'explode' method?

What if I simply want to add 'related actors' to a news item or a feature - would the relations table be overkill?

There is never a good time to store data like that. Its simply bad database design.

Brook · 01-25-2008, 10:15 PM

Thanks VI.

Although it does make things harder for me - as I was simply going to use an articles hack and put the actor_ids in a field for each article.

I am going to have to think of a way to add my own relational table in there now.

Is it really bad if i go with the original way? It would be much easier for me... *runs!

Village Idiot · 01-25-2008, 11:40 PM

It will take some getting used to, but once you get used to that setup you will love it.

Brook · 01-27-2008, 06:56 PM

Would it be really bad if I did it my original way?

I'm not sure I can do it the other way yet tbh. (I'll be using an articles hack where I was going to simply add the actor_ids into a field. Most of the time it will only be one id being entered (only used movies/actors as an example)).