Query with PHP using PDO are not inserted in MySQL

Question

I insert with PHP some data in my MySQL database, using PDO.

$connection->query(
    "INSERT INTO eOrderManagement.first_name (first_name)
    VALUES ('" . $firstName . "');

    INSERT INTO eOrderManagement.last_name (last_name)
    VALUES ('" . $lastName . "');

    INSERT INTO eOrderManagement.section (section)
    VALUES ('" . $section . "');

    INSERT INTO eOrderManagement.order (creation_date, wished_delivery_date, status, email, first_name_id, last_name_id, section_id)
    VALUES ('" . $creationDate . "', '" . $wishedDeliveryDate . "', 'En attente', '" . $email . "', (SELECT first_name_id FROM eOrderManagement.first_name WHERE first_name LIKE '" . $firstName . "'), (SELECT last_name_id FROM eOrderManagement.last_name WHERE last_name LIKE '" . $lastName . "'), (SELECT section_id FROM eOrderManagement.section WHERE section LIKE '" . $section . "'));

    INSERT INTO eOrderManagement.equipment (description, imputation, cost, maintenance_cost, order_id)
    VALUES ('" . $description . "', '" . $imputation . "', '" . $cost . "', '" . $maintenanceCost . "', (SELECT MAX(order_id) FROM eOrderManagement.order));"
    );

The three first requests are inserted in my database, but the two last are not. I tried to insert them manually in the MySQL Commande Line, directly with data on the query (unlike with variables in PHP), and all the query insert the data correctly.

Any idea why this two query doesn't insert anything ? The two query I am talking about are :

    INSERT INTO eOrderManagement.order (creation_date, wished_delivery_date, status, email, first_name_id, last_name_id, section_id)
    VALUES ('" . $creationDate . "', '" . $wishedDeliveryDate . "', 'En attente', '" . $email . "', (SELECT first_name_id FROM eOrderManagement.first_name WHERE first_name LIKE '" . $firstName . "'), (SELECT last_name_id FROM eOrderManagement.last_name WHERE last_name LIKE '" . $lastName . "'), (SELECT section_id FROM eOrderManagement.section WHERE section LIKE '" . $section . "'));

    INSERT INTO eOrderManagement.equipment (description, imputation, cost, maintenance_cost, order_id)
    VALUES ('" . $description . "', '" . $imputation . "', '" . $cost . "', '" . $maintenanceCost . "', (SELECT MAX(order_id) FROM eOrderManagement.order));"

zkanoca · Accepted Answer · 2014-04-03 06:56:52Z

order is a reserved word for SQL. So you have to quote your table name order

`eOrderManagement.order`

Try the following:

INSERT INTO `eOrderManagement.order` (creation_date, wished_delivery_date, status, email, first_name_id, last_name_id, section_id)
    VALUES ('" . $creationDate . "', '" . $wishedDeliveryDate . "', 'En attente', '" . $email . "', (SELECT first_name_id FROM eOrderManagement.first_name WHERE first_name LIKE '" . $firstName . "'), (SELECT last_name_id FROM eOrderManagement.last_name WHERE last_name LIKE '" . $lastName . "'), (SELECT section_id FROM eOrderManagement.section WHERE section LIKE '" . $section . "'));

    INSERT INTO eOrderManagement.equipment (description, imputation, cost, maintenance_cost, order_id)
    VALUES ('" . $description . "', '" . $imputation . "', '" . $cost . "', '" . $maintenanceCost . "', (SELECT MAX(order_id) FROM `eOrderManagement.order`));"

Moreover, your queries are vulnerable for SQL injections. So I suggest you to use prepared statements with parameters.

for example:

$query = $db->prepare('INSERT INTO eOrderManagement.equipment 
                          (description, 
                           imputation, 
                           cost, 
                           maintenance_cost, 
                           order_id) 
                   VALUES (:description, 
                           :imputation, 
                           :cost, 
                           :maintenanceCost, 
                           (SELECT MAX(order_id) FROM `eOrderManagement.order`)
                          )'
             );

$query->bindValue(':description', $description, PDO::PARAM_STR);
...

OR

$query->execute(':description' => $description, :imputation => $imputation, ...);

Another point what if there are two insert processes executed simultaneously?

 SELECT MAX(order_id) FROM `eOrderManagement.order`

would return incorrect value. Use lastInsertId() function of PDO.

Your Common Sense · Accepted Answer · 2014-04-03 06:53:01Z

1

In fact, your problem is FAR deeper than silly syntax issue.

First of all, such a database structure makes absolutely no sense.
Having tables consists of one single field won't let you to get your data back!

So, instead of first three you have to make ONE table first, contains all your fields.

Then create ONE insert query that have to utilize prepared statements instead of interpolating variables like you do here.

And you ought to use LAST_INSERT_ID()/PDO::insertId() to handle table relations, instead of these selects.

edited Apr 3, 2014 at 6:53

answered Apr 3, 2014 at 6:44

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

2 Comments

Emilie Over a year ago

Yes, the single field tables are a bit weird, in fact it is to do some auto-completion. In the context of my project, it make sense. Thanks for the LAST_INSERT_ID()/PDO::insertId()

Your Common Sense Over a year ago

IT DOESN'T make ANY sense in ANY context. Your idea on whatever "auto-completion" is just wrong. One can make auto-completion with regular tables all right

Collectives™ on Stack Overflow

Query with PHP using PDO are not inserted in MySQL

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related