php database entry

Hey Bill,

Without seeing your code, we're left guessing. There are various ways to debug code, ranging from simple data dumping right through to step debugging.

Unless you've got your development environment fully set up, then data dumping is probably the easiest.

First thing to do is to turn on error reporting in your PHP script. Right at the very start, add the following:

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

Select allOpen in new window

Now any errors will be shown (don't run this in production!!). Now, the next step is to visualise the data that you're sending to your PHP script. You say you have a page that should be entering data, so I'm going to assume you have a form making a POST request. If so, then in your PHP script, dump that data:

var_dump($_POST);

Select allOpen in new window

That will show you exactly what data is coming from your form into your PHP script.

That should get you started with some very basic debug info, so give it a go, and if you need further advice, maybe post up your code

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

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["site_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>";
    // echo $e->getMessage();

}
}

?>

Select allOpen in new window

I tried the error reporting and ini_set but nothing was returned
Will try the dump

The dump gave me:
array(18) { ["radvisit"]=> string(12) "Update Visit" ["site_company"]=> string(13) "1922 Clothing" ["companydropdown"]=> string(0) "" ["site_name"]=> string(4) "Bill" ["sitename"]=> string(0) "" ["site_email"]=> string(19) "bill@inventas.co.uk" ["siteemail"]=> string(0) "" ["site_rep"]=> string(11) "Daniel East" ["repdropdown"]=> string(0) "" ["site_comments"]=> string(5) "asaas" ["site_todo"]=> string(5) "asasa" ["time_from"]=> string(5) "14.30" ["time"]=> string(5) "14:30" ["time_to"]=> string(5) "17.30" ["time2"]=> string(5) "17:30" ["site_visitdate"]=> string(10) "2021-11-01" ["site_return"]=> string(10) "2021-11-04" ["submit"]=> string(8) " Submit " }

Your code looks OK, so my guess is that you don't have a submit key in your POST, so this:

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

effectively skips over your entire code. Maybe add an 'else' statement to check (or data dump and make sure it exists!)

if(isset($_POST["submit"])){
  // do your DB stuff
} else {
  echo "We don't have a SUBMIT";
}

Select allOpen in new window

Hmmm. OK - you do have a 'submit' !!

Maybe try simplifying your code. Remove the beginTransaction / commit parts, and try it with just one query / prepare / execute.

Not sure whether this makes a difference or not, but your data dump does seem to have some duplicate info in there, which indicates a potential problem in your HTML form.

Maybe these are causing a problem:
They are the dropdown select boxes in the form:
["companydropdown"]=> string(0) ""
["sitename"]=> string(0) ""
["siteemail"]=> string(0) ""
["repdropdown"]=> string(0) ""

Shouldn't really matter as your script doesn't use them. Have you tried simplifying your code as suggested

If I remove the "beginTransaction / commit parts, " the page does not load

Hmmm. Post up your code as you have it now. Removing the transaction code shouldn't have broken anything.

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

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

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

    $sitesSql = "INSERT INTO siteform (frm_companyname, frm_contact, frm_email, frm_rep, frm_date, frm_return, frm_timefrom, frm_timeto, frm_comment1, frm_comment2, frm_comment3, frm_comment4, frm_comment5, frm_todo1, frm_todo2, frm_todo3, frm_todo4, frm_todo5, frm_updated) VALUES (:compname, :contact, :email, :rep, :date, :datereturn, :timefrom, :timeto, :comment1, :comment2, :comment3, :comment4, :comment5, :todo1, :todo2, :todo3, :todo4, :todo5, :updated)";

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

    $sites->execute([
    'compname' => $_POST["site_company"],
    'contact' => $_POST["site_name"],
    'email' => $_POST["site_email"],
    'rep' => $_POST["site_rep"],
   'date' => $_POST["site_visitdate"],
   'datereturn' => $_POST["site_return"],
    'timefrom' => $_POST["time_from"],
    'timeto' => $_POST["time_to"],
    'comment1' => $_POST["site_comments"],
    'comment2' => $_POST["site_comments2"],
    'comment3' => $_POST["site_comments3"],
    'comment4' => $_POST["site_comments4"],
    'comment5' => $_POST["site_comments5"],
    'todo1' => $_POST["site_todo"],
    'todo2' => $_POST["site_todo2"],
    'todo3' => $_POST["site_todo3"],
    'todo4' => $_POST["site_todo4"],
    'todo5' => $_POST["site_todo5"],
   'updated' => $_POST["radvisit"],

    ]);

    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>";
     //echo $e->getMessage();

}
}

?>

Select allOpen in new window

Ah ha
This is the output:
array(18) { ["radvisit"]=> string(9) "New Visit" ["site_company"]=> string(25) "About You Recruitment Ltd" ["companydropdown"]=> string(0) "" ["site_name"]=> string(4) "test" ["sitename"]=> string(0) "" ["site_email"]=> string(19) "info@ticktockit.net" ["siteemail"]=> string(0) "" ["site_rep"]=> string(12) "Josh McNiven" ["repdropdown"]=> string(0) "" ["site_comments"]=> string(4) "test" ["site_todo"]=> string(4) "test" ["time_from"]=> string(5) "14.15" ["time"]=> string(5) "14:15" ["time_to"]=> string(5) "18.30" ["time2"]=> string(5) "18:30" ["site_visitdate"]=> string(10) "2021-11-01" ["site_return"]=> string(10) "2021-11-03" ["submit"]=> string(6) "Submit" } SQLSTATE[HY000]: General error: 1364 Field 'sites_newvisit' doesn't have a default value

   

 

It is now working - the above error message indicated it was the database fields that were causing the problem
These are now fixed with a "NULL" value
Records are being entered correctly
It looks like the database, having been moved to a new Apache/WAMP installation a few weeks ago, is behaving differently so needed some changes

As always Chris - you are absolutely spot on with the resolution

Yes
What caught me out here was I had another page with a very similar script that was running perfectly - just the fields had been edited
The code you suggested:

} catch (Exception $e) {
    echo $e->getMessage();
}
Was the clincher