SELECT from DB

[faticus]

Hi,

I've inherited a database, which contains a table of film titles containing the following snippet of fields: filmid,filmtitle, actors, directors etc.

The actors & directors names are stored in full(firstname + surname) in their corresponding fields.

If the film includes several actors/directors the names are seperated by commas

ie filmid=1

filmtitle=fight club

actors= Brad Pitt,Edward Norton,Meat Loaf

director =David Fincher.

I need to extract the actors/directors by surname depending on a users choice of first letter.

ie SELECT actor FROM films WHERE actors surname LIKE p%

should return Pitt.

I just need to know how to isolate the surname from the actors field.

Thanks in advance

Faticus

[SuperBlue]

Have a look at the explode function, http://php.net/manual/en/function.explode.php

[Mchl]

As long as you have name and surname in one field, you'll have nothing but problems with this design.

 

Consider these names:

 

Sanghavi

Iron Eyes Cody

Charles Fernley Fawcett

David "Shark" Fralick

Eddie Foy Jr.

John P. Farley

 

There's no way you could find out, which word in each of these cases is a surname

 

[radalin]

If you want to get Brad Pitt from the sql qeury, explode won't help you as it's done on your php application. You should do that parsing in your query which is really hard.

 

You should think to create additional tables for actor/movie and director/movie relation in the database as this is a many-to-many relation (one actor can play more than movies, and more than one actors can play in one movie).

 

You could want to implement such a way, you have to create three tables: movies, cast and actors.

 

In actors there is: id, fname, lname

In movies there is: id, name, year, plot

In cast there is: id, movie_id, actor_id

 

movie_id is a reference to movies table id field and actor_id is a reference to actors table id field. And you data becomes something like this.

 

Actors:

id  fname    lname

1    Brad      Pitt 

2    George  Clooney

 

Movies:

id  name                    year        plot

1    Ocean's Thirteen    2006      they are doing again.

2    Fight Club            1998        fight!

 

Cast:

id  movie_id    actor_id

1    1              1

2    1              2

3    2              1

 

 

When making the query you can use something like this:

 

SELECT a.lname

FROM actors a, movies m, cast c

WHERE c.actor_id = a.id

    AND m.id = c.movie_id

    AND m.id = $_GET['movie']

 

It will return you the last name list of the actors playing in that movie

[faticus]

HI,

Many thanks for your prompt replies.

I think a DB re-design is on the cards!

 

[BioBob]

You might be able to finagle something with a wildcard function.

 

...WHERE actor LIKE '%rad Pitt%'  will match "Brad Pitts".

 

Use LIKE instead of = to use % as a wildcard.

[radalin]

But still he can't only get the last name of the actors, he just get movies with that actors. As actors are comma separated values, he has to make some string operation on the select statement to get that part. And it will increase complexity.