SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2003
    Location
    USA
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PDO, bindParam, MySQL date and float types, don't seem to mix.

    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:
    Code:
    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.
    PHP Code:
    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(dsnuserpassword);
            
    $dbh->setAttribute(PDO::ATTR_ERRMODEPDO::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.
    --David Reagan
    DavidReagan.net

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2003
    Location
    USA
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh. I wish it had told me that when I created the column Condition.

    Thanks!
    --David Reagan
    DavidReagan.net

  4. #4
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you can use backtick to escape any reserved word
    `Condition`

  5. #5
    SitePoint Enthusiast
    Join Date
    Feb 2003
    Location
    USA
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Shrapnel_N5 View Post
    you can use backtick to escape any reserved word
    `Condition`
    Thanks! That fixed it.

    Now to fix the other problems that have turned up.
    --David Reagan
    DavidReagan.net


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •