php - 发出硬sql请求以查找图片的喜欢计数

我的数据库中有3个表：
用户，图片，类似于用：

CREATE TABLE IF NOT EXISTS `user` (
    `id` int(10) NOT NULL auto_increment,
    `email` varchar(255),
    `login` varchar(255),
    `password` varchar(255),
    `admin` boolean,
    `pwreset` boolean,
    `validate_link` varchar(255),
    `created_at` date, PRIMARY KEY( `id` )
);
CREATE TABLE IF NOT EXISTS `pic` (
    `id` int(10) NOT NULL auto_increment,
    `name` varchar(255),
    `owner_id` int(11) NOT NULL,
    `created_at` date,
    PRIMARY KEY( `id` )
);
CREATE TABLE IF NOT EXISTS `like` (
    `id` int(10) NOT NULL auto_increment,
    `owner_id` int(11) NOT NULL,
    `pic_id` int(11) NOT NULL,
    `created_at` date,
    PRIMARY KEY( `id` )
);";

我试图取回我的照片并通过照片的所有者id登录
我做了这个：

$find_pic = $db->prepare("SELECT p.*, u.login FROM camagru_jgengo.pic as p INNER JOIN camagru_jgengo.user as u ON p.owner_id = u.id  ORDER BY p.id DESC ");
$find_pic->execute();
$pics = $find_pic->fetchAll();

输出为：

[0] => Array
(
    [id] => 189
    [0] => 189
    [name] => 1492882407.png
    [1] => 1492882407.png
    [owner_id] => 1
    [2] => 1
    [created_at] => 2017-04-22
    [3] => 2017-04-22
    [login] => jgengo
    [4] => jgengo
)

...

现在，我想在这个数组中有一个新的信息，比如：
$pics[x][likes_count]这是用这个like.pic_id找到的
我试过了：

$find_pic = $db->prepare("SELECT p.*, u.login, l.like FROM camagru_jgengo.pic as p INNER JOIN camagru_jgengo.user as u ON p.owner_id = u.id INNER JOIN camagru_jgengo.like as  l.pic_id = p.id ORDER BY p.id DESC ");
$find_pic->execute();
$pics = $find_pic->fetchAll();

但它不起作用，有人能帮我吗？

最佳答案

此查询错误

SELECT
    p.*, u.login, l.like
FROM
    camagru_jgengo.pic as p
INNER JOIN
    camagru_jgengo.user as u ON p.owner_id = u.id
INNER JOIN
    camagru_jgengo.like as  l.pic_id = p.id
ORDER BY
    p.id DESC

l.pic_id您尚未定义什么是l
所以应该修正为

SELECT
    p.*, u.login, l.like
FROM
    camagru_jgengo.pic as p
INNER JOIN
    camagru_jgengo.user as u ON p.owner_id = u.id
INNER JOIN
    camagru_jgengo.like as l ON l.pic_id = p.id
ORDER BY
    p.id DESC

关于php - 发出硬sql请求以查找图片的喜欢计数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/43562928/