Page 1 of 2

PHP MySQL error code

Posted: Thu Apr 08, 2004 5:10 am
by copachumpy
Hi,
I am very new to both PHP and MySQL.
I have some code that I am working on, with the help of a text book.

I get this strange error code:

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

What does this mean, as I can not work out it, and it's driving me crazy.
What's making it so confusing is that at Line 1, all I have written is the <html> tag. and if I move the HTML code so it is after my PHP code, I still get the same error!

Please help

Thanks

John

Posted: Thu Apr 08, 2004 5:20 am
by malcolmboston
showing us the code would be a big help

Posted: Thu Apr 08, 2004 5:22 am
by copachumpy
Sorry, I didnt include the code.
Here is the code that is causing me this little but very annoying problem:


Code: Select all

<?php
<HTML>
<HEAD>
<TITLE>Create a Database Table: Step 3</TITLE></HEAD>
<BODY>
<h1>Adding table <?php echo "$_POST[table_name]"; ?></h1><?
$sql = "CREATE TABLE $_POST[table_name] ";
for ($i = 0; $i < count($_POST[field_name]); $i++) {
	$sql .= "$_POST[field_name][$i] $_POST[field_type][$i]";
	if ($_POST[field_length][$i] != "") {
	$sql .= " ($_POST[field_length][$i]), ";
	} else {
		$sql .= ", ";
	}
}
$sql = substr($sql, 0, -1);
$sql .= ")";

// create connection 
$conn = mysql_connect("host","user","pass") or die(mysql_error());

// select database
$db = mysql_select_db("testDB", $conn) or die(mysql_error());

// execute SQL query and get result
$sql_result = mysql_query($sql,$conn) or die(mysql_error());

//print success message
if ($sql_result) {
	echo "<P>$_POST[table_name] has been created!</p>";
}
?>
</BODY>
</HTML>
?>

Posted: Thu Apr 08, 2004 5:53 am
by Wayne
you dont have a ( in the SQL ...

Code: Select all

$sql = "CREATE TABLE $_POST[table_name] ( ";

Posted: Thu Apr 08, 2004 6:05 am
by copachumpy
Thanks for the help,
I've put the ( in the SQL..

but still no good,
do I not need to close the bracket that I've just put in?
and If so where?

Still comes up with same error code...

Posted: Thu Apr 08, 2004 6:09 am
by Wayne
you have a space after the comma .... so you need to change the substr to -2 instead of -1!!
їphp]
$sql .= ", ";
}
}
$sql = substr($sql, 0, -1);
ї/php]
you already close the bracket with їphp
$sql .= ")";

Posted: Thu Apr 08, 2004 6:09 am
by JayBird
this line is wrong

Code: Select all

$sql = "CREATE TABLE $_POST[table_name] ";
needs to be..

Code: Select all

$sql = "CREATE TABLE ".$_POST['table_name']."";
That goes for your other SQL statements

Also read Array do's and don'ts - Why is $foo[bar] wrong?

Mark

Posted: Thu Apr 08, 2004 6:12 am
by JayBird
actually, there are so many errors in the code, i have rewritten it correctly for you.

Code: Select all

<HTML> 
<HEAD> 
<TITLE>Create a Database Table: Step 3</TITLE></HEAD> 
<BODY> 
<h1>Adding table <?php echo $_POST[table_name]; ?></h1>

<?php 
$sql = "CREATE TABLE ".$_POST['table_name']." "; 
for ($i = 0; $i < count($_POST['field_name']); $i++) { 
   $sql .= "".$_POST['field_name'][$i] $_POST['field_type'][$i].""; 
   if ($_POST['field_length'][$i] != "") { 
   $sql .= " (".$_POST[field_length][$i]."), "; 
   } else { 
      $sql .= ", "; 
   } 
} 
$sql = substr($sql, 0, -1); 
$sql .= ")"; 

// create connection 
$conn = mysql_connect("host","user","pass") or die(mysql_error()); 

// select database 
$db = mysql_select_db("testDB", $conn) or die(mysql_error()); 

// execute SQL query and get result 
$sql_result = mysql_query($sql,$conn) or die(mysql_error()); 

//print success message 
if ($sql_result) { 
   echo "<P>".$_POST['table_name']." has been created!</p>"; 
} 
?> 
</BODY> 
</HTML>
You may still need to implement Wayne's suggestions.

