Syntax problem inserting using PHP [Archive]

Click to See Complete Forum and Search --> : Syntax problem inserting using PHP

Marcus Maximus

03-16-2007, 10:45 AM

Could not execute query: 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 ''users' (Username, Password, FullName, Address1, Address2, Cit

this is the query
$sql = "INSERT INTO 'users'
(Username, Password, FullName, Address1, Address2, City, County, Country, Email, LastLogged, Limit)
VALUES
('$Username', PASSWORD('$Password'), '$FullName', '$Address1', '$Address2', '$City', '$County', '$Country', '$Email', '$datereg', '$lim')";

These are the names of the columns in my table named users
Username, Password, FullName, Address1, Address2, City, County, Country, Email, LastLogged, Limit

PineSolPirate

03-16-2007, 10:58 AM

Don't quote your table name.

$sql = "INSERT INTO users
(Username, Password, FullName, Address1, Address2, City, County, Country, Email, Las...

Marcus Maximus

03-16-2007, 11:00 AM

didn't work unquoted and Still doesn't work unquoted

Marcus Maximus

03-16-2007, 12:06 PM

Heres all the code

connection
#connect to MySQL
$conn = @mysql_connect("localhost", "#","#")
or die("Could not Connect");
#select the specified database
$rs = @mysql_select_db("#", $conn)
or die("Could not select specified");

inserting the values
<?
$Username = $_POST['Account_Username'];
$Password = $_POST['Account_Password'];
$ConfirmP = $_POST['Account_PasswordConfirm'];
$FullName = $_POST['Contact_FullName'];
$Address1 = $_POST['Contact_StreetAddress'];
$Address2 = $_POST['Contact_Address2'];
$City = $_POST['Contact_City'];
$County = $_POST['Contact_County'];
$Country = $_POST['Contact_Country'];
$Email = $_POST['Contact_Email'];

$self = $_SERVER['PHP_SELF'];
$referer = $_SERVER['HTTP_REFERER'];
$c1 = true;
$c2 = true;
$c3 = true;
$c4 = true;
$c5 = true;
$c6 = true;

$c1 = checkuser($Username);
$c2 = checkpass($Password, $ConfirmP);
$c3 = checkname($FullName);
$c4 = checkemail($Email);
$c5 = checkexists($Username);
$c6 = checkEmailexists($Email);

/*if((!$Username) or (!$Password) or (!$ConfirmP) or (!$FullName) or (!$Email) or ($Password != $ConfirmP))
{
header("Location:$referer");
exit();
}*/

include("connection.php");
$datereg = date("d.m.y");
$lim = 1000;
#create the sql query
$sql = "INSERT INTO users
(Username, Password, FullName, Address1, Address2, City, County, Country, Email, LastLogged, Limit)
VALUES
('$Username', PASSWORD('$Password'), '$FullName', '$Address1', '$Address2', '$City', '$County', '$Country', '$Email', '$datereg', '$lim')";

$sql2 = "CREATE TABLE $Username (blobId int auto_increment not null, blobTitle varchar(50), blobData longblob,
blobType varchar(50), primary key(blobId))";

#echo("Just before test true c1=$c1 c2=$c2 c3=$c3 c4=$c4 c5=$c5");

if($c1 == true && $c2 == true && $c3 == true && $c4 == true && $c5 == true){
#echo("inside test true ");
#echo("$datereg");
#execute the query
$rs = mysql_query($sql, $conn)
or die ("Could not execute query: ".mysql_error());

$rs2 = mysql_query($sql2, $conn)
or die ("Could not execute query: ".mysql_error());

if($rs == true && $rs2 == true) {
#echo("$Username $Password $ConfirmP $FullName $Address1 $Address2 $City " );
#echo("$Province $Country $Email");
#echo("Table was created");
header('Location: login.html');
exit();
}
}
else{
?>
<html><head><title>Registration</title></head>
<body>

<hr>
<?include("register.inc");?>
</body>
</html>
<?}
#################################
function checkuser($Username)
{
if($Username == ""){
echo("Username must be filled in ");
return false;
}
else
return true;
}

function checkexists($Username)
{
$sqll = "select Username from users where Username = '".addslashes($Username)."'";
include("connection.php");
#execute the query
$qrr = mysql_query($sqll, $conn)
or die ("Could not execute query: ".mysql_error());

#get number of rows that get usernames and passwords
$num = mysql_num_rows($qrr);

#if there is a match the login is authenticated
if($num >= 1){
echo("Username: $Username already exists on the database please select another ");
return false;
}
else
return true;
}
function checkpass($Password, $ConfirmP)
{
if($Password == "" || $ConfirmP == ""){
echo("Password and Confirm Password must be filled in ");
return false;
}
else
return true;

if($Password != $ConfirmP){
echo("Password must match Confirm Password ");
return false;
}
else
return true;
}
function checkname($Fullname)
{
if($Fullname == ""){
echo("Fullname must be filled in ");
return false;
}
else
return true;
}
function checkemail($Email)
{
if($Email == ""){
echo("Email must be filled in ");
return false;
}
else
return true;
}
function checkEmailexists($Email)
{
$sqll = "select Email from users where Email = '".addslashes($Email)."'";
include("connection.php");
#execute the query
$qrr = mysql_query($sqll, $conn)
or die ("Could not execute query: ".mysql_error());

#get number of rows that get usernames and passwords
$num = mysql_num_rows($qrr);

#if there is a match the login is authenticated
if($num >= 1){
echo("Email Address: $Email already exists on the database please select another ");
return false;
}
else
return true;
}?>

hlaiken

03-16-2007, 02:52 PM

The table name "users" should definitely NOT be quoted.

I'm a little rusty on my SQL syntax, but you may be having some issues with the SQL PASSWORD() function. First of all, it is my understanding that that function is not necessarily intended for encrypting passwords for web applications. Unless you plan on using the data in the database between different web languages, I would suggest that you encrypt the password through PHP. This brings me to my next suspicion (again it's been a while) that you may not be able to use the PASSWORD() function within the VALUES bracket.

What I typically do when I throw SQL errors is to run the query in QUANTUM DB (an Eclipse IDE plugin) to verify the query syntax. (Or you can try the queries out in myPHPAdmin or something like that...)

Anyway, just some suggestions. When you solve the problem, please post back to let us know what the solution was.

Marcus Maximus

03-16-2007, 03:10 PM

Don't know why but it works when i take out the limit value

On the database it is set as
Limit int(20) NOT NULL

$sql = "INSERT INTO users
(Username, Password, FullName, Address1, Address2, City, County, Country, Email, LastLogged)
VALUES
('$Username', PASSWORD('$Password'), '$FullName', '$Address1', '$Address2', '$City', '$County', '$Country', '$Email', '$datereg')";

Any suggestions why

polorboy

03-16-2007, 03:17 PM

Limit does have a meaning all its own in MySQL. You usually set something like "SELECT * FROM table_name WHERE column_name = 'column_value' LIMIT 1", the LIMIT 1 will only return one row from the table containing the column value you set. It might be getting confused with what you mean by LIMIT and what it means by LIMIT.

Marcus Maximus

03-16-2007, 06:12 PM

cheers i never new limit was a key word in mysql