Update query from PHP does not work

Question

I run this update query from PHP code:

$update_begin_insurance = "UPDATE `vehicles`
                           SET `begin_insurance_date` = '$begin_insurance'
                           WHERE `plate` = '$plate'";

$conn->query($update_begin_insurance);

$conn is a PDO object.

The problem is that any exception is thrown by $conn, but the vehicles table in my database is not updated. So, I've tried to run this query directly through phpmyadmin, and it works correctly, so I think it's a PHP problem, but I can't figure out where the problem is. My begin_insurance_date column is of type DATE, and $begin_insurance is a string in the correct format (YYYY-MM-DD, I've tried this code with 2017-06-10).

I'm using MySQL DBMS

This is the echo of $update_begin_insurance:

UPDATE `vehicles`
SET `begin_insurance_date` = '2017-06-10'
WHERE `plate` = 'ccccc'

UPDATE

This is the full PHP code of my page:

<?php
    require_once "connect_db.php";

    $plate = $_POST["plate"];
    $begin_insurance = $_POST["begin_insurance"];
    $end_insurance = $_POST["end_insurance"];
    $fuel_economy = $_POST["fuel_economy"];
    $fuel_type = $_POST["fuel_type"];
    $response = array();

    try
    {
        $conn->beginTransaction();

        if ($begin_insurance != "")
        {
            $update_begin_insurance = "UPDATE `vehicles`
                                       SET `begin_insurance_date` = '$begin_insurance'
                                       WHERE `plate` = '$plate'";

            $conn->query($update_begin_insurance);
        }

        if ($end_insurance != "")
        {
            $update_end_insurance = "UPDATE `vehicles`
                                     SET `end_insurance_date` = '$end_insurance'
                                     WHERE `plate` = '$plate'";
            $conn->query($update_end_insurance);
        }

        if ($fuel_economy != "")
        {
            $update_fuel_economy = "UPDATE `vehicles`
                                    SET `fuel_economy` = $fuel_economy
                                    WHERE `plate` = '$plate'";
            $conn->query($update_fuel_economy);
        }

        if ($fuel_type != "")
        {
            $update_fuel_type = "UPDATE `vehicles`
                                 SET `id_fuel` = $fuel_type
                                 WHERE `plate` = '$plate'";
            $conn->query($update_fuel_type);
        }

        $response["post"] = $_POST;
        $response["error_code"] = "0";
        $response["error_message"] = "none";
        $response["driver_error_code"] = "0";
    }
    catch (PDOException $e)
    {
        if ($conn->inTransaction())
        {
            $conn->rollBack();
        }

        $response["post"] = $_POST;
        $response["error_code"] = $e->getCode();
        $response["error_message"] = $e->getMessage();
        $response["driver_error_code"] = $e->errorInfo[1];
    }

    echo json_encode($response);
?>

As I said before, I don't get any exception (as you can see, I also print the $_POST array to check if the params are received correctly, and yes, they are). This is what echo json_encode($response) prints:

{ "post": { "plate":"ccccc", "begin_insurance":"2017-06-10" }, "error_code":"0", "error_message":"none", "driver_error_code":"0" }

I'm sure the connection works correctly because I've got others PHP files which execute some INSERT queries, and they works correctly.

What's the end query that your code produces? Have you tried it with prepared statements? — Peon, Jun 10 '17 at 16:37
@DainisAbols I've edited my question and I've put the print of my query. yes, I've tried prepared statements but it still doesn't work — , Jun 10 '17 at 16:47
Does the updated query (exactly the one you echo) work in phpmyadmin? What is the exception thrown? it should show you if it's a query error or a DB connection or something else. Are you connected to the same DB that you are using in your phpmyadmin? — Peon, Jun 10 '17 at 18:24
Are you certain. Positively, that your connection is setup to access the database you are expecting to update? and not just a test database? I don't know how many times I forgot to update the connection string, prior to running code, and looking for an error in the code itself, when It had been performing correctly, but on another test server. I can't tell if you are, or aren't getting an exception from the PDO object. If you are, can you tell us what it is? — Morten Bork, Jun 10 '17 at 19:48
@DainisAbols yes, it works correctly if I execute it with phpmyadmin, and yes, I'm sure I'm connected to the correct DB — , Jun 11 '17 at 09:38
@MortenBork I've edited my post including the full PHP code, I think it's more clear now. No, I don't get any exception, and I'm sure I'm connected to the correct DB — , Jun 11 '17 at 09:40
@Clyky Hi again :) Can I convince you to try to wrap to this around your conn object: if ($conn->query($statement) === TRUE) { echo "Record updated successfully"; } else { echo "Error updating record: " . $conn->error; } PHP may be covering up the error by determining that something is wrong, but listing it as "Acceptably wrong" catching it before an exception is thrown, thus preventing you from perceiving it as an error. — Morten Bork, Jun 11 '17 at 15:06
@MortenBork I've tried that `if/else`, but I think it's useless because I've set the `PDO::ATTR_ERRMODE` to `PDO::ERRMODE_EXCEPTION`. with your solution, `$conn->query` returns `false`. Since there's no `error` fied in the `PDO` class I've tried to print `$conn->errorCode()`, but it prints the `00000` SQLSTATE — , Jun 11 '17 at 20:25
@Clyky regardless of your set errormode, you now know, its because the statement isn't being executed via the PDO. The reason why you are getting the 00000 SQLSTATE can be found here: https://stackoverflow.com/questions/11519966/pdo-sql-state-00000-but-still-error — Morten Bork, Jun 11 '17 at 20:33
@ClykyYour query is not being executed. It could be a number of things, without a more detailed explination from the $conn objects its hard to help you further. I would try to hardcode a complete string that's an update, and process that, to see if it works. if it does, I am willing to bet 100$ that its the way you are including your variables in the statement string that's causing your issue, if it doesn't process the hardcoded string it's a configuration issue with your $conn object. — Morten Bork, Jun 11 '17 at 20:34
@MortenBork thank you for your help, but I've solved... i was missing the `$conn->commit()`.. — , Jun 11 '17 at 20:43

score 0 · Accepted Answer · answered Jun 11 '17 at 20:48

0

I've solved the problem, I was missing the $conn->commit().

answered Jun 11 '17 at 20:48

Update query from PHP does not work

1 Answers1