PHP PDO MySQL No Exception or Error

Question

I've got the following script that uses PDO.

    $db = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);

    $sql    = "BEGIN;\n".
              "INSERT INTO exp_ws_gk_text (WGT_PRG_CODE, WGT_TEXT)\n".
              "    VALUES('".$_POST['prg_code']."', ".$db->quote($_POST['text']).");\n" .
              "SELECT @Lid:=LAST_INSERT_ID();\n" .
              "INSERT INTO exp_ws_gk_state (WGS_STATE, WGS_TYPE, WGS_WGT_RID)\n".
              "    VALUES('".$_POST['state']."', ".$_POST['type'].", @Lid);\n".
              "SELECT a.wgt_prg_code prg_code, b.wgs_state state, b.wgs_type type, a.wgt_rid id, a.wgt_text text\n".
              "    FROM exp_ws_gk_text a\n".
              "    JOIN exp_ws_gk_state b ON b.wgs_wgt_rid = a.wgt_rid\n".
              "    WHERE b.wgs_wgt_rid = @Lid ORDER BY a.wgt_prg_code;\n".
              "COMMIT;\n";

$fp = fopen("/var/tmp/save_gatekeeper.txt", "a");
fwrite($fp, "SQL:". $sql."\n");

    try {
        $res = $db->prepare($sql);
        $res->execute();
        $error = $db->errorInfo();

fwrite($fp, "ErrorInfo:".print_r($error, true)."\n\n");
fwrite($fp, "Row:      ".print_r($res->fetch(PDO::FETCH_ASSOC), true)."\n\n");
fwrite($fp, "Row2:     ".print_r($res->fetch(PDO::FETCH_LAZY),true)."\n\n");

    }
    catch (PDOException $e) {

fwrite($fp, "ExceptionInfo:".print_r($e, true)."\n\n");

    }    

fclose($fp);

    echo json_encode($res->fetch(PDO::FETCH_ASSOC););

This is what the log produced:

SQL:BEGIN;
INSERT INTO exp_ws_gk_text (WGT_PRG_CODE, WGT_TEXT)
    VALUES('EDS', '[h3]IMPORTANT INFORMATION[/h3][p]This is a test gatekeeper for [b]Eddie\'s[/b] Classroom Course[/p]');
SELECT @Lid:=LAST_INSERT_ID();
INSERT INTO exp_ws_gk_state (WGS_STATE, WGS_TYPE, WGS_WGT_RID)
    VALUES('AL', 1, @Lid);
SELECT a.wgt_prg_code prg_code, b.wgs_state state, b.wgs_type type, a.wgt_rid id, a.wgt_text text
    FROM exp_ws_gk_text a
    JOIN exp_ws_gk_state b ON b.wgs_wgt_rid = a.wgt_rid
    WHERE b.wgs_wgt_rid = @Lid ORDER BY a.wgt_prg_code;
COMMIT;

ErrorInfo:Array
(
    [0] => 00000
)


Row:      

Row2:

I'm not sure why it isn't reporting any kind of error or exception. This is the first time I'm using PDO. I converted from using the older mysql calls.

Can someone help out here?

I'm expecting the results from the last select to be sent back, all I get is "false";

If you're pulling in multiple database rows, you want to use `fetchAll` instead of `fetch`. Use `fetch` if you want to return a single, specific row (via a unique or primary key). — David Wyly, Apr 29 '15 at 20:08
Using `fetchAll()` only returned an empty array. I **know** that the record was inserted. — MB34, Apr 29 '15 at 20:09
Oh, sorry. You're doing an INSERT, not a SELECT. I should have been paying more attention. Try `query`. — David Wyly, Apr 29 '15 at 20:10
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/76578/discussion-between-mb34-and-david-wyly). — MB34, Apr 29 '15 at 20:13
FYI `$db->errorInfo();` retrieves error information on the handle and not on the prepared statement. `$res->errorInfo();` returns error information on your prepared statement. Not your issue but thought you should know. — jnthnjns, Apr 29 '15 at 20:25

score 3 · Answer 1 · edited May 23 '17 at 10:26

Since you're using a prepared statement with multiple queries, you need to use emulated prepared statements. Refer to PDO support for multiple queries (PDO_MYSQL, PDO_MYSQLND)

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 1);

Otherwise, I believe the only thing you are executing is BEGIN;

In my personal opinion, I think this is bad practice to put multiple queries into one PDO statement. You should consider using a transaction with PDO and multiple statements which allows you to more easily figure which query is causing issues and rollback if an error occurs.

Secondly, you are using prepared statements but are still not protecting yourself from SQL injections. Don't use $_POST in the prepare(). You are supposed to bind those after you prepare the statement.

score 0 · Accepted Answer · answered Apr 29 '15 at 23:00

Cheers, we solved this in chat.

You're mixing multiple types of statements. You need to separate out your queries into two separate queries; one with the INSERT with $res->query, and then validate the response, and then another with the SELECT using $res->fetch() or $res->fetchAll()

PHP PDO MySQL No Exception or Error

2 Answers2