SQLite subquery not working in PHP

Question

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?

Answer 1

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 = ?

Answer 2

why use a subquery, if you’re already joining with the votes table? you can chain multiple joins together.