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
Post
by nhwood » Tue May 23, 2006 2:56 pm
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);
?>
TheMoose
Forum Contributor
Posts: 351 Joined: Tue May 23, 2006 10:42 am
Post
by TheMoose » Tue May 23, 2006 3:00 pm
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 » Tue May 23, 2006 3:06 pm
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?
TheMoose
Forum Contributor
Posts: 351 Joined: Tue May 23, 2006 10:42 am
Post
by TheMoose » Tue May 23, 2006 3:11 pm
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());
RobertGonzalez
Site Administrator
Posts: 14293 Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA
Post
by RobertGonzalez » Tue May 23, 2006 3:17 pm
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 » Tue May 23, 2006 3:18 pm
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);
?>
RobertGonzalez
Site Administrator
Posts: 14293 Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA
Post
by RobertGonzalez » Tue May 23, 2006 3:21 pm
try this and see what it does.
Code: Select all
<?php
$insertSQL = "INSERT INTO $username4 (`style`, `texttest`, `title`) VALUES ('osx', 'Welcome to SPIRFBoard!', 'Title')";
?>
TheMoose
Forum Contributor
Posts: 351 Joined: Tue May 23, 2006 10:42 am
Post
by TheMoose » Tue May 23, 2006 3:23 pm
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 » Tue May 23, 2006 3:27 pm
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.
RobertGonzalez
Site Administrator
Posts: 14293 Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA
Post
by RobertGonzalez » Tue May 23, 2006 3:58 pm
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()); ?>
RobertGonzalez
Site Administrator
Posts: 14293 Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA
Post
by RobertGonzalez » Tue May 23, 2006 4:05 pm
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 » Tue May 23, 2006 4:05 pm
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 » Tue May 23, 2006 4:07 pm
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.
RobertGonzalez
Site Administrator
Posts: 14293 Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA
Post
by RobertGonzalez » Tue May 23, 2006 4:29 pm
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
.
nhwood
Forum Newbie
Posts: 13 Joined: Tue May 23, 2006 2:51 pm
Post
by nhwood » Tue May 23, 2006 4:32 pm
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!