Syntax Error

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

nhwood
Forum Newbie
Posts: 13
Joined: Tue May 23, 2006 2:51 pm

Syntax Error

Post by nhwood »

I don't understant the issue with this code, I keep getting the following 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 '(style, texttest, title) VALUES (osx/osx.css, Welcome to SPIRFBoard!, Title)' at line 1
Here is my code I'm useing, Any Tips?

Code: Select all

<?php require_once('Connections/spirfboard.php'); ?>
<?php
$username4 = $_POST['username'];

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO $username4 (style, texttest, title) VALUES (osx/osx.css, Welcome to SPIRFBoard!, Title)",

  mysql_select_db($database_spirfboard, $spirfboard);
  $Result1 = mysql_query($insertSQL, $spirfboard) or die(mysql_error());

  $insertGoTo = "login.html";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}

mysql_select_db($database_spirfboard, $spirfboard);
$query_Recordset1 = "SELECT * FROM `$username4`";
$Recordset1 = mysql_query($query_Recordset1, $spirfboard) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?>
User avatar
TheMoose
Forum Contributor
Posts: 351
Joined: Tue May 23, 2006 10:42 am

Post by TheMoose »

You have to explicitly put quotes around any string value you want to insert.

Code: Select all

$insertSQL = sprintf("INSERT INTO $username4 (style, texttest, title) VALUES (osx/osx.css, Welcome to SPIRFBoard!, Title)",...
Should be:

Code: Select all

$insertSQL = sprintf("INSERT INTO $username4 (style, texttest, title) VALUES ('osx/osx.css', 'Welcome to SPIRFBoard!', 'Title')",...
nhwood
Forum Newbie
Posts: 13
Joined: Tue May 23, 2006 2:51 pm

Post by nhwood »

I tired that but I now have another error:
Parse error: syntax error, unexpected ';' in /home/nhwood/public_html/spirf/register3.php on line 38
Line 38 has this:

Code: Select all

mysql_select_db($database_spirfboard, $spirfboard);
Whereas my line of code that you told me to fix was line 36.

Does this many any sense?
User avatar
TheMoose
Forum Contributor
Posts: 351
Joined: Tue May 23, 2006 10:42 am

Post by TheMoose »

Yes it does. You're currently using sprintf() without closing the function, so the interpreter is reading it as:

Code: Select all

$insertSQL = sprintf("INSERT INTO $username4 (style, texttest, title) VALUES ('osx/osx.css', 'Welcome to SPIRFBoard!', 'Title')", mysql_select_db($database_spirfboard, $spirfboard);
$Result1 = mysql_query($insertSQL, $spirfboard) or die(mysql_error());
You did not end sprintf, nor did you pass it the second argument. What you might want to do is just change it to:

Code: Select all

$insertSQL = "INSERT INTO $username4 (style, texttest, title) VALUES ('osx/osx.css', 'Welcome to SPIRFBoard!', 'Title')";
mysql_select_db($database_spirfboard, $spirfboard);
$Result1 = mysql_query($insertSQL, $spirfboard) or die(mysql_error());
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

nhwood wrote:Line 38 has this:

Code: Select all

mysql_select_db($database_spirfboard, $spirfboard);
Whereas my line of code that you told me to fix was line 36.
Does this many any sense?
For unended or incorrectly ended strings, PHP will usually give you the line just before the error. Sounds funny, I know, but once you get used to it you will be able to locate issues quickly and easily.
nhwood
Forum Newbie
Posts: 13
Joined: Tue May 23, 2006 2:51 pm

Post by nhwood »

Okay changed, but up comes yet another syntax 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 '(style, texttest, title) VALUES ('osx/osx.css', 'Welcome to SPIRFBoard!', 'Title' at line 1
Just for clarification here is the PHP code now:

Code: Select all

<?php require_once('Connections/spirfboard.php'); ?>
<?php
$username4 = $_POST['username'];

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
$insertSQL = "INSERT INTO $username4 (style, texttest, title) VALUES ('osx/osx.css', 'Welcome to SPIRFBoard!', 'Title')";
mysql_select_db($database_spirfboard, $spirfboard);
$Result1 = mysql_query($insertSQL, $spirfboard) or die(mysql_error()); 

  $insertGoTo = "login.html";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}

mysql_select_db($database_spirfboard, $spirfboard);
$query_Recordset1 = "SELECT * FROM `$username4`";
$Recordset1 = mysql_query($query_Recordset1, $spirfboard) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?>
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

try this and see what it does.

Code: Select all

<?php
$insertSQL = "INSERT INTO $username4 (`style`, `texttest`, `title`) VALUES ('osx', 'Welcome to SPIRFBoard!', 'Title')"; 
?>
User avatar
TheMoose
Forum Contributor
Posts: 351
Joined: Tue May 23, 2006 10:42 am

Post by TheMoose »

On this line:

Code: Select all

$insertSQL = "INSERT INTO $username4 (style, texttest, title) VALUES ('osx/osx.css', 'Welcome to SPIRFBoard!', 'Title')";
Make sure the table $username4 actually exists before you try to insert, and that all 3 of the fields (style, texttest, title) are string (varchar/text) fields. If they are, make sure that each of the fields is capable of storing the proper length of each value (style needs to hold at least 11 characters, etc).
nhwood
Forum Newbie
Posts: 13
Joined: Tue May 23, 2006 2:51 pm

Post by nhwood »

Everah wrote:try this and see what it does.

Code: Select all

<?php
$insertSQL = "INSERT INTO $username4 (`style`, `texttest`, `title`) VALUES ('osx', 'Welcome to SPIRFBoard!', 'Title')"; 
?>
Inserting that wouldn't work becasue I have an if string that includes the insert syantx.

I do have $username4 from a script before this one and all of my MySQL tables are in place.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Let me put it a little more basic. Open phpMyAdmin, enter the following query into the SQL window...

Code: Select all

#Substitute $username4 for the actual table name you are inserting into
INSERT INTO $username4 (`style`, `texttest`, `title`) VALUES ('osx/osx.css', 'Welcome to SPIRFBoard!', 'Title');
What happens? Does it error, or does it let it ride? Also, in your die(mysql_error() code, add the $username4 var for output to test what table is actually being hit. This may have more to do with your query data than anything else...

Code: Select all

<?php $Result1 = mysql_query($insertSQL, $spirfboard) or die("Could not insert into table $username4 because: " . mysql_error()); ?>
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

I noticed, also, that you assign the table name by way of a form field var...

Code: Select all

<?php $username4 = $_POST['username']; ?>
You are then passing that value to the query as a table name without cleaning it at all. Do you really have a table for every user? Just wondering.
nhwood
Forum Newbie
Posts: 13
Joined: Tue May 23, 2006 2:51 pm

Post by nhwood »

I can do it in phpMyAdmin, let me just play around with the code and try to insert the code you gave me...
nhwood
Forum Newbie
Posts: 13
Joined: Tue May 23, 2006 2:51 pm

Post by nhwood »

Everah wrote:I noticed, also, that you assign the table name by way of a form field var...

Code: Select all

<?php $username4 = $_POST['username']; ?>
You are then passing that value to the query as a table name without cleaning it at all. Do you really have a table for every user? Just wondering.
Yes, This is a script that is apart of some more scripts that creates a user, then creates a table for the user, then this script here is to insert a default set of data for the databse.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Just seems like a lot of overhead. I mean, what if you got 50,000 users? That would be 50,000 tables plus whatever other tables you have for the other parts of your site. Seems odd 8O .
nhwood
Forum Newbie
Posts: 13
Joined: Tue May 23, 2006 2:51 pm

Post by nhwood »

I'll end up with a better system in time, but for now... Anyway, I've decided to ditch my way of inserting data. Do anyone else have a way to connect to a database, and insert data into the tables? Thanks!
Post Reply