You have an error in your SQL syntax; check the manual that corresponds to

Question

anitg 0 Newbie Poster

14 Years Ago

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?

Please help. Have tried other forums. Nobody could help. Hope to find the solution here.

mysql

2 Contributors
3 Replies
307 Views
7 Hours Discussion Span
Latest Post 14 Years Ago Latest Post by smantscheff

Dani AI

Generated 9 Months Ago

— the $result that asked for shows the root problem: the generated column names still contain spaces, hyphens (and in the original static example a leading ÿþ, i.e. a BOM/encoding artifact). MySQL does not accept unquoted identifiers with spaces or hyphens (hyphen is parsed as minus), so the CREATE TABLE fails with a syntax error. The replacement in the dynamic script did not have the intended effect because of encoding/hidden characters or because the header was parsed naively.

Key points to verify

Check file encoding and remove any BOM (the ÿþ points to UTF‑16 LE interpreted as ISO text).
Don’t split the CSV header with a plain explode(','); use PHP’s fgetcsv() to handle quoted fields.
Trim and normalize each header field, remove non-printable characters, and either quote identifiers with backticks or replace illegal characters with underscores.
The detection of numeric columns via preg_match('/[1-9]/',...) is unreliable — treat emails/phones as text unless the source guarantees pure digits.

A concise, robust approach

Read the real header with fgetcsv().
Remove BOM and control characters, then sanitize: collapse whitespace, replace non-alphanumerics with _, prefix a leading digit with an underscore, and wrap the final identifier in backticks.
Build the column list from those sanitized identifiers and choose safe types (e.g., VARCHAR for emails/phones).
Echo the final SQL and test it in the MySQL client or phpMyAdmin to see the exact parser error if it still fails.

Additional practical notes

Avoid reading a fixed number of bytes (the 5120 limit can truncate a long header).
Move away from the old mysql_* extension; prefer PDO or mysqli for better error messages and security.

All 3 Replies

smantscheff 265 Veteran Poster

14 Years Ago

Show the content of $result before the error occurs.

Reply to this topic

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.

anitg 0 Newbie Poster · Answer 1 · 2012-09-11T11:02:32+00:00

The $result echo is

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

smantscheff 265 Veteran Poster · Answer 2 · 2012-09-11T12:02:34+00:00

Is this the error-producing query? I don't think so. I asked for the content of $result in you 2nd code sample.

You have an error in your SQL syntax; check the manual that corresponds to

Dani AI

Recommended Answers Collapse Answers

All 3 Replies

Recommended Answers