Mark

Posted: Thu Apr 08, 2004 12:28 pm
by sutejok
it seems like your line 1 is "<?php".

"<?php ?>" is used only used when you want to write php commands. HTML syntax should be outside the "<?php ?>" block, or should be echo-ed out.

Posted: Thu Apr 08, 2004 12:35 pm
by pickle
To help with error reporting, change

Code: Select all

//this
$sql_result = mysql_query($sql,$conn) or die(mysql_error());

//to this
$sql_result = mysql_query($sql,$conn) or die($sql);
It's good practice to do what you did by echoing the error, but by echoing the query, you can see exactly what MySQL is getting as a query, and you can see where the problem is.

Posted: Fri Apr 09, 2004 3:39 am
by copachumpy
OK,
I have implemented all of the suggestions, and thank you very much for all your help.

As I am very new to PHP and I'm trying to get this working for my job, could you help me work out why I'm still getting errors!

my complete code is as follows:

Code: Select all

<?php
<?
$sql = "CREATE TABLE".$_POST['table_name']."";
for ($i = 0; $i < count($_POST['field_name']); $i++) {
	$sql .= "".$_POST['field_name'][$i] $_POST['field_type'][$i]."";
	if ($_POST['field_length'][$i] != "") {
	$sql .= "(".$_POST[field_length][$i]."),";
	} else {
		$sql .= ",";
	}
}
$sql = substr($sql, 0,-1);
$sql .= ")";

// create connection 
$conn = mysql_connect("devoke.ibb.gov","mysqladmin","risc2me") or die($sql);

// select database
$db = mysql_select_db("testDB", $conn) or die($sql);

// execute SQL query and get result
$sql_result = mysql_query($sql,$conn) or die($sql);

//print success message
if ($sql_result) {
	echo "<P>$_POST[table_name] has been created!</p>";
}
?>
<HTML>
<HEAD>
<TITLE>Create a Database Table: Step 3</TITLE></HEAD>
<BODY>
<h1>Adding table 
<?php echo $_POST[table_name]; ?> 

 </h1>
</BODY>
</HTML>
?>
There is still an error on line 4, and I don't understand enough to work it out!

Thanks for all your help

John

Posted: Fri Apr 09, 2004 4:32 am
by feyd
well this isn't a valid statement:

Code: Select all

"".$_POST['field_name'][$i] $_POST['field_type'][$i]."";
I think you may want to replace it with:

Code: Select all

"$_POST[field_name][$i] $_POST[field_type][$i]";

Posted: Fri Apr 09, 2004 5:02 am
by copachumpy
I changed the code, and now I get a page with NO error codes on, but it does display a message :

CREATE TABL)

and yet no table has been created in the database...

the code is as follows:

Code: Select all

<?

$sql = "CREATE TABLE".$_POST['table_name']."";
for ($i = 0; $i < count($_POST['field_name']); $i++) {
	$sql .= "$_POST[field_name][$i] $_POST[field_type][$i]";
	if ($_POST['field_length'][$i] != "") {
	$sql .= "(".$_POST[field_length][$i]."),";
	} else {
		$sql .= ",";
	}
}
$sql = substr($sql, 0,-1);
$sql .= ")";

// create connection 
$conn = mysql_connect("host","user","pass") or die($sql);

// select database
$db = mysql_select_db("testDB", $conn) or die($sql);

// execute SQL query and get result
$sql_result = mysql_query($sql,$conn) or die($sql);

//print success message
if ($sql_result) {
	echo "<P>$_POST[table_name] has been created!</p>";
}
?>
<HTML>
<HEAD>
<TITLE>Create a Database Table: Step 3</TITLE></HEAD>
<BODY>
<h1>Adding table to
<?php echo $_POST[table_name]; ?> 

 </h1>
</BODY>
</HTML>

<?php

?>
Thanks for the help, I guess my next step is start learning this so I don't have to keep asking for help!

Posted: Fri Apr 09, 2004 5:46 am
by feyd
well that tells me that:

Code: Select all

for ($i = 0; $i < count($_POST['field_name']); $i++) {
fails to run. I'd bet that $_POST['field_name'] is not an array.

Posted: Fri Apr 09, 2004 6:09 am
by copachumpy
Does that mean that I have not set it up as an array in the script before this? How do I solve this?