CODEWITHSUNDEEP
phpmysql
John Doo
John Doo

Reputation: 119

How to Fetch data from one to many relationship mysql table

My code: 

    $stmt = $conn->prepare("SELECT tmdb_movies.movie_title, images.image_url

FROM tmdb_movies

JOIN images ON images.images_tmdb_id=tmdb_movies.tmdb_id

GROUP BY tmdb_movies.movie_title,images.image_url");

 // Then fire it up
 $stmt->execute();
 // Pick up the result as an array
 $result = $stmt->fetchAll();

// Now you run through this array in many ways, for example
 for($x=0, $n=count($result); $x < $n; $x++){

 echo'
  '.$result[$x]["movie_title"].' - <img src="'.$result[$x]["image_url"].'"/>
  ';

}

Example: How it Echo Data

// SOME Stuff Here
The Dark Knight: - <img src="sdfsdfds.jpg"/>
// MORE STUFF HERE
// SOME Stuff Here
The Dark Knight: - <img src="sdfsdfds.jpg"/>
// MORE STUFF HERE// SOME Stuff Here
The Dark Knight: - <img src="sdfsdfds.jpg"/>
// MORE STUFF HERE

How I Want It to echo data

// Some Stuff Here
The Dark Knight -  <img src="sdfsdfds.jpg"/> <img src="fdfgfdd.jpg"/> <img src="sdfs.jpg"/>
// More Stuff Here

I am using an One To Many Relationship SQL table, Two tables:

tmdb_movies and images

Upvotes: 1

Views: 703

Answers (1)

Sudipta Mondal
Sudipta Mondal

Reputation: 2572

Two options

  1. Condition in For Loop

    for($x=0, $n=count($result); $x < $n; $x++){
{
    If ($x == 0 )
       {then echo $result[$x]["movie_title"];}
    echo ' - <img src="'.$result[$x]["image_url"].'"/>';
}

  2. Group_concat

    SELECT tmdb_movies.movie_title, Group_concat(images.image_url, ', ')
FROM tmdb_movies
JOIN images ON images.images_tmdb_id=tmdb_movies.tmdb_id
GROUP BY tmdb_movies.movie_title,images.image_url;

The above will give you data in a single record, with comma separated values, which you can split and then create the tags

I would go for the first option though

Upvotes: 3

Related Questions