PDO 2 table insert statement

Thats because you're only declaring the second insert. You also need to query it: $dbh->query($sql2)

As for your code, the security is paramount when working with user inputs. PHP handles this using data filtering and PDO provides prepare statement to avoid security pitfalls. Read more before going in production with the above code.

Where do I put the $dbh->query($sql2)

Thanks all - will try
And points noted

<?php
if(isset($_POST["submit"])){
$hostname='localhost';
$username='root';
$password='xxxxxx';

try {
    $dbh = new PDO("mysql:host=$hostname;dbname=inventas",$username,$password);

    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // <== add this line

    $dbh->beginTransaction();

    $sitesSql = "INSERT INTO inventassites (sites_name, sites_email, sites_company, sites_representative, sites_comments, sites_todo, sites_datevisited, sites_timefrom, sites_timeto, sites_return, sites_updated) VALUES (:name, :email, :company, :rep, :comments, :todo, :visit, :from, :to, :return, :updated)";
    $historySql = "INSERT INTO invhistory (histrepname, histwork, histdate, histupdated) VALUES (:siteRep, :work, :date, :updated)";

    $sites = $dbh->prepare($sitesSql);
    $history = $dbh->prepare($historySql);

    $sites->execute([
    'name' => $_POST["site_name"],
    'email' => $_POST["site_email"],
    'company' => $_POST["site_company"],
    'rep' => $_POST["site_rep"],
	'comments' => $_POST["site_comments"],
	'todo' => $_POST["site_todo"],
	'visit' => $_POST["site_visitdate"],
	'from' => $_POST["time_from"],
	'to' => $_POST["time_to"],
	'return' => $_POST["site_return"],
	'updated' => $_POST["radvisit"],

    ]);

    $history->execute([
    'siteRep' => $_POST["site_rep"],
	'work' => $_POST["site_comments"],
	'date' => $_POST["sites_visitdate"],
	'updated' => $_POST["radvisit"],
    ]);

    $dbh->commit();

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

} catch (Exception $e) {

    $dbh->rollBack();
    echo "<script type= 'text/javascript'>alert('Data not successfully Inserted.');</script>";

}
}

?>

Select allOpen in new window

This is my current code. The records are not successfully entered into the database unfortunately "Data not successfully inserted"

Hey doctorbill,

Something, somewhere is clearly throwing an exception. We can get more information about what's going on by examining the message from that exception. In the catch block, take a look at the exception message by calling the getMessage() method:

} catch (Exception $e) {

    $dbh->rollBack();
    printf( "<script type= 'text/javascript'>alert('%s');</script>", $e->getMessage() );

}

Select allOpen in new window

You should now see the error message the the exception contains. Should help you identify what the problem is, but if not, post the error message here and we can take a look.

I tried this script but no exception is thrown up at all

One thing - I notice the php_pdo.dll extension is not in my php.ini file. Could this be the issue
I am running php version 5.5.9 in xampp on windows

If I look at the php.ini I have:
pdo drivers:    mysql, pgsql, sqlite

Hey Bill,

Firstly, I'm presuming PDO is working correctly as your opening question implied that at least one of the inserts worked OK. If PDO wasn't working you wouldn't have got the one insert.

Secondly, you say no Exception is thrown, but you've said that you got the message Data not successfully Inserted. This message only appears if an Exception is thrown as it's in the catch block or the Exception Handler!

Let's bin the script alerts and turn on full error reporting to see if we can find out what's going on:

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

if( isset($_POST["submit"]) ){

    $hostname='localhost';
    $username='root';
    $password='xxxxxx';

    try {

        $dbh = new PDO("mysql:host=$hostname;dbname=inventas",$username,$password);
        $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // <== add this line

        $dbh->beginTransaction();

        $sitesSql = "INSERT INTO inventassites (sites_name, sites_email, sites_company, sites_representative, sites_comments, sites_todo, sites_datevisited, sites_timefrom, sites_timeto, sites_return, sites_updated) VALUES (:name, :email, :company, :rep, :comments, :todo, :visit, :from, :to, :return, :updated)";
        $historySql = "INSERT INTO invhistory (histrepname, histwork, histdate, histupdated) VALUES (:siteRep, :work, :date, :updated)";

        $sites = $dbh->prepare($sitesSql);
        $history = $dbh->prepare($historySql);

        $sites->execute([
            'name' => $_POST["site_name"],
            'email' => $_POST["site_email"],
            'company' => $_POST["site_company"],
            'rep' => $_POST["site_rep"],
            'comments' => $_POST["site_comments"],
            'todo' => $_POST["site_todo"],
            'visit' => $_POST["site_visitdate"],
            'from' => $_POST["time_from"],
            'to' => $_POST["time_to"],
            'return' => $_POST["site_return"],
            'updated' => $_POST["radvisit"],
        ]);

        $history->execute([
            'siteRep' => $_POST["site_rep"],
            'work' => $_POST["site_comments"],
            'date' => $_POST["sites_visitdate"],
            'updated' => $_POST["radvisit"],
        ]);

        $dbh->commit();

        echo "Data Inserted Successfully";

    } catch (Exception $e) {

        $dbh->rollBack();
        echo $e->getMessage();

    }

}

?>

Select allOpen in new window

Run the script again and see what you get. If you're running this code before outputting some html, you may need to view the source to see the error message.

If I run this I get "PDO available" message:

if (!defined('PDO::ATTR_DRIVER_NAME')) {
echo 'PDO unavailable';
}
elseif (defined('PDO::ATTR_DRIVER_NAME')) {
echo 'PDO available';
}

Will try. Just a note - no records are  being inserted from any of the transactions.

Perfect Chris - that error message reporting did it
The error message told me that there was an error in this code:

'date' => $_POST["sites_visitdate"],

It should have been:
'date' => $_POST["site_visitdate"],

All now working
Again - thanks so much for your work on this. You really are a minefield of information and are very precise and accurate with your solutions, focusing on just the problem area. This is why I like using Experts Exchange and the experts (yourself) rather than just trying to find a solution on Google. So much rubbish is out there on the web and in many cases, the "solution" is not addressing the initial problem or is very outdated/inaccurate

Once again - thanks very much

Thanks Chris and Ionut
One final comment to Chris:

When I said that an exception wasn't shown for the code below, I meant and exception message wasn't shown - sorry for the confusion:

printf( "<script type= 'text/javascript'>alert('%s');</script>", $e->getMessage() );

This one did the trick:
 echo $e->getMessage();

Hey Bill,

Excellent. Glad you got there in the end.

If you take one thing away from this it should be error reporting! Whenever you're developing, ALWAYS turn on error reporting. Without it, your script becomes a black box and will often die silently. Just make sure you turn it off again when you go live.

The original exception was trying to create a script tag to alert the message, which is probably why it wasn't showing. When developing (and debugging), it makes life a lot easier if you just view the raw messages - later on your can format them and alert them however you like etc.

And as for 'no records from any transaction being inserted' ... that's the whole point of a transaction. It wraps all your queries into one transaction, and if any part fails, then they will all fail (rollback). If you think about it logically, this makes sense. Often your dataset will depend on information from one or more tables. If only part of that data is created, then the integrity will be compromised, so rather than leaving partial data, it works on an all-or-nothing principle.

Once again Chris - thanks for all your help. I only got there in the end because of your excellent input