SQLSTATE[HY000]: General error on PDOStatement->fetchAll(2)

Question

Is there any wrong with my Code? I tried to display the result but return zero and instead title above occur or is there any way around on how to get this smoothly. Please let me know. Thanks

    $pdo = Database::connect();
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION, 1);
    $sql = ("               SET @curRank1 = 0;
                            SET @curRank2 = 0;
                            SELECT
                                D1.reading_date
                                ,D1.consumer_id
                                ,D1.readings AS previous
                                ,D2.readings AS current
                                ,(D2.readings - D1.readings) AS kwh_used

                            FROM 
                            (
                                SELECT M1.*, @curRank1 := @curRank1 + 1 AS rank
                                FROM consumption M1
                                LEFT JOIN consumption M2
                                ON Date(M1.reading_date) = Date(M2.reading_date)
                                AND M1.reading_date > M2.reading_date
                                WHERE M2.reading_date IS NULL
                              ) D2
                            LEFT JOIN
                            (
                                SELECT M1.*, @curRank2 := @curRank2 + 1 AS rank
                                FROM consumption M1
                                LEFT JOIN consumption M2
                                ON Date(M1.reading_date) = Date(M2.reading_date)
                                AND M1.reading_date > M2.reading_date
                                WHERE M2.reading_date IS NULL
                            ) D1
                            ON D2.rank = (D1.rank + 1)  
                            WHERE D1.reading_date IS NOT NULL
                            AND D1.consumer_id = :consumer_id");
    $q = $pdo->prepare($sql);
    $q->execute(array($id));
    $q->nextRowset();
    $data = $q->fetchAll(PDO::FETCH_ASSOC);

EDIT: SQLSTATE[HY000]: General error onPDOStatement->fetchAll(2)

score 1 · Answer 1 · answered Mar 13 '15 at 02:30

1

I believe you're using binding syntax, which is what mysqli uses. PDO just uses placeholders so :consumer_id should be ?.

answered Mar 13 '15 at 02:30

chris85

23,846
7
34
51

it was set as if ( !empty($_GET['consumer_id'])) { $id = $_REQUEST['consumer_id']; } – Mark Gil Bungaos Dotillos Mar 13 '15 at 02:35
Yes that's not the issue. You're not telling the SQL how to get it. Rizier or my answer should work for you. http://php.net/manual/en/pdostatement.execute.php – chris85 Mar 13 '15 at 02:36
Already tried also to echo that consumer_id if it is null and it returns not null – Mark Gil Bungaos Dotillos Mar 13 '15 at 02:37
If you do `print_r(array($id));` do you get the value you expect `$id` to be? – chris85 Mar 13 '15 at 02:40

score 1 · Answer 2 · answered Mar 13 '15 at 02:34

1

If you are using positional placeholders (e.g. ?) Then you can use a normal array like you do now, but if you use named placeholders (e.g. :consumer_id) you have to use an associative array like this:

$q->execute(array(":consumer_id" => $id));

Otherwise it won't know which placeholder has to be replaced with which value.

answered Mar 13 '15 at 02:34

Rizier123

58,877
16
101
156

I guess I missed that PDO supported the binding. – chris85 Mar 13 '15 at 02:42
@chris85 Well you know it now :D – Rizier123 Mar 13 '15 at 02:43
@Rizeier123 the error was gone but there is another error occured Warning: PDO::setAttribute() expects exactly 2 parameters, 3 given on line 12 ($sql ) – Mark Gil Bungaos Dotillos Mar 13 '15 at 02:46
@MarkGilBungaosDotillos Yes see the first example here: http://php.net/manual/en/pdo.error-handling.php (-> Remove your 3rd argument from setAtt..) – Rizier123 Mar 13 '15 at 02:47
@Rizeir123 already tried that too and this is my first error occurred before that , Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error – Mark Gil Bungaos Dotillos Mar 13 '15 at 02:52
@MarkGilBungaosDotillos *General error* Well I would say that's the most helpful error message I saw ;D Did you run your SQL on your MySql server? And if yes did it worked? – Rizier123 Mar 13 '15 at 02:54
Yes of course. The output what I am trying to display was meet. – Mark Gil Bungaos Dotillos Mar 13 '15 at 02:57
@MarkGilBungaosDotillos Maybe take a look here: http://stackoverflow.com/a/20316240/3933332 – Rizier123 Mar 13 '15 at 02:59
@MarkGilBungaosDotillos Remove this line and see if it works: `$q->nextRowset();` – Rizier123 Mar 13 '15 at 16:58

score 0 · Answer 3 · answered Mar 13 '15 at 02:59

Please let me know how to debug this

Use $q->debugDumpParams();

Then run the query directly (Mysql termina / Mysql Workbench / phpmyAdmin or however you want) and see if you get the same result as your code

NOTE: I am just answer how I usually test my code with some issue with PDO. This is not a solution to your issue specifically.

SQLSTATE[HY000]: General error on PDOStatement->fetchAll(2)

3 Answers3