CODEWITHSUNDEEP
phpmysqlsorting
acerluc
acerluc

Reputation: 1

mysql results sort by array

I think I don't understand how 'sort' works, so please don't judge me. I really searched all day long.

I have a movies table with actors column. A column it's named "actors". The actors are links separated by space " ". The order of the links it's very important.

I explode the links into an array which looks like [0]-> link0, [1]->link1, ...

I have the actors table where every actor also has it's movies links. I don't want to make 20 different sql searches so I made a variable with all the links I want, like this ( WHERE actor_link = link1 OR actor_link = link2 OR .. )

The problem is this. The search will probably find first the link7, and so my sorting it's gone. What can I do to keep that order from the movies table. I want to display the actors by popularity in the movie, not the order of my database.

Can you give me another method to search the actors without making 'x' sql searches and still keeping the order? 

$actors[] = explode(" ", $row['actors_link']); 
$x=0;
$actors2 = '';
while ($actors[0][$x]) {
  $actors2 = $actors2 . "`link_imdb_actor` = " . "'".$actors[0][$x]."' OR ";
  $x++;
}
$actors2 = substr($actors2, 0, -3);

$sql = "SELECT * FROM `actors` WHERE $actors2";
$sql_result = mysql_query($sql) or die(" ");
while ($row3 = mysql_fetch_array($sql_result)) {
  echo $row3['link_imdb_actor'];
}

So, the movie Hotel Transylvania has Adam Sandler, Andy Samberg and Selena Gomez. My search shows Selena Gomez, Andy Samberg, Adam Sandler because this is the order from my database. How can I sort the sql results by the order of the actors array? Thank you!

Upvotes: 0

Views: 120

Answers (2)

lcHatter
lcHatter

Reputation: 120

To expand on Arjan's comment, if you want to be able to actually use the actor data (e.g. search with it) I would recommend at least two more tables. One called actors with the fields actorID, firstName, and lastName. The other table would be castings with the fields castingID, actorID, movieID, and castingOrder.

Each castingID will then link an actor to a movie - this would make for easy searches of every movie a particular actor has been in or every actor in a particular movie.

The castingOrder field can be used to maintain the order you want.

Upvotes: 1

Jason
Jason

Reputation: 1220

I need your existing code to really get the gist of what's going on.

I will make one suggestion in your query. Instead of saying WHERE actor_link = a OR actor_link = b OR actor_link = c do this instead:

WHERE actor_link IN (link1, link2, link3)

Upvotes: 0

Related Questions