在PHP中使用group_concat回显mysql数据 [英] Echo mysql data using group_concat in PHP

问题描述

这是我的SQL提琴: SQL提琴

This is my SQL Fiddle: SQL Fiddle

我正在尝试使用PHP回显此结果.这是我的预期结果:

I am trying to echo this result using PHP. This is my expected result:

// Some Stuff Here

The Dark Knight Rises -  7.5
Batman Begins - 7.5 
Iron Man - 7.3
The Lord of the Rings: The Return of the King - 8.1 
etc...

// Some more Stuff here

我的PHP代码:

$stmt = $conn->prepare("SELECT tmdb_movies.movie_title
,GROUP_CONCAT(recommendations.recommendations_title  ORDER BY recommendations.recommendations_title) as recommendations_title
,GROUP_CONCAT(recommendations.recommendations_vote_average ORDER BY recommendations.recommendations_title) as recommendations_vote_average

FROM tmdb_movies 

LEFT JOIN cast ON cast.cast_tmdb_id=tmdb_movies.tmdb_id
LEFT JOIN recommendations ON recommendations.recommendations_tmdb_id=tmdb_movies.tmdb_id


Where tmdb_movies.tmdb_id= 155

GROUP BY tmdb_movies.movie_title
 ");


     // 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++){
$recommendations_name_list = explode(',',$result[$x]["recommendations_title"]);
$recommendations_vote_average = explode(',',$result[$x]["recommendations_vote_average"]);
foreach( $recommendations_name_list as $index => $recommendations_title ) {
           echo'
    <p>'.$recommendations_title.'- '.$recommendations_vote_average[$index].'</p>';
    }
}

---

此代码的输出:

---

Output of this code:

Batman Begins- 7.5

Batman Begins- 7.5

Batman Begins- 7.5

Batman Begins- 7.5

Batman Begins- 7.5

Batman Begins- 7.5

Batman Begins- 7.5

Batman Begins- 7.5

Batman Begins- 7.5

Batman Begins- 7.5

Captain America: The First Avenger- 6.6

Captain America: The First Avenger- 6.6

Captain America: The First Avenger- 6.6

Captain America: The First Avenger- 6.6

Captain America: The First Avenger- 6.6

Captain America: The First Avenger- 6.6

Captain America: The First Avenger- 6.6

Captain America: The First Avenger- 6.6

Captain America: The First Avenger- 6.6

Captain America: The First Avenger- 6.6

Inception- 8

Inception- 8

Inception- 8

Inception- 8

Inception- 8

Inception- 8

Inception- 8

Inception- 8

Inception- 8

Inception- 8

Iron Man- 7.3

Iron Man- 7.3

Iron Man- 7.3

Iron Man- 7.3

Iron Man- 7.3

Iron Man- 7.3

Iron Man- 7.3

Iron Man- 7.3

Iron Man- 7.3

Iron Man- 7.3

Iron Man 2- 6.6

Iron Man 2- 6.6

Iron Man 2- 6.6

Iron Man 2- 6.6

Iron Man 2- 6.6

Iron Man 2- 6.6

Iron Man 2- 6.6

Iron Man 2- 6.6

Iron Man 2- 6.6

Iron Man 2- 6.6

The Dark Knight Rises- 7.5

The Dark Knight Rises- 7.5

The Dark Knight Rises- 7.5

The Dark Knight Rises- 7.5

The Dark Knight Rises- 7.5

The Dark Knight Rises- 7.5

The Dark Knight Rises- 7.5

The Dark Knight Rises- 7.5

The Dark Knight Rises- 7.5

The Dark Knight Rises- 7.5

The Lord of th- 8

但是，如果我删除此行LEFT JOIN cast ON cast.cast_tmdb_id=tmdb_movies.tmdb_id 然后，我的php代码可以正常工作.

But If i remove this line LEFT JOIN cast ON cast.cast_tmdb_id=tmdb_movies.tmdb_id then, my php code works fine.

预期结果:

Batman Begins- 7.5

Captain America: The First Avenger- 6.6

Inception- 8

Iron Man- 7.3

Iron Man 2- 6.6

The Dark Knight Rises- 7.5

The Lord of the Rings: The Fellowship of the Ring- 8

The Lord of the Rings: The Return of the King- 8.1

The Lord of the Rings: The Two Towers- 7.9

The Matrix- 7.9

推荐答案

LEFT JOIN cast
的使用 对选择值没有用(由于基于maun表movie_title，结果选择为ayway) 也不会返回有用的信息，因为select子句中di表中没有列，并生成具有更多行的结果集

The use of the LEFT JOIN cast
is unuseful for the select values (the results are select ayway because are based on maun table movie_title adn don't return useful info because there are no columns from di table in select clause ) and produce a resulting set with more rows

因此，如果您想坚持左联接，可以将DISTINCT添加到原始查询中

so if you want insist on thi left join you can add DISTINCT to your originale query

 SELECT DISTICNT 
  tmdb_movies.movie_title
  ,GROUP_CONCAT(recommendations.recommendations_title  
        ORDER BY recommendations.recommendations_title) as recommendations_title
  ,GROUP_CONCAT(recommendations.recommendations_vote_average 
      ORDER BY recommendations.recommendations_title) as recommendations_vote_average

FROM tmdb_movies 

LEFT JOIN cast ON cast.cast_tmdb_id=tmdb_movies.tmdb_id
LEFT JOIN recommendations 
    ON recommendations.recommendations_tmdb_id=tmdb_movies.tmdb_id
Where tmdb_movies.tmdb_id= 155
GROUP BY tmdb_movies.movie_title

或者最好不要使用左连接pn投射

or better don use left join pn cast

   SELECT 
    tmdb_movies.movie_title
    ,GROUP_CONCAT(recommendations.recommendations_title  
          ORDER BY recommendations.recommendations_title) as recommendations_title
    ,GROUP_CONCAT(recommendations.recommendations_vote_average 
        ORDER BY recommendations.recommendations_title) as recommendations_vote_average

  FROM tmdb_movies 


  LEFT JOIN recommendations 
      ON recommendations.recommendations_tmdb_id=tmdb_movies.tmdb_id
  Where tmdb_movies.tmdb_id= 155
  GROUP BY tmdb_movies.movie_title

这篇关于在PHP中使用group_concat回显mysql数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！