So, I’ve been trying to use the bindParam function to add data to a MySQL table. It doesn’t like me. Keeps giving me this error:
PDO Exception Caught. Error with the database:
SQL Query: INSERT INTO inventory (DateEntered, DateSold, LotNumber, StorageLocation, Title, Author, ISBN, Condition, CoverPrice, PaidPrice, SellingPrice, Shipping, WebsiteFee, HB, PB, Genre, SellingOnline) VALUES (:DateEntered, :DateSold, :LotNumber, :StorageLocation, :Title, :Author, :ISBN, :Condition, :CoverPrice, :PaidPrice, :SellingPrice, :Shipping, :WebsiteFee, :HB, :PB, :Genre, :SellingOnline)Error: 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 'Condition, CoverPrice, PaidPrice, SellingPrice, Shipping, WebsiteFee, HB, PB, Ge' at line 2
After some searching, it looked like it wanted me to tell bindParam exactly what data type I was using. Unfortunately, I can’t find a PDO::PARAM for date or float. Is there one?
Here is my function. Any suggestions on how to deal with the date and float types would be appreciated.
function insertData($dataArray)//need to test this to make sure it works correctly
{
$DateEntered = $dataArray['DateEntered'];
$DateSold = $dataArray['DateSold'];
$LotNumber = $dataArray['LotNumber'];
$StorageLocation = $dataArray['StorageLocation'];
$Title = $dataArray['Title'];
$Author = $dataArray['Author'];
$ISBN = $dataArray['ISBN'];
$Condition = $dataArray['Condition'];
$CoverPrice = $dataArray['CoverPrice'];
$PaidPrice = $dataArray['PaidPrice'];
$SellingPrice = $dataArray['SellingPrice'];
$Shipping = $dataArray['Shipping'];
$WebsiteFee = $dataArray['WebsiteFee'];
$HB = $dataArray['HB'];
$PB = $dataArray['PB'];
$Genre = $dataArray['Genre'];
$SellingOnline = $dataArray['SellingOnline'];
try
{
$dbh = new PDO(dsn, user, password);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = 'INSERT INTO inventory
(DateEntered, DateSold, LotNumber, StorageLocation, Title, Author, ISBN, Condition, CoverPrice, PaidPrice, SellingPrice, Shipping, WebsiteFee, HB, PB, Genre, SellingOnline)
VALUES (:DateEntered, :DateSold, :LotNumber, :StorageLocation, :Title, :Author, :ISBN, :Condition, :CoverPrice, :PaidPrice, :SellingPrice, :Shipping, :WebsiteFee, :HB, :PB, :Genre, :SellingOnline)';
$stmt = $dbh->prepare($sql);
//$stmt->bindParam(':id', NULL);
$stmt->bindParam(':DateEntered',$DateEntered); //date
$stmt->bindParam(':DateSold',$DateSold); //date
$stmt->bindParam(':LotNumber',$LotNumber,PDO::PARAM_STR); //text
$stmt->bindParam(':StorageLocation',$StorageLocation,PDO::PARAM_STR); //text
$stmt->bindParam(':Title',$Title,PDO::PARAM_STR); //text
$stmt->bindParam(':Author',$Author,PDO::PARAM_STR); //text
$stmt->bindParam(':ISBN',$ISBN,PDO::PARAM_INT); //int
$stmt->bindParam(':Condition',$Condition,PDO::PARAM_STR); //text
$stmt->bindParam(':CoverPrice',$CoverPrice); //float
$stmt->bindParam(':PaidPrice',$PaidPrice); //float
$stmt->bindParam(':SellingPrice',$SellingPrice); //float
$stmt->bindParam(':Shipping',$Shipping); //float
$stmt->bindParam(':WebsiteFee',$WebsiteFee); //float
$stmt->bindParam(':HB',$HB,PDO::PARAM_INT); //int
$stmt->bindParam(':PB',$PB,PDO::PARAM_INT); //int
$stmt->bindParam(':Genre',$Genre,PDO::PARAM_STR); //text
$stmt->bindParam(':SellingOnline',$SellingOnline,PDO::PARAM_INT); //int
$stmt->execute();
}
catch (PDOException $e)
{
echo 'PDO Exception Caught. ';
echo 'Error with the database: <br />';
echo 'SQL Query: ', $sql;
echo 'Error: ' . $e->getMessage();
return $e->getMessage();
}
}
I modeled it off of page 53 in the PHP Anthology.