$SQL = Insert and Update

Question

I'm using PDO in PHP. I've managed to get the first Insert to run but I haven't been able to get the 2nd to. I'm running them on a button click. Here are my basic two SQL Queries.

INSERT INTO MockTable ( 
     `A`, `B`, `C`, `D` ) 
SELECT `A`, `B`, `C`, `D`
FROM SecondaryTable
ORDER BY A ASC;

UPDATE MockTable
SET
    `Name` = From Form,
    `Date` = Actual Date
WHERE
    `Name` = ''
AND `Date` = '0000-00-00';

And I'm using

.$_POST["Name"].
.date("Y-m-d").

For Name and Date

PHP Code (really messy because I was trying to fix it):

<?php
if(isset($_POST["submit"])){

try {
require '/home4/kylex63/MockUpConfig.php';

$sql = "
INSERT INTO MockTable (`A`, `B`, `C`, `D`) 
SELECT `A`, `B`, `C`, `D` 
FROM SecondaryTable 
ORDER BY A ASC;


UPDATE `MockTable` 
SET `Name` = ".$_POST["Name"].", `Date` = ".date("Y-m-d")." 
WHERE `Name` = \'\' 
AND `Date` = \"0000-00-00\";
";

if ($db->query($sql)) {
echo "<script type= 'text/javascript'>alert('New Record Inserted     Successfully');</script>";
}

1. You have to set PDO in Exception mode to see the actual error. 2. No quotes have to be ever used around placeholders. Read how to use PDO properly [here](https://phpdelusions.net/pdo) — Your Common Sense, Feb 24 '16 at 09:38
Don't put quotes around `?` placeholders. This is not the syntax for prepared statements. SQL engine knows what to do with the parameters based on their data types: don't add quotes! — trincot, Feb 24 '16 at 09:39
Added my php up until the else. I get errors when I try and include the 2nd query. Second one doesn't run when I try and separate them. — Kyle Dunne, Feb 24 '16 at 09:46
Heads up, you got two separate queries in one($sql) variable. Separate them maybe like -> $sql_one = "INSERT ....." $sql_two = "UPDATE ........" — isnisn, Feb 24 '16 at 09:48
DO NOT use string concatenation to build SQL queries. Use prepared statements instead. See http://www.phptherightway.com/#pdo_extension. You'll note that your code looks similar to the example that is shown as being the wrong way to do it (they've used $_GET rather than $_POST in the example, but the effect is the same). Below that example is another one that shows the right way to do it. Read it. Understand it. Change your code. — Simba, Feb 24 '16 at 09:56

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

You should use prepared statements to avoid SQL injection, and then use the ? place holders without quotes. I would also execute one statement at a time, so you can control what needs to happen if the first or the second statement fails:

$db->query("
    INSERT INTO MockTable (`A`, `B`, `C`, `D`) 
    SELECT `A`, `B`, `C`, `D` 
    FROM SecondaryTable 
    ORDER BY A ASC");

$stmt = $db->prepare("
    UPDATE MockTable 
    SET `Name` = ?, `Date` = ? 
    WHERE `Name` = '' 
    AND `Date` = '0000-00-00'");

$stmt->execute(array($_POST["Name"], date("Y-m-d")));

echo "<script type= 'text/javascript'>
    alert('New Record Inserted Successfully');
</script>";

This should make the UPDATE work as expected.

You could best have the error handling strategy set to PDO::ERRMODE_EXCEPTION:

PDO::ERRMODE_EXCEPTION

In addition to setting the error code, PDO will throw a PDOException and set its properties to reflect the error code and error information. This setting is also useful during debugging, as it will effectively "blow up" the script at the point of the error, very quickly pointing a finger at potential problem areas in your code (remember: transactions are automatically rolled back if the exception causes the script to terminate).

Exception mode is also useful because you can structure your error handling more clearly than with traditional PHP-style warnings, and with less code/nesting than by running in silent mode and explicitly checking the return value of each database call.

See Exceptions for more information about Exceptions in PHP.

Code to set this error handler strategy (right after you initialize the connection):

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Make sure that all exceptions and warnings are reported, by setting the error_reporting and display_errors directives appropriately in php.ini.

errorInfo() is unreliable source. Please learn [how to report PDO errors properly](https://phpdelusions.net/pdo#errors) — Your Common Sense, Feb 24 '16 at 10:02
@YourCommonSense, many thanks for that enlightenment! I updated my answer to hopefully not point in the wrong direction with respect to error handling. — trincot, Feb 24 '16 at 14:11

$SQL = Insert and Update

1 Answers1