PHP MySQL INNER JOIN查询只返回一行，而同一查询的phpMyAdmin返回四行

Question

我有两个表"收藏夹"one_answers"图像"：

1. "收藏夹"-包含列：
   • 用户的用户名(varchar(唯一标识符
   • 图像的viewkey(varchar(唯一标识符
   • action(tinyint(1=图片已被收藏
   • 创建(日期时间(时间用户喜爱的图像
2. "image"-包含列：上传图像的用户的用户名(varchar(唯一标识符
3. 上传图像的viewkey(varchar(唯一标识符
4. 图像的标题
5. 标签(文本(csv格式的标签描述图像
6. filename(文本(服务器上图像文件的路径
7. 上传(日期时间(用户上传的时间

我想输出一个数组，其中包含当前登录用户喜爱的所有图像，以便他们可以查看喜爱的相册

我使用的是一个自定义数据库类，该类具有改编自CodexWorld的查询构建函数。我是联接表的新手，但我编写了getJoin((函数来在两个表之间构建一个INNER JOIN查询，该查询产生了以下MySQL查询：

SELECT image.* 
FROM image 
INNER JOIN favorite ON image.viewkey=favorite.viewkey 
WHERE favorite.username = 'Caddy7676' 
GROUP BY image.title, image.tags, image.filename, image.uploaded, image.modified, image.username, image.viewkey

关键是当我在phpMyAdmin中运行这个查询时，会返回4行正确的图像，但在我的php程序中，只返回1行正确的行。没有其他人。它还生成一个数组，其中所有列都是字符串。我做错了什么？

1. 函数getJoin((-构建并执行返回数组的查询

class DB {
private $hostname = DB_HOST;
private $username = DB_USER; 
private $password = DB_PASS; 
private $dbname = DB_NAME; 
public function __construct() {
if (!isset($this->db)) {
// Connect to database
$conn = new mysqli($this->hostname, $this->username, $this->password, $this->dbname);
if ($conn->connect_error) {
die ("Failed to connect with MySQL: " . $conn->connect_error);
} else {
$this->db = $conn;
}
}
}
public function getJoin($table1, $table2, $conditions = array()) {
if (array_key_exists("select", $conditions)) {
$sql = " SELECT "; 
$i = 0;
foreach ($conditions['select'] as $tbl => $value) {
$pre = ($i > 0)?' , ':'';
$sql .= $pre . $tbl . "." . $value;
$i++;
}
$sql .= ' FROM ' . $table1; 
}
if (array_key_exists("inner_join", $conditions)) {
$sql .= " INNER JOIN " . $table2 . " ON " . $table1 . "." . $conditions['inner_join'] . "=" . $table2 . "." . $conditions['inner_join'];

}
if (array_key_exists("where", $conditions)) {
$sql .= ' WHERE ';
$i = 0;
foreach($conditions['where'] as $key => $value) {
$pre = ($i > 0)?" AND ":'';
$sql .= $pre.$table2 . "." . $key ." = " . "'$value'" ;
$i++;
}
} 
if (array_key_exists("group_by", $conditions)) {
$sql .= ' GROUP BY ';
$i = 0;
foreach($conditions['group_by'] as $key => $value) {
$pre = ($i > 0)?", ":'';
$sql .= $pre . $table1 . "." . $key;
$i++;
}
}
if (array_key_exists("order_by", $conditions)) {
$sql .= ' ORDER BY ' . $conditions['order_by'] . ' DESC ';
} 
$result = $this->db->query($sql);
if (array_key_exists("return_type", $conditions) && $conditions['return_type'] != 'all') {
switch ($conditions['return_type']) {
case 'count':
$data = $result->num_rows;
break;
case 'single':
$data = $result->fetch_assoc();
break;
default:
$data = '';
}
} else {
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
$data[] = $row;
}
}
}
echo $sql;
return !empty($data)?$data:false;
}

2. view_favorites.php将所有参数传递给getJoin((并充实查询

$viewFavoritesDB = new DB();
$images = array ();
// Get all favorited images based on logged in username and favorite action
$conditions['select'] = array (
'image' => '*'
);
$conditions['inner_join'] = 'viewkey';
$conditions['where'] = array (
'username' => $_SESSION['username'],
);
$conditions['group_by'] = array (
'title' => '',
'tags' => '',
'filename' => '',
'uploaded' => '',
'modified' => '',
'username' => '',
'viewkey' => '',
);
$images = $viewFavoritesDB->getJoin('image', 'favorite', $conditions);

3. var_dump($images(

array (size=8)
'id' => string '53' (length=2)
'title' => string 'Green Triangle' (length=14)
'tags' => string 'green,triangle,2-d' (length=18)
'filename' => string '33238_1648833408.png' (length=20)
'uploaded' => string '2022-04-01 13:16:21.790642' (length=26)
'modified' => string '2022-04-07 01:25:11.000000' (length=26)
'username' => string 'AgBRAT' (length=6)
'viewkey' => string '62473379c82ec' (length=13)

Answer 1

已回答

我以前使用过变量$conditions，它有一个"single"行参数剩余。用$conditions = array ();重新初始化$conditions清除了所有内容。感谢您的评论。