Page 1 of 2

Syntax Error

Posted: Tue May 23, 2006 2:56 pm
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);
?>

Posted: Tue May 23, 2006 3:00 pm
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')",...

Posted: Tue May 23, 2006 3:06 pm
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?

Posted: Tue May 23, 2006 3:11 pm
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());

Posted: Tue May 23, 2006 3:17 pm
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.

Posted: Tue May 23, 2006 3:18 pm
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);
?>

Posted: Tue May 23, 2006 3:21 pm
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')"; 
?>

Posted: Tue May 23, 2006 3:23 pm
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).

Posted: Tue May 23, 2006 3:27 pm
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.

Posted: Tue May 23, 2006 3:58 pm
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()); ?>

Posted: Tue May 23, 2006 4:05 pm
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.

Posted: Tue May 23, 2006 4:05 pm
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...

Posted: Tue May 23, 2006 4:07 pm
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.

Posted: Tue May 23, 2006 4:29 pm
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 .

Posted: Tue May 23, 2006 4:32 pm
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!