PHP Forum

**daddy2five** · 08:37 PM

OK it's been a while since my last post. I finally got MySQL running right on my computer. I am learning to create tables with PHP and Im running into problems. I am getting a syntax error back from MySQL. I dont know what it is maybe someone can enlighten me on what I'm doing wrong. Here is the PHP code.

PHP Code:


			
<?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 .=",";
                                                     } 
}

Somethings not right in this code. Please help.
Thanks.

**colbyt** · 04-26-2006, 08:36 PM

I am not good enough with php to spot your problem. I can copy and paste. Here is one section of an install tables that I know works. Maybe you can figure it out from that.

PHP Code:


			
$q1 = "DROP TABLE IF EXISTS class_admin";
mysql_query($q1) or die(mysql_error()." at row ".__LINE__);
$q1 = "CREATE TABLE class_admin (
AdminID int(10) NOT NULL auto_increment,
username varchar(50) NOT NULL default '',
password varchar(50) NOT NULL default '',
PRIMARY KEY (AdminID))";
mysql_query($q1) or die(mysql_error()." at row ".__LINE__);
$q1 = "INSERT INTO class_admin VALUES (1, 'admin', 'admin')";
mysql_query($q1) or die(mysql_error()." at row ".__LINE__);

Hope this helps you.

**daddy2five** · 04-26-2006, 08:42 PM

I should have told you all that am trying to create the table from a form I created with HTML. I guess you can figure that out from the code though. Thanks.

**Crimson** · 04-26-2006, 09:03 PM

Are you sure it's a MySQL error you're getting? Because you have a tiny error in your PHP in these lines:

Code:

if($_POST[field_length] [$i] !=""){
          $sql.=" ("$_POST[field_length][$i]."),";
}

You need to add a "." between the starting " (" string and the $_POST reference, like this:

Code:

if($_POST[field_length] [$i] !=""){
          $sql.=" (" . $_POST[field_length][$i]."),";
}

**Crimson** · 04-26-2006, 09:03 PM

(double post, sorry)

**daddy2five** · 04-26-2006, 11:32 PM

OK, Im at home now I can just copy and paste my code. I was at work when i made this post. So I was going from memory as to how the code goes. OK first of all here is the error message 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 '" (ITEM_ID int (5),ITEM_TITLE varchar (50),ITEM_DESC text,"

Here is the code of the form that is generated by php that gives me the error

PHP Code:


			
<?php
if ( (!$_POST[table_name]) || (!$_POST[num_fields])) {
 header("Location: http://localhost/show_createtable1.html");
 exit;
}
?>
<HTML>
<HEAD>
<TITLE>Create a Database Table: Step 2</TITLE>
</HEAD>
<BODY>
<H1> Define fields for <?php echo "$_POST[table_name]"; ?></H1>
<FORM method="POST" action="do_createtable.php">
<INPUT type="hidden" name="table_name" value=<?php echo "$_POST[table_name]"; ?>">
<table cellspacing=5 cellpadding=5>
<tr>
<th>FIELD</th><th>FIELD TYPE</th> <th>FIELD LENGTH</th></tr>
<?php
for ($i = 0 ; $i <$_POST[num_fields]; $i++) {
 echo "
<tr>
<td align=center>
<input type= 'text' name= 'field_name[]' size= '30'>
</td>
<td align=center>
<select name='field_type[]'>
<option value='int'>int</option>
<option value='text'>text</option>
<option value='varchar'>varchar</option>
<option value='float'>float</option>
</select>
</td>
<td align=center>
<input type='text' name='field_length[]' size='5'>
</td>
</tr>";
}
?>
<tr>
<td align=center colspan=3>
<INPUT type="submit" value"Create Table"></td>
</tr>
</table>
</FORM>
</BODY>
</HTML>

And here is the code that suppose to make the table in MySQL.

PHP Code:


			
<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("localhost","user","password") 
or die (mysql_error() );
//select database
$db = mysql_select_db("bravo" , $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>

Ive been looking at this code now for close to 8 hours. Maybe someone here can give me a reason for the error.

**dennismp** · 04-27-2006, 04:41 AM

Can you print out the $sql?

**daddy2five** · 04-27-2006, 09:51 AM

What do you mean print the sql? If you mean just send the query via print or echo, I cant do that. Because I want to build the Tables though forms and be able to populate the database via HTML forms. The code I have here is just not sending the correct syntax to MySQL. Just cant find out where the error is in the syntax. Thanks.

**0beron** · 04-27-2006, 07:14 PM

Quote:

Originally Posted by daddy2five

What do you mean print the sql?

He means put a line under the code which generates the sql statement, so it reads:

PHP Code:


			
$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  .= "); ";



echo $sql; // <-- add this line

This is just a temporary thing so we can see the whole query, look for the error and then fix the PHP so that it generates a valid query.

**daddy2five** · 04-27-2006, 09:37 PM

Alright, sorry for the misunderstanding. Here is the MySQL syntax im trying to send though this code.

CREATE TABLE MY_PRODUCTS (ITEM_ID int(5), ITEM_TITLE varchar (50), ITEM_DESC text, ITEM_PRICE float);

This will create the table and the fields that i need.

I am will be working on the code that populates these fields next, but I need to find out what is going wrong in my loop. Im close to figuring it out. Im just at work right now and I cant do anything. I appreciate all the help. Thanks.

**daddy2five** · 11:38 PM

OK I added the echo statement. here is what I got. I can see why I am getting the error.
CREATE TABLE MY_PRODUCTS" (ITEM_ID int (5),ITEM_TITLE varchar (50),ITEM_DESC text,ITEM_PRICE float);
OK maybe I wasnt as close to figuring this out as I thought. How do I get rid of the " at the end of the MY_PRODUCTS ?

**daddy2five** · 04-27-2006, 11:51 PM

I got it now thank you all who helped. the problem was not in the loop. I was in the first set of code here:

PHP Code:


			
<HTML>
<HEAD>
<TITLE>Create a Database Table: Step 2</TITLE>
</HEAD>
<BODY>
<H1> Define fields for <?php echo "$_POST[table_name]"; ?></H1>
<FORM method="POST" action="do_createtable.php">
<INPUT type="hidden" name="table_name" value=<?php echo "$_POST[table_name]"; ?>"> // <----I forgot the " at the value="<?php...
<table cellspacing=5 cellpadding=5>
<tr>
<th>FIELD</th><th>FIELD TYPE</th> <th>FIELD LENGTH</th></tr>
<?php

Thanks to everyone who tried to help me.
Talkupation for you Oberon! For showing me how to do the echo statement. That really helped me. Thanks.