The default phpmyadmin functionality to import a CSV isn't working for me (spins and spins no errors) so I wrote a script to import it. I am not getting any errors from my script but none of the rows are importing! Can you see what I may be doing wrong?
<?php
$db_host = 'secret';
$db_user = 'secret';
$db_pass = 'secret';
$db_database = 'secret';
/* End config */
$link = mysql_connect($db_host,$db_user,$db_pass) or die('Unable to establish a DB connection');
echo "Successfully connected
";
mysql_select_db($db_database,$link);
mysql_query("SET names UTF8");
//get the csv file
// $file = $_FILES[csv][dc.csv];
$handle = fopen("dc.csv","r");
$counter = 0;
$data = array();
echo "starting import
";
while( ($line = fgetcsv($handle)) !== false) {
$data[] = $line;
//if ($data[0]) {
mysql_query("INSERT INTO private_schools (school_name,
city,
state,
county_code,
zip_code,
NA2,
low_grade,
high_grade,
NA3,
NA4,
NA5,
grade_range,
NA6,
NA7,
NA8,
NA9,
NA10,
NA11,
NA12,
NA13,
NA14,
NA15,
NA16) VALUES
(
'".addslashes($data[$counter][0])."',
'".addslashes($data[$counter][1])."',
'".addslashes($data[$counter][2])."',
'".addslashes($data[$counter][3])."',
'".addslashes($data[$counter][4])."',
'".addslashes($data[$counter][5])."',
'".addslashes($data[$counter][6])."',
'".addslashes($data[$counter][7])."',
'".addslashes($data[$counter][8])."',
'".addslashes($data[$counter][9])."',
'".addslashes($data[$counter][10])."',
'".addslashes($data[$counter][11])."',
'".addslashes($data[$counter][12])."',
'".addslashes($data[$counter][13])."',
'".addslashes($data[$counter][14])."',
'".addslashes($data[$counter][15])."',
'".addslashes($data[$counter][16])."',
'".addslashes($data[$counter][17])."',
'".addslashes($data[$counter][18])."',
'".addslashes($data[$counter][19])."',
'".addslashes($data[$counter][20])."',
'".addslashes($data[$counter][21])."',
'".addslashes($data[$counter][22])."'
)
" or die("THere was an issue!!: " . mysql_error()));
//print($data[$counter][0]);
print "INSERT INTO private_schools (school_name,
city,
state,
county_code,
zip_code,
NA2,
low_grade,
high_grade,
NA3,
NA4,
NA5,
grade_range,
NA6,
NA7,
NA8,
NA9,
NA10,
NA11,
NA12,
NA13,
NA14,
NA15,
NA16) VALUES
(
'".addslashes($data[$counter][0])."',
'".addslashes($data[$counter][1])."',
'".addslashes($data[$counter][2])."',
'".addslashes($data[$counter][3])."',
'".addslashes($data[$counter][4])."',
'".addslashes($data[$counter][5])."',
'".addslashes($data[$counter][6])."',
'".addslashes($data[$counter][7])."',
'".addslashes($data[$counter][8])."',
'".addslashes($data[$counter][9])."',
'".addslashes($data[$counter][10])."',
'".addslashes($data[$counter][11])."',
'".addslashes($data[$counter][12])."',
'".addslashes($data[$counter][13])."',
'".addslashes($data[$counter][14])."',
'".addslashes($data[$counter][15])."',
'".addslashes($data[$counter][16])."',
'".addslashes($data[$counter][17])."',
'".addslashes($data[$counter][18])."',
'".addslashes($data[$counter][19])."',
'".addslashes($data[$counter][20])."',
'".addslashes($data[$counter][21])."',
'".addslashes($data[$counter][22])."'
)
" ;
$counter ++;
if ($counter === 3){
exit();
}
// }
}
//var_dump($data);
?>
Printing the sql statement yields
INSERT INTO private_schools (school_name, city, state, county_code, zip_code, NA2, low_grade, high_grade, NA3, NA4, NA5, grade_range, NA6, NA7, NA8, NA9, NA10, NA11, NA12, NA13, NA14, NA15, NA16) VALUES ( 'Evangel Classical Christian School', 'Alabaster', 'AL', '1117', '35007', '21', 'KG', '12', '5', '2', '8', 'K-12', '295', '40.7', '28.8', '10.2', '99', '0.7', '0', '0', '0', '0', '0.3' )
Sample of CSV file:
Abbeville Christian Academy,Abbeville,AL,1067,36310,42,PK,12,6,2,8,K-12,157,52.9,16.6,9.5,95.5,0.6,0,1.9,0,0.6,1.3
Kingwood Christian School,Alabaster,AL,1117,35007,21,KG,12,6,2,4,K-12,413,44.1,33.1,12.5,88.9,6.8,0,1.9,0,0.2,2.2
Evangel Classical Christian School,Alabaster,AL,1117,35007,21,KG,12,5,2,8,K-12,295,40.7,28.8,10.2,99,0.7,0,0,0,0,0.3
Westwood Bapt Weekday Edu Ministry,Alabaster,AL,1117,35007,21,PK,KG,6,2,5,K-6,12,50,1,12,100,0,0,0,0,0,0
Look into using the mysqli extension as mysql is deprecated.
Manpage for addslashes says
To escape database parameters, DBMS specific escape function (e.g. mysqli_real_escape_string() for MySQL or pg_escape_literal(), pg_escape_string() for PostgreSQL) should be used for security reasons.
For mysql try mysql_real_escape_string, and for mysqli try mysqli_real_escape_string.
I don't use this format to check/display errors, but I think the way you would write this is:
mysql_query($yourInsertQueryString) or die("THere was an issue!!: " . mysql_error());
Are all of those column types really strings?
You can try this approach too, quoting the fields too and using the "coupple" assingment to verify the correct assignment (a frequent problem with csv importing)
<?php
$db_host = 'secret';
$db_user = 'secret';
$db_pass = 'secret';
$db_database = 'secret';
$_db = mysql_connect($db_host,$db_user,$db_pass);
mysql_select_db($db_database,$_db);
$fileds = array(
"school_name",
"city",
"state",
"county_code",
"zip_code",
"NA2",
"low_grade",
"high_grade",
"NA3",
"NA4",
"NA5",
"grade_range",
"NA6",
"NA7",
"NA8",
"NA9",
"NA10",
"NA11",
"NA12",
"NA13",
"NA14",
"NA15",
"NA16",
);
$handle = fopen("dc.csv","r");
$counter = 0;
$data = array();
echo "starting import
";
while( ($line = fgetcsv($handle)) !== false) {
$data[] = $line;
}
foreach($data as $row){
if(count($fileds) != count($row)){
continue;
}
$couples = array();
foreach($fileds as $k => $v){
$couples[] = sprintf("`%s` = '%s",$v ,mysql_real_escape_string($row[$k],$_db));
}
$_sql = 'INSERT INTO private_schools '.join(',', $couples).';';
echo "$_sql
";
mysql_query($_sql);
}
// and remeber to commit if you use a transactiona storage engine
mysql_query('commit',$_db); // fixed for "old" client
You could try using mysqlimport. You run in from a command window.
It does not matter if you are using windows, it is available on that as well, just the same.