CODEWITHSUNDEEP
phpmysqlwebpdo
jrgilman
jrgilman

Reputation: 483

Can't INSERT into mysql via PDO

I've been banging my head against this PHP insert code for the last day or so. I can't seem to find the problem and it persists after 2 complete re-do's of the code. The problem is pretty straightforward, it won't insert anything, and I've cut down on the possibilities by simple directly inputting values for the variables rather than using GET to obtain them from the HTML via JS. Maybe someone on here can spot what is going on. I know PDO is definitely working though, because I am able to obtain info from the mysql table.

<?php

$tbl = 'transactions';
$acc = 'blah1';
$date = '2014-07-01';
$cp = 'counterparty';
$ctg = 'category';
$dbt = 1.00;
$crd = 0.00;

$user = "root";
$pass = #######; // Note: the password is actually in the file on my side.

try {
    $con = new PDO("mysql:host=localhost;dbname=budget;charset=utf8",$user,$pass);
    $con -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}

$sql = $con -> prepare("INSERT INTO transactions VALUES (:account, :date, :counterparty, :category, :debit, :credit)");

$sql -> bindValue(':account', $acc, PDO::PARAM_STR);
$sql -> bindValue(':date', $date, PDO::PARAM_STR);
$sql -> bindValue(':counterparty', $cp, PDO::PARAM_STR);
$sql -> bindValue(':category', $ctg, PDO::PARAM_STR);
$sql -> bindValue(':debit', strval($dbt), PDO::PARAM_STR);
$sql -> bindValue(':credit', strval($crd), PDO::PARAM_STR);

$sql -> execute();

$dbh = NULL;
?>

Any help would be appreciated. Thanks.

Upvotes: 2

Views: 550

Answers (2)

jrgilman
jrgilman

Reputation: 483

I answered this in the comments of my post, but I'm gonna post it up as an answer so that it is more visible:

Well the code works now, I added this to the JS: xmlhttp.onreadystatechange=function() { if (xmlhttp.readyState==4 && xmlhttp.status==200) {}} in an attempt to see what was going on with the try-catch blocks, and suddenly the code started to work. I'm not sure I understand why this suddenly fixed the problem? Could someone care to explain.

Upvotes: 1

sudharsan_kn
sudharsan_kn

Reputation: 46

Instead of this:

$sql = $con -> prepare("INSERT INTO transactions VALUES (:account, :date, :counterparty, :category, :debit, :credit)");

Try this way:

$sql = $con -> prepare("INSERT INTO transactions (ACCOUNT,DATE,COUNTERPARTY, CATEGORY,DEBIT,CREDIT) VALUES (:account, :date, :counterparty, :category, :debit, :credit)");

Things to check:

1) Check the table definition for exact fieldnames.

2) Check the datatypes declared in the table. (In your code, you tried to insert string to all the fields)

3) Use Try, Catch block

 try 
{
// Your code
}
catch (PDOException $pe)
{
    $con->rollBack();

    die($pe->getMessage());
}

Upvotes: 0

Related Questions