Rukikun Rukikun -3 years ago 75
MySQL Question

Add backticks in creating table to mysql with csv file -PHP

I have a php code that will create a table to mysql database using a csv file. However, some column headers are not being read by mysql. The only time that mysql will read the query is when I add backticks(`). Can you help me on where to put backticks in my query? :(

Here's my code:

$file = 'C:\Users\user\Desktop\Sample CSV
Files\672VER.csv';
$table = '`672ver`';

// get structure from csv and insert db
ini_set('auto_detect_line_endings',TRUE);
$handle = fopen($file,'r');
// first row, structure
if ( ($data = fgetcsv($handle) ) === FALSE ) {
echo "Cannot read from csv $file";die();
}
$fields = array();
$field_count = 0;
for($i=0;$i<count($data); $i++) {
$f = strtolower(trim($data[$i]));
if ($f) {
// normalize the field name, strip to 20 chars if too long
$f = substr(preg_replace ('/[^0-9a-z]/', '_', $f), 0, 100);
$field_count++;
$fields[] = $f.' VARCHAR(500)';
}
}

$sqlcreate = "CREATE TABLE $table (" . implode(', ', $fields) . ')';


echo $sqlcreate;

Answer Source

If you enclose the field name in backticks, this will stop it trying to interpret some column names as reserved words...

$fields[] = '`'.$f.'` VARCHAR(500)';
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download