[RESOLVED] Mysql Error message VARCHAR

MarkBad311

don't you just love query errors?

I get this Mysql 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 'address_two varchar ,address_three varchar ,postcode varchar ,country varchar ,p' at line 1

I am submitting results of a form that creates a table to this script:

<?php
$db_name = "markbad_markbadsql";
$connection=mysql_connect ("localhost", "markbad_drpl1", "removed")
 or die ('I cannot connect to the database because: ' . mysql_error());
$db = mysql_select_db ($db_name, $connection)
 or die ('I cannot connect to the database because: ' . mysql_error());
//start creating SQL statment
$sql = "CREATE TABLE $_POST[table_name] (";
//continue SQL statement for each field
for ($i = 0; $i < count($_POST[field_name]); $i++) {

$sql .= $_POST[field_name][$i]." ".$_POST[field_type][$i];
  if ($_POST[auto_increment][$i] == "Y") {
    $additional = " NOT NULL auto_increment";
  } else { 
    $additional = "";
  }

  if ($_POST[primary][$i] == "Y") {
    $additional .= " , primary key (".$_POST[field_name][$i].")";
  } else {
    $additional = "";
  }

  if ($_POST[field_length][$i] != "") {
    $sql .= " (".$_POST[field_length][$i].") $additional ,";
	} else {
	  $sql .= "$additional ,";
	}
  }
  // Clean up the end of the string.
  $sql = substr($sql, 0, -1);
  $sql .= ")";
// Execute Query
  $result = mysql_query($sql,$connection)
   or die (mysql_error());
// Get a good result if sucessful
  if ($result) {
  $msg = "<p>".$_POST[table_name]." has been created!</p>";
  }
?>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<link href="http://www.eriescene.com/layout.css" rel="stylesheet" type="text/css" />
<title>Show Fields Script</title>
<link href="layout.css" rel="stylesheet" type="text/css" />
</head>

<body>
 <div id="title">
 </div>

 <div id="frame1">
  <img src="http://www.eriescene.com/images/SideMenu1.gif" alt="Erie Scene Menu" border="0" usemap="#Map" />
  <map name="Map" id="Map">
    <area shape="rect" coords="20,17,171,46" href="index.htm" alt="Home Of Erie Bars" />
    <area shape="rect" coords="19,53,172,80" href="erie-bar-club-event/erie-bars.php" alt="Erie Bars Directory" />
    <area shape="rect" coords="18,87,171,112" href="contact-us.php" alt="Contact The Erie Scene" />
    <area shape="rect" coords="19,118,173,145" href="erie-open-chat/index.php" target="_blank" alt="Erie Chat Rooms" />
    <area shape="rect" coords="21,151,173,178" href="erie-forums/index.php" alt="Erie Forum" />
    <area shape="rect" coords="20,184,173,212" href="erie-pa-events/index.html" alt="Erie Events" />
    <area shape="rect" coords="20,218,172,244" href="free-erie-media.htm" />
    <area shape="rect" coords="19,249,172,280" href="erie/index.htm" alt="Erie Pa Sports Schedule" />
  </map>
 </div>
 <div id="frame2">
  &nbsp;
 </div>
 <div id="frame3">
 </div>
 <div id="frame4">&nbsp;
  <table class="align" width="85%">
   <tr>
    <td height="326">
	 <h1>adding to table <? echo "$result"; ?>...</h1>
	 <? echo "$msg"; ?> 
	 <a href="users.php">Go Back To Users</a>
    </td>
   </tr>
  </table>

<!-- rest of html -->

What am I doing wrong this time? I am pretty sure this script worked before. I just had never input a type "varchar" yet until today and this is the error I got.

rincewind456

That's because you need a size of field ie. VARCHAR(45)

MarkBad311

how do I do that... I don't understand.. I thought the lengths were added when I created the sql statement. I defined them in the table and for every varchar I use it doesn't define a length. I am cluesless here.

Thanks!

rincewind456

This section of your code deals with field lengths

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

It says that if it's blank, leave it blank it needs a default value so it would look like this

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

it also needs to be the first "if" not the third so that its appended to the sql statement at the right place.

MarkBad311

the only problem is I am not leaving the length blank I am filling out and specifing the length it just isn't following through I guess? or do I need to create if statement saying if it is blank this the default if not then place it here?

the other two ifs check to see if it is a primary key or auto incrementing. it should beHow isn't the query in order? this is how it is added:

1st

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

2nd

$sql .= $_POST[field_name][$i]." ".$_POST[field_type][$i];

3rd

$sql .= " (".$_POST[field_length][$1].") $additional ,";

and 4 & 5 together

  $sql = substr($sql, 0, -1);
  $sql .= ")";

I don't see the problem but that must be because I'm a noobie I don't know what I am talking about yet! But we must remeber one thing I am entering field_length in when I am processing the form I just don't understand why it ain't carrying through!

rincewind456

Try echoing out the query before executing it to see what's happening with it.

MarkBad311

this is what echoed:

CREATE TABLE my_contacts (id int NOT NULL auto_increment , primary key (id) ,f_name varchar ,l_name varchar ,address1 varchar ,address2 varchar ,address3 varchar ,postcode varchar ,country varchar ,prim_tel varchar ,sec_tel varchar ,email varchar ,co varchar ,birthday date )

each one of these fields should have a length on it I specified a length

MarkBad311

found the problem in my showfields form i left it out "[]"

I had field_length and not field_length[]

bpat1434

Watch your PHP code....

When referencing Array items, you need to quote strings. So $POST[field_name] is wrong, and $POST['field_name'] is proper.

And I was right, it was in your form (I deleted that part... but I did ask why you didn't post your form).

~Brett