Member Avatar for Killer_Typo

heres the complete error that i am getting.

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

does anyone else get these errors at all?

<?php
$host = "localhost"; //location of the mysql
$name = "michael"; //user name for logging into mysql
$pass = ""; //password for logging into mysql

$TBname = "$_POST[TBname]"; //name of the table for Creation
$DBname = "$_POST[DBname]"; //name of the database for working in
$field = "$_POST[field]"; //name of the field
$type = "$_POST[type]"; //type of field
$length = "$_POST[length]"; //length of field
$prim = "$_POST[primary]"; //is field primary key or not
$incr = "$_POST[auto_increment]"; //is field auto_incrementing

 mysql_connect($host, $name, $pass) or die(mysql_error());
 mysql_select_db($DBname) or die(mysql_error());
 
 $sql = "CREATE TABLE $TBname ("; //creates the table

 for ($count = 0; $count < count($field); $count++)
	{
  $sql .= $field[$count] . " " . $type[$count];
   
   if ($incr[$count]  == "y")
	 {
	  $additional = "NOT NULL auto_increment";
	 }
   else
	   {
	 $additional = "";
	}
   if ($prim[$count] == "y")
	 {
	  $additional .= ", primary key (" . $field[$count] . ")";
	 }
   else
	{
	 $additional = "";
	}
   if ($length[$count] != "")
	 {
	  $sql .= " (" .$length[$count] . ") $additional ,";
	 }
   else
	{
	 $sql .= " $additional ,";
	}
	}
// clean up the end of the string
$sql = substr($sql, 0, -1);
$sql .= ")";

$result = mysql_query($sql, mysql_connect($host, $name, $pass)) or die(mysql_error()); // execute the query

if ($result)
  {
   $msg = "<p>" . $TBname . "has been created</p>";
  }
echo "Adding $TBname to $DBname ....Done<br />";
echo "Creating fields in $TBname ....Done<br />";
echo $msg . "<br />"; 
echo "<a href=\"http://localhost/dbadmin/db_management.php\">Return to Database Management</a>";
?>

so far ive written most of cheap spin off of phpmyadmin without a hitch, but now ive run into this error. any help would be greatly appreciated!

  • 2 Contributors
  • 2 Replies
  • 411 Views
  • 1 Year Discussion Span
  • Latest Post Latest Post by tubajake711

Dani AI

Generated

Brief summary and quick fixes for the CREATE TABLE problems seen in this thread (addresses the issues raised by and ): the most common root causes are (a) a leftover trailing comma before the final ) in the CREATE statement, (b) unescaped or numeric-only column names, (c) applying AUTO_INCREMENT to a non-integer type, and (d) logic that accidentally wipes previously set column attributes. Fixes and a safer way to build the SQL follow.

Build the column list and implode it (avoids trailing commas):

$cols = array();
for ($i=0; $i < count($fields); $i++) {
  $name = quote_ident($fields[$i]);
  $len  = $lengths[$i] ? '(' . intval($lengths[$i]) . ')' : '';
  $attrs = array();
  if ($auto[$i] === 'y') $attrs[] = 'NOT NULL AUTO_INCREMENT';
  if ($primary[$i] === 'y') $attrs[] = 'PRIMARY KEY';
  $cols[] = "$name " . strtoupper($types[$i]) . $len . ($attrs ? ' ' . implode(' ', $attrs) : '');
}
$sql = 'CREATE TABLE ' . quote_ident($table) . ' (' . implode(', ', $cols) . ')';

Quote identifiers and validate types:

function quote_ident($s) {
  return '`' . str_replace('`','``',$s) . '`';
}

Important rules and checks

  • Always quote table/column names with backticks to protect against reserved words, spaces, or numeric-only names.
  • AUTO_INCREMENT only works on integer types (INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT) and the column must be NOT NULL (and usually PRIMARY KEY). Validate the chosen type before adding AUTO_INCREMENT.
  • Don’t reuse one variable for multiple, mutually-exclusive attribute decisions; use a per-column array of attributes to avoid accidentally clearing previously set attributes.

Debugging checklist

  • Echo the final $sql and paste it into the MySQL client or phpMyAdmin to get the precise parser location.
  • Use implode() or rtrim() to avoid trailing commas (the , ) pattern is a telltale sign).
  • Make sure you execute the query on the same connection resource.
  • For newer PHP, migrate from mysql_* to mysqli or PDO for security and future compatibility.

Following the above will resolve the typical syntax errors seen in this thread and make the table-creation code much more robust.

Member Avatar for Killer_Typo

well im not getting that error anymore, but heres the new one.

Heres 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 '123 char (41) ,)' at line 1

where 123 is first row. char is the type, and 41 is the length

and when i try to define one rows as being auto_increment i get this error;

Incorrect column specifier for column 'column name'

<?php
$host = "localhost"; //location of the mysql
$name = "michael"; //user name for logging into mysql
$pass = "engineer"; //password for logging into mysql
$TBname = "$_POST[TBname]"; //name of the table for Creation
$DBname = "$_POST[DBname]"; //name of the database for working in

	$connect = mysql_connect($host, $name, $pass) or die(mysql_error());
	mysql_select_db($DBname) or die(mysql_error());
	
	$sql = "CREATE TABLE $TBname ("; //creates the table
	for ($count = 0; $count < count($_POST[field]); $count++)
	   {
		$sql .= $_POST[field][$count] . " " . $_POST[type][$count];
			
			if ($_POST[auto_increment][$count]  == "y")
			  {
			   $additional = "NOT NULL AUTO_INCREMENT";
			  }
			else
				{
				 $additional = "";
				}
			if ($_POST[primary][$count] == "y")
			  {
			   $additional .= ", primary key (" . $_POST[field][$count] . ")";
			  }
			else
				{
				 $additional = "";
				}
			if ($_POST[length][$count] != "")
			  {
			   $sql .= " (" .$_POST[length][$count] . ") $additional ,";
			  }
			else
				{
				 $sql .= " $additional ,";
				}
	   }
// clean up the end of the string
$sql .= ")";
$result = mysql_query($sql, $connect) or die(mysql_error()); // execute the query
if ($result)
  {
   $msg = "<p>" . $TBname . "has been created</p>";
  }
echo "Adding $TBname to $DBname ....Done<br />";
echo "Creating fields in $TBname ....Done<br />";
echo $msg . "<br />"; 
echo "<a href=\"http://localhost/dbadmin/db_management.php\">Return to Database Management</a>";
?>
Member Avatar for tubajake711

Hi, I've been getting a messege that I have an SQL syntax error from a website, near line 1 at the "
What do I do?

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.