如何使用PHP在MySQL中过滤数据(一对多关系)

问题描述

用户将使用GET发送一个值，然后将值存储在名为$category1的变量中.现在将显示所有包含$category1值的行.这就像一个搜索系统.

The user will send a value using GET and I will store the value in a variable named $category1. Now all the rows which contain the value of $category1 will display. This is like a search system.

$stmt = $conn->prepare("SELECT tmdb_movies.movie_title
,GROUP_CONCAT(DISTINCT genres.genres_name SEPARATOR ', ') AS genres

FROM tmdb_movies

JOIN genres ON genres.genres_tmdb_id=tmdb_movies.tmdb_id

WHERE EXISTS (SELECT genres.genres_name FROM genres WHERE genres.genres_name = '$category1');

GROUP BY tmdb_movies.movie_title");

错误:由于WHERE Exist行(IDK，是我应该使用还是不使用)

Error: Because of WHERE Exist line (IDK, if I should use it or no)

现在让我解释一下我的表结构

Let me explain my Table Structure now

我有两个桌子

1. tmdb_movies(存储电影标题和tmdb_id)
2. genres(存储类型，通过tmdb_id连接)

1. tmdb_movies (it store movie title and tmdb_id)
2. genres (It stores genres, which is connected via tmdb_id)

表格示例.

tmdb_id     movie_title
1           The_Dark_Night
2           Logan
3           IronMan

tmdb_id       genres
 1            Action
 1            Crime
 1            Drama
 2            Action
 2            Drama
 3            Action
 3            Comedy

推荐答案

在5.7中，sqlmode默认设置为:

In 5.7 the sqlmode is set by default to:

ONLY_FULL_GROUP_BY,
 NO_AUTO_CREATE_USER,
 STRICT_TRANS_TABLES,
 NO_ENGINE_SUBSTITUTION

要删除条款 ONLY_FULL_GROUP_BY ，您可以执行以下操作:

To remove the clause ONLY_FULL_GROUP_BY you can do this:

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

这表明您需要使用未聚合的列来创建GROUP BY.

This supposed you need to make that GROUP BY with non aggregated columns.

否则我在本地测试了您的代码，它工作得很好.

otherwise i tested your code locally it working perfectly fine.

这篇关于如何使用PHP在MySQL中过滤数据(一对多关系)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！