[El Forum]

[eluser]vadimyer[/eluser]
Hello there.

I have a search_model with main function that creates the search query:

Code:

$query = $this->db->query("SELECT movies.title, movies.id FROM movies WHERE movies.title LIKE '%$match%'");
return $query;

For example, this query returns 2 results (2 movies), when in controller, I have no problems printing them out:

Code:

foreach($query->result() as $row) { echo $row->id; }

This returns those 2 movies in separated divs, as formatted in HTML.

But the question is: How can I fetch additional data related to each one movie? For example, I have a separate table called "actors" which contains actor.id, actor.name and actor.movie_id (which equals to movies.id), and 5 actors related to one movie, and say 7 actors related to the second movie.

How do I do that? Also, actors can have additional columns like URI, actor_image etc. as well.

Please, if you can, post in code. Cause I'm stucked with this problem 2 days and didn't find a solution yet. I like CI very much and don't wanna go back to WordPress, hehe.

THANKS IN ADVANCE!

[El Forum]

[eluser]jmadsen[/eluser]
Use a database join to the other table.

http://www.tizag.com/mysqlTutorial/mysqljoins.php

[El Forum]

[eluser]BradEstey[/eluser]
Try:

Code:

SELECT
    movies.title,
    movies.id,
    actors.id,
    actors.name
FROM movies
LEFT JOIN actors ON actors.movie_id = movies.id
WHERE movies.title LIKE '%$match%''

Or I like assigning shorthand to each table, like:

Code:

SELECT
    a.title,
    a.id,
    b.id,
    b.name
FROM movies a
LEFT JOIN actors b ON b.movie_id = a.id
WHERE a.title LIKE '%$match%'

[El Forum]

[eluser]vadimyer[/eluser]
Bradillac, jmadsen, I tried JOINS but they return separate results, each actor related to one movie is separated, what causes movies to duplicate, and in search results as well. I mean:

movie 1 = actor John Dow
movie 1 = actor Katy Lary
movie 1 = actor Barack Obama
movie 2 = ...
etc. etc.

The query array (if print_r it) looks like:

Code:

Array ( [0] => stdClass Object ( [title] => Inception [id] => 1 [actor] => Leo Di [movie_id] => 1 )
[1] => stdClass Object ( [title] => Inception [id] => 1 [actor] => Ken Watanabe [movie_id] => 1 ) )

And I don't know how to return only 1 movie with both 2 actors from it...

[El Forum]

[eluser]vadimyer[/eluser]
GROUP BY actors.movie_id
in the end doesn't work as well. It just adds only first actor row to each movie, but I need all the actors

[El Forum]

[eluser]Aziz Chouhan[/eluser]
hello dear
i understand about ur need and problem.
ur need is
suppose for movie1,the result should be

movie1 | Actor1,Actor2,Actor3,..|etc.
movie2 | Actor1,Actor4,Actor6,..|etc.

i have a simple solution for ur problem that is

1. make ur single table query and show the result in view page where in actor column do something like this.
2. in 1st movie row,actor column,call a ur own custom helper function named like
show_actors($movie_id).
3. that will also give u another result for showing actors

here now u can show ur actor lists for ur all movies. similar to this u can also show ur actresses lists for ur movie lists.
if u have any problem reply
enjoy the solution
ha ha ha ha ha ha

[El Forum]

[eluser]vadimyer[/eluser]
Aziz, haha, yeah it does work! Thank you very much dude.

What I finally did is:

1) Added function show_actors($id) to the search_model, this function makes the query that selects actors by movie_id that's passed from search results.
2) Inside search results foreach loop I've added another foreach loop for that actors function.

... and it works as charm! Thanks!

Are there any other interesting solutions?
Though the topic is solved.

[El Forum]

[eluser]Aziz Chouhan[/eluser]
hi dude
if you have any query for php and codeigniter related then you can message me at azaz.websolutions@gmail.com
thanks buddy for quick reply