PHP MySQL error code

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

copachumpy
Forum Newbie
Posts: 11
Joined: Thu Apr 08, 2004 5:10 am

PHP MySQL error code

Post 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
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

showing us the code would be a big help
copachumpy
Forum Newbie
Posts: 11
Joined: Thu Apr 08, 2004 5:10 am

Post 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>
?>
User avatar
Wayne
Forum Contributor
Posts: 339
Joined: Wed Jun 05, 2002 10:59 am

Post by Wayne »

you dont have a ( in the SQL ...

Code: Select all

$sql = "CREATE TABLE $_POST[table_name] ( ";
copachumpy
Forum Newbie
Posts: 11
Joined: Thu Apr 08, 2004 5:10 am

Post 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...
User avatar
Wayne
Forum Contributor
Posts: 339
Joined: Wed Jun 05, 2002 10:59 am

Post 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 .= ")";
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
User avatar
sutejok
Forum Commoner
Posts: 37
Joined: Wed Mar 24, 2004 4:08 pm

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
copachumpy
Forum Newbie
Posts: 11
Joined: Thu Apr 08, 2004 5:10 am

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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]";
copachumpy
Forum Newbie
Posts: 11
Joined: Thu Apr 08, 2004 5:10 am

Post 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!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
copachumpy
Forum Newbie
Posts: 11
Joined: Thu Apr 08, 2004 5:10 am

Post 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?
Post Reply