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

Question

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;

score 1 · Accepted Answer · answered Jun 14 '17 at 06:39

1

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)';

answered Jun 14 '17 at 06:39

Nigel Ren

56,122
11
43
55

That did it :D Thank you! :) and if you dont mind me asking... what do you mean column names as reserved words? can you give me some example? – Rukikun Jun 14 '17 at 06:46
Column names such as `order`, `select`, `value` ... basically any word that is normally part of a SQL statement is treated differently. – Nigel Ren Jun 14 '17 at 06:48
alright. I'll try to keep those column names in mind when I use my backticks :) thanks for the tip. :) – Rukikun Jun 14 '17 at 06:50

score 1 · Answer 2 · answered Jun 14 '17 at 06:42

1

Change the last line of the loop this way:

$fields[] = '`' . $f . '` VARCHAR(500)';

I hope it works fine,

answered Jun 14 '17 at 06:42

mahyard

1,230
1
13
34

Professor Abronsius · Answer 3 · 2017-06-14T06:39:45.960

0

I think you could do

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

edited Jun 14 '17 at 06:39

answered Jun 14 '17 at 06:34

Professor Abronsius

33,063
5
32
46

sorry... mysql didn't read my query because the comma(,) has not been read :c – Rukikun Jun 14 '17 at 06:37
its saying a comma or closing bracket was expected – Rukikun Jun 14 '17 at 06:38
Thiis will put the ` around the field type definition as well. – Nigel Ren Jun 14 '17 at 06:41
Good point - should have properly inspected the code to see what was going on before leaping to the wrong conclusion – Professor Abronsius Jun 14 '17 at 06:44

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

3 Answers3

Linked