Member Avatar for anitg

I have no issues with the query in the below code. But the second code is throwing the error

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 '' at line 1

Working code :

<?php
mysql_connect("localhost", "","") or die(mysql_error());
mysql_select_db("") or die(mysql_error());

$result ="  CREATE TABLE google_csv(
    ÿþName varchar(80),
    Given_Name varchar(80),
    Additional_Name varchar(80),
    Family_Name varchar(80),
    Yomi_Name varchar(80),
    Given_Name_Yomi varchar(80),
    Additional_Name_Yomi varchar(80),
    Family_Name_Yomi varchar(80),
    Name_Prefix varchar(80),
    Name_Suffix varchar(80),
    Initials varchar(80),
    Nickname varchar(80),
    Short_Name varchar(80),
    Maiden_Name varchar(80),
    Birthday varchar(80),
    Gender varchar(80),
    Location varchar(80),
    Billing_Information varchar(80),
    Directory_Server varchar(80),
    Mileage varchar(80),
    Occupation varchar(80),
    Hobby varchar(80),
    Sensitivity varchar(80),
    Priority varchar(80),
    Subject varchar(80),
    Notes varchar(80),
    Group_Membership varchar(80),
    E_mail_1___Type int(11),
    E_mail_1___Value int(11),
    E_mail_2___Type int(11),
    E_mail_2___Value int(11),
    Phone_1___Type int(11),
    Phone_1___Value int(11),
    Phone_2___Type int(11),
    Phone_2___Value int(11),
    Phone_3___Type int(11),
    Phone_3___Value int(11),
    Address_1___Type int(11),
    Address_1___Formatted int(11),
    Address_1___Street int(11),
    Address_1___City int(11),
    Address_1___PO_Box int(11),
    Address_1___Region int(11),
    Address_1___Postal_Code int(11),
    Address_1___Country int(11),
    Address_1___Extended_Address int(11),
    Website_1___Type int(11),
    Website_1___Value int(11)
)";

echo $result;

mysql_query($result);
echo mysql_error();
?>

Error producing code;

<?php

// GENERATE TABLE FROM FIRST LINE OF CSV FILE

$inputFile = 'google.csv';
$tableName = 'google_csv';

$fh = fopen($inputFile, 'r');
$contents = fread($fh, 5120); // 5KB
fclose($fh);

$fileLines = explode("\n", $contents);

$fieldList = explode(',', $fileLines[0]);

$lastField=count($fieldList)-1;

echo "Total Fields :".count($fieldList)."<br>";

echo "Last Field :".$lastField."<br>";

$result ="CREATE TABLE $tableName(";

for($i = 0; $i < count($fieldList); $i++)
{
    if(preg_match('/[1-9]/', $fieldList[$i]))
    {

    if ($i==$lastField)
    {$result .=str_replace(array(" ",'-'),"_",$fieldList[$i]). ' int(11)';}
    else
    {$result .=str_replace(array(" ",'-'),"_",$fieldList[$i]). ' int(11),';}

    }
    else
    {

    if($i==$lastField)

    {$result .=str_replace(array(" ",'-'),"_",$fieldList[$i]). ' varchar(80)';}
    else
    {$result .=str_replace(array(" ",'-'),"_",$fieldList[$i]). ' varchar(80),';}

    }
}

$result .=")";

if ($result) {echo $result."<br>".$i."<br>";} else {Echo "Some Problem!";}

mysql_connect("localhost", "","") or die(mysql_error());
mysql_select_db("") or die(mysql_error());

mysql_query($result);

echo mysql_error();

?>

I am using the $result output from here in the first code and it works perfectly fine! Where am I going wrong in the second code?

  • 3 Contributors
  • 3 Replies
  • 227 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by broj1

Dani AI

Generated

Most likely cause: the CREATE statement being built has a missing or invalid column name (for example a blank header field or a header that still carries a BOM/CRLF), which produces the MySQL error mentioning an empty identifier. was right to ask you to echo the query — inspect the full SQL string for patterns like , ), ,, or a column fragment that becomes just varchar(80) with no name.

Concrete checklist to fix it:

  • Echo the final $result (or save it to a file) and look for empty column names or trailing commas before running it.
  • Parse the CSV header with a CSV-aware function (fgetcsv or str_getcsv) instead of explode(','). That avoids quoted fields and stray commas.
  • Trim each token (remove \r and \n) and filter out empty tokens (a trailing comma in the header yields an empty element).
  • Remove or convert BOMs / encodings. Common BOMs are UTF‑8 (\xEF\xBB\xBF) and UTF‑16 (\xFF\xFE / \xFE\xFF) — strip them from the first header field or convert the line to UTF‑8.
  • Sanitize and quote identifiers: replace spaces/hyphens and any non‑alphanumeric characters with _, prefix names that start with a digit, and wrap final column names in backticks to avoid collisions with MySQL keywords.

Practical example (read header robustly, sanitize, and build a safe CREATE): 

if (($h = fopen('google.csv','r')) !== false) {
  $fields = fgetcsv($h);
  fclose($h);
  $fields[0] = preg_replace('/^\xEF\xBB\xBF|^\xFF\xFE|^\xFE\xFF/', '', $fields[0]);
  $fields = array_map('trim', $fields);
  $fields = array_filter($fields, function($v){ return $v !== ''; });
  foreach ($fields as $f) {
    $c = preg_replace('/[^A-Za-z0-9_]/', '_', $f);
    if (preg_match('/^[0-9]/', $c)) $c = 'col_'.$c;
    $cols[] = "`$c` VARCHAR(255)";
  }
  $sql = 'CREATE TABLE google_csv('.implode(', ', $cols).')';
  echo $sql;
}

Extra notes: deciding column type purely from header text (looking for digits) is fragile — inspect sample rows instead or default to VARCHAR. Also migrate away from the old mysql_* extension to mysqli or PDO in modern PHP.

Recommended Answers

All 3 Replies

Member Avatar for broj1

Echo the problematic query that the second code generates and post it. Also enclose field names within backticks since there might be a possibility that the generated field name is a mysql keyword.

Member Avatar for estinah

i need help with the codes of the calculator
how to code % and the squareroot

Member Avatar for broj1


Please start a new thread for your question. Post the code there you have so far.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.