CODEWITHSUNDEEP
phpmysqlsql
StudioTime
StudioTime

Reputation: 23959

Strip comma separated value

I am using PHP and MySQL database

I Have a table which has a column: actors

In a row in the table, in the actors column, there maybe several actors listed e.g. Denzel Washington, Charlie Sheen, Matt Damon

The next row might be: Matt Damon, Robert De Niro, Denzel Washington

They are always comma seperated.

I have an autocomplete where I am asking the user to choose an actor. So, when the autocomplete looks in the column, it returns the whole column value.

e.g. someone searching for Denzel will get in their autocomplete results:

Denzel Washington, Charlie Sheen, Matt Damon Matt Damon, Robert De Niro, Denzel Washington

As far as I know there's no way to seperate this at that level so I'm thinking I might have to strip out the actors from that table, into a seperate table which just services the autocomplete.

Is this the best way to do it and if so, can it be done? Is there a simple SQL query i could use to get the actors out, seperately, then load another table. This is constantly updated data so needs to run every day but I presume if I had the actor column in a new table as unique then i wouldn't have duplicates.

Or is there a more efficient way?

NOTE: I need to point out that this is a XML feed and I have no control over the data - yes, I know it should all be seperate from the get go but unfortunately I'm just working with what I have

Upvotes: 0

Views: 297

Answers (4)

web-nomad
web-nomad

Reputation: 6003

This is probably not a very good way to store data since it would become very slow and difficult to maintain once the csv reaches a certain size.

Anyway, as for your question, let me know if this helps:

<?php
$search = $_GET['search']; // assume it's 'de'
$result = array(); // this contains the result

$sql = 'SELECT `actors` from `tablename`;';
$run = mysql_query( $sql );

if ( $run && mysql_num_rows( $run ) ) {
   while ( ( $data = mysql_fetch_assoc( $run ) ) !== FALSE) {
      $num = explode( ',', $data[ 'actors' ] );

      for ($i=0; $i < $num; $i++) {
         if( stripos( $data[ $i ], $search ) !== false ) {
            $result[] = trim( $data[ $i ] );
         }
      }
   }        
}

// here's the result you want
$result = array_values( array_unique( $result ) );
?>

This will work, but again, as many have suggested, this is not a very good choice.

Upvotes: 0

scibuff
scibuff

Reputation: 13755

What you need is something like this

table actors

    id | name 

table actor_entries

    id | data_id | actor_id

table data

    id | <other columns>

Then your data would like something like this

table actors
1 | Denzel Washington
2 | Charlie Sheen
3 | Robert De Niro

table data
1 | some data // this row is associated with actors 1 and 2
2 | some data // this row is associated with actors 2 and 3

table actor_entries ( id | data_id | actor_id )

1 | 1 | 1
2 | 1 | 2
3 | 2 | 2
4 | 2 | 3

Upvotes: 2

tereško
tereško

Reputation: 58444

What you should have had is three different tables:

Movies         Actors        MovieActors
----           ----          ----------
movie_id PK    actor_id PK   movie_id FK
title          name          actor_id FK
year           born
               gender

Then you would not have that huge mess. Also you would be able to search data

SELECT Actors.name FROM Actors WHERE Actors.name LIKE '%Pam' LIMIT 5

Or get all movies where actor has participated:

SELECT 
    Movies.title
FROM Movies
    LEFT JOIN MovieActors USING(movie_id)
    LEFT JOIN Actors USING(actor_id)
WHERE 
    Actors.name = 'Foo Bar'
    AND Movies.year > 1990

If you already know the basics of SQL, i would suggest for you to read SQL Antipatterns. That book covers this an many other issues.

Upvotes: 1

Userbn
Userbn

Reputation: 342

You can use the php explode function with ',' as separator. It will return an array with all actor name

Upvotes: 0

Related Questions