Jump to content

Inserting csv records into a mysql table


Go to solution Solved by mac_gyver,

Recommended Posts

I'm sorry to be back so soon, but I'm up against another mystery.  I'm using the code below to enter a bunch of css data from a spreadsheet into a mysql table.  I think the data file is OK.  The array created by the script checks out with print_r.  (There are many more records than shown.  I truncated it to save space.)

The problem is that I get this error regarding my sql statement, not the data or anything else:

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'check, name, phone, email, entry_fee, print_fee, image_name, description, med...' at line 1 in /Users/studio/Sites/BannerProject/b-as/_test_site/csv_to_array.php:242 Stack trace: #0 /Users/studio/Sites/BannerProject/b-as/_test_site/csv_to_array.php(242): PDO->prepare('INSERT INTO tbl...') #1 {main} thrown in /Users/studio/Sites/BannerProject/b-as/_test_site/csv_to_array.php on line 242

I've typed it in a dozen times to make sure there are no errors and keep getting the same error.

I tried running a test file and gradually increasing the number of placeholders and at some point I always end up getting the same error,  I can delete the most recent addition and it works again.  Then I can add another placeholder exactly as before and it works the second time.  It feels like a ghost in the machine.

Any idea what I am doing wrong?  An I typing something I don't see?

<?php
	
require '__classes/Db.php';

$csvData = '1,FALSE,Carol Lettko,,,TRUE,FALSE,Carol_Lettko-DSC_3022.jpg,Baby Herons/Brickyard,photo,,,
,,,925-285-0320,cjl164@aol.com,,,Carol_Lettko-DSC_0164.JPG,Heron/Brickyard,photo,,,
,,,,,,,Carol_Lettko-IMG_5723.jpg,Kayaker/Brickyard,photo,,,
,,,,,,,,,,,,
2,FALSE,Louise Williams,,,TRUE,FALSE,Louise_Williams-BirdsOfAFeatherAOPR.jpg,Alligator with Words,Book Excerpt,,,
,,,510-232-9547,lkw@louisekwilliams.com,,,Louise_Williams-Hope-TheFairyChickenAOPR.jpg,Hope The Fairy Chicken,,,,
,,,The d exatrfrfvct/.*tygrvurr,,,,,,,,,
,,,,,,,,,,,,
3,TRUE,Dorothy Leeland,,lelanddorothy@gmail.com,TRUE,FALSE,DJ_Lee-bridge at dusk 700px width.jpg,Bridge,photo,,,
,,,,,,,DJ_Lee-friends 700px width.jpg,Friends,photo,,,
,,,,,,,DJ_Lee-hybiscus 700 px wide.jpg,Hibiscus,photo,,,
,,,,,,,,,,,,
4,FALSE,Rita Gardner,,,TRUE,FALSE,Rita_Gardner-Explosion - Gardner photo.JPG,Explosion,photo,,,
,,,,tropicrita@msn.com,,,Rita_Gardner-Ferry Point tables and chair - Gardner.JPG,Ferry Point Tables,photo,, ,
,,,,,,,Rita_Gardner-Forks - Gardner photo.JPG,Forks,photo,,,
,,,,,,,,,,,,
';

$lines = explode(PHP_EOL, $csvData);
$array1 = array();
foreach ($lines as $line) {
    $array1[] = str_getcsv($line);
}

$stmt = $pdo->prepare("INSERT INTO tbl_person_data (number, check, name, phone, email, entry_fee, print_fee, image_name, description, medium, select, orient, site) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)");

    foreach ($array1 as $row)
    {
        $stmt->execute('$row');
    }

echo '<pre>';
print_r($array1);
echo '</pre>';


?>

 

Link to post
Share on other sites

PS -- This is a sample of the execute array as print_r displays it.  It looks right to me:

Array
(

    [0] => Array
        (
            [0] => 1
            [1] => FALSE
            [2] => Carol Lettko
            [3] => 
            [4] => 
            [5] => TRUE
            [6] => FALSE
            [7] => Carol_Lettko-DSC_3022.jpg
            [8] => Baby Herons/Brickyard
            [9] => photo
            [10] => 
            [11] => 
            [12] => 
        )

    [1] => Array
        (
            [0] => 
            [1] => 
            [2] => 
            [3] => 925-285-0320
            [4] => cjl164@aol.com
            [5] => 
            [6] => 
            [7] => Carol_Lettko-DSC_0164.JPG
            [8] => Heron/Brickyard
            [9] => photo
            [10] => 
            [11] => 
            [12] => 
)       
     

 

Link to post
Share on other sites
  • Solution
5 minutes ago, kenoli said:

for the right syntax to use near 'check

check is a reserved work and should not be used as a column name. either use a different name for that column or you must enclose the column name in back-ticks to cause it to be treated as an identifier.

Link to post
Share on other sites
1 hour ago, mac_gyver said:

check is a reserved work and should not be used as a column name. either use a different name for that column or you must enclose the column name in back-ticks to cause it to be treated as an identifier.

Thanks.  That makes sense.  I also changed "select" as I thought that might be a problem and that issue is now gone.  

I had to fix a couple of other errors that I figured out and it worked!

--Kenoli

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.