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
– David Wyly, Commented Apr 29, 2015 at 20:08
Using fetchAll() only returned an empty array. I know that the record was inserted. — MB34
– MB34, Commented Apr 29, 2015 at 20:09
Oh, sorry. You're doing an INSERT, not a SELECT. I should have been paying more attention. Try query. — David Wyly
– David Wyly, Commented Apr 29, 2015 at 20:10

Community · Accepted Answer · 2017-05-23 10:26:55Z

3

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.

edited May 23, 2017 at 10:26

CommunityBot

11 silver badge

answered Apr 29, 2015 at 20:19

Devon Bessemer

35.6k10 gold badges77 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

David Wyly · Accepted Answer · 2015-04-29 23:00:20Z

0

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

answered Apr 29, 2015 at 23:00

David Wyly

1,7011 gold badge11 silver badges20 bronze badges

Collectives™ on Stack Overflow

PHP PDO MySQL No Exception or Error

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related