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);

Bu döndürür:

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

Ben denedim ilk şey, alt sorgu değişiyordu

(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

Ben doğru sorgu anlamak, alt sorgu önlemek ve bir birleşim kullanabilirsiniz. Ayrıca polls seçmek gerekmez.

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

Zaten oy tablo ile katılmadan eğer, neden, bir alt sorgu kullanabilir? Eğer zincir birden birlikte katıldı olabilir.

SQLite sorgu PHP çalışmıyor

2 Cevap

etiketler