1

Summary:

I have a function that I'm using to set a key value pair in a MySQL database with PHP, it calls a utility function that does all of the inserting. I know this utility function works (use it with other functions) but for some reason, it is failing to insert the record.

The part that is confusing is that it does NOT throw a PDO Exception, I have the proper errmode set in the connection function so it is confusing me as to why I am getting no response.

Any idea how to resolve this or get it to provide an error?

Function that takes an id and assigns a key/value to it in the database

function setCredit($id){
//sets credit bool
//table to insert
$table='accountMeta';
//key
$key="isCredit";
//key value
$value="Y";
//array that is generated to insert 
$valueArray=array("id"=>$id,"key"=>$key,"value"=>$value,"date"=>mysqlTime(currentDate()));
if (isset($id)){
    //run the insert if $id exists
    $results=insert($valueArray,$table);
    echo $results;

} else {
    //fail if $id doesn't exist
    $results="ERROR: Invalid Inputs";

}
return $results;}

Utility Function that does the inserting

function insert($array,$table){
//$array should be formatted like "column"=>"data",... etc
$columns="";
$values="";
//generate the insert statement
foreach ($array as $id=>$val){
    $columns.=$id.",";
    $columnBind.=":".$id.",";

}

    //cut off some extra commas after our foreach
$columns=rtrim($columns,",");
$columnBind=rtrim($columnBind,",");
    //get dat connection
$DBH=dbConn();

    //prep the PDO


$sql=$DBH->prepare("Insert into $table($columns) VALUES($columnBind)");

//bind each value in the PDO
foreach ($array as $id=>$val){
    $sql->bindValue(":$id",$val);   

}

try{
    //give'r a go
    $sql->execute();
    return "SUCCESS";
} catch (PDOExecption $e){
    //and if she says no:
    return "ERROR: Failed to Insert";
    logMessage("select ".$e->getMessage());
   }
}

Database Connection Function

function dbConn(){
$dbName=confLine("databaseName");
$dbUser=confLine("databaseUser");
$dbPass=confLine("databasePass");
$dbAddress=confLine("databaseAddress");
try{
    $DBH=new PDO("mysql:host=$dbAddress;dbname=$dbName","$dbUser","$dbPass");
    $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e){
    echo "Err connecting to DB";
    logMessage("dbConn ".$e->getMessage());
    exit;
}
  return $DBH;
}
14
  • You're very likely just not committing. Please read php.net/manual/en/pdo.transactions.php Commented Sep 22, 2014 at 3:49
  • 1
    Are you displaying errors? Your dev environment php.ini file should have display_errors = On and error_reporting = E_ALL Commented Sep 22, 2014 at 3:52
  • 2
    Also, how far does your execution get? Do you get "SUCCESS" back from your setCredit() function? Maybe try stepping through with a debugger Commented Sep 22, 2014 at 3:54
  • 1
    @Phil, I forgot to restart apache after altering my php.ini. It is now throwing errors. I can see what I am doing wrong and can actually work on the issue now. Commented Sep 22, 2014 at 4:02
  • 2
    Technically debugging was what I was asking for. I'll answer my own question in detail on what caused the error and cite you. Thanks! Commented Sep 22, 2014 at 4:06

1 Answer 1

1

The Answer

2 issues arose:

PHP.ini wasn't set up to provide the error verbosity required. Thanks to @Phil who suggested in the comments to set display_errors=on, error_reporting=E_ALL in the php.ini file.

After this, I was presented with this error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key,value,date) VALUES('9','isCredit','Y','2014-09-21')' at line 1' in /var/www/html/finance/PHP/utils.inc:133 Stack trace: #0 /var/www/html/finance/PHP/utils.inc(133): PDOStatement->execute() #1 /var/www/html/finance/PHP/functions.inc(116): insert(Array, 'accountMeta') #2 /var/www/html/finance/PHP/setCredit.php(5): setCredit('9') #3 {main} thrown in /var/www/html/finance/PHP/utils.inc on line 133

After some very short looking into there error, I found out that I was using a reserved word in mysql. A completely obvious one, I was using 'key' as a column name. Once I changed the column name to something not reserved, the insert was successful.

Sign up to request clarification or add additional context in comments.

1 Comment

Funny you should post that. When I saw the word key, I immediately thought "reserved word", but didn't say anything because and TBH, I couldn't exactly follow how your code worked. So that's what it was then. Glad you found it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.