SQLite 子查询在 PHP 中不起作用

发布于 2024-08-07 14:32:32 字数 1346 浏览 5 评论 0原文

我正在使用 php 和 sqlite 开发一个简单的投票系统。我预先构建了一个完整的场景，并在 sqlite 命令行中对其进行了测试，它运行良好。但是一个应该返回民意调查的所有可能答案以及投票的用户的查询不再起作用...

Tables:

CREATE TABLE polls (
 question CHAR(200)
);

CREATE TABLE choices (
 poll_id INTEGER(5),
 choice_text CHAR(200) 
);

CREATE TABLE votes (
 poll_id INTEGER(5),
 user_id INTEGER(5),
 choice_id INTEGER(5),
 PRIMARY KEY (poll_id, user_id)
);

CREATE TABLE users (
 name CHAR(100),
 pass CHAR(100),
 session CHAR(100)
);

PHP:

$query = "SELECT choices.rowid,choices.choice_text,
          (SELECT users.name FROM users WHERE votes.user_id=users.rowid) AS name
       FROM choices,polls LEFT OUTER JOIN votes ON choices.rowid = votes.choice_id
       WHERE polls.rowid=choices.poll_id AND polls.rowid='$pollid'";

$result = $db->arrayQuery($query, SQLITE_ASSOC);
echo json_encode($result);

这将返回：

Warning: SQLiteDatabase::arrayQuery() [sqlitedatabase.arrayquery]: no such column: votes.user_id in C:\Users\jan\Eigene Programme\xampplite\htdocs\cattle\vote\update.php on line 122
false

我尝试的第一件事是将子查询更改为

(SELECT users.name FROM users,votes WHERE votes.user_id=users.rowid) AS name

This Works，但不会返回相同的结果：/ 我对这个查询进行了很长时间的修改，因为我不是专业人士，所以我想可能还有更好的查询？

原文

I'm working on a simple voting system, using php and sqlite. I prebuild a whole scenario and tested it in the sqlite commandline, where it worked nicely.
But one query, which should return all possible answers of a poll and the users that voted for it, doesn't work anymore...

Tables:

CREATE TABLE polls (
 question CHAR(200)
);

CREATE TABLE choices (
 poll_id INTEGER(5),
 choice_text CHAR(200) 
);

CREATE TABLE votes (
 poll_id INTEGER(5),
 user_id INTEGER(5),
 choice_id INTEGER(5),
 PRIMARY KEY (poll_id, user_id)
);

CREATE TABLE users (
 name CHAR(100),
 pass CHAR(100),
 session CHAR(100)
);

PHP:

$query = "SELECT choices.rowid,choices.choice_text,
          (SELECT users.name FROM users WHERE votes.user_id=users.rowid) AS name
       FROM choices,polls LEFT OUTER JOIN votes ON choices.rowid = votes.choice_id
       WHERE polls.rowid=choices.poll_id AND polls.rowid='$pollid'";

$result = $db->arrayQuery($query, SQLITE_ASSOC);
echo json_encode($result);

This returns:

Warning: SQLiteDatabase::arrayQuery() [sqlitedatabase.arrayquery]: no such column: votes.user_id in C:\Users\jan\Eigene Programme\xampplite\htdocs\cattle\vote\update.php on line 122
false

The first thing i tried was changing the subquery to

(SELECT users.name FROM users,votes WHERE votes.user_id=users.rowid) AS name

This works, but doesn't return the same result :/
I tinkered very long with that query, since i'm not a professional, so i guess there might even be better queries for this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

内心旳酸楚 2024-08-14 14:32:32

如果我正确理解您的查询，您可以避免子查询并使用联接。您也不需要从民意调查中进行选择。

SELECT
    choices.rowid,
    choices.choice_text,
    users.name
FROM
    choices
    LEFT JOIN votes ON choices.rowid = votes.choice_id
    LEFT JOIN users ON votes.user_id = users.rowid
WHERE choices.poll_id = ?

If I understand your query correctly, you can avoid the subquery and use a join. You also don't need to select from polls.

SELECT
    choices.rowid,
    choices.choice_text,
    users.name
FROM
    choices
    LEFT JOIN votes ON choices.rowid = votes.choice_id
    LEFT JOIN users ON votes.user_id = users.rowid
WHERE choices.poll_id = ?

回复收藏 0 原文