MYSQL Issue or possibly php

ace2109 · 22 Jun 2009 at 21:54

Hi guys,
Im trying to create a form that when filled out will post all its information to a mysql database.

Everytime i try to fill in the form to test it, it keeps throwing back this error message:

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 'a')' at line 1

I cant seem to understand it, i think i have entered all the correct information but i am not sure.

Here is the code from the page where the form sits. (Named - waiting_list.php)

<td width="653"><FORM ACTION="waiting_list_return.php" METHOD="POST" onSubmit="submitonce(this)" NAME="contact_form">
<TABLE align="center">
<TR>
<TD width="190" class="body_text_black"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">First Name</font></TD>
<TD width="242" class="body_text_black"> <input type=text name="fname" onkeypress="return handleEnter(this, event)"></TD>
</TR>
<TR>
<TD width="190" class="body_text_black"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Last Name:</font></TD>
<TD width="242" class="body_text_black"> <input type=text name="lname" onkeypress="return handleEnter(this, event)"></TD>
</TR>
<TR>
<TD width="190" class="body_text_black"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Address:</font></TD>
<TD width="242" class="body_text_black"> <input type=text name="address" onkeypress="return handleEnter(this, event)"></TD>
</TR>
<TR>
<TD width="190" class="body_text_black"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Postcode:</font></TD>
<TD width="242" class="body_text_black"> <input type=text name="postcode" onkeypress="return handleEnter(this, event)"></TD>
</TR>
<TR>
<TD width="190" class="body_text_black"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Date of Birth:</font></TD>
<TD width="242" class="body_text_black"> <input type=text name="dob" onkeypress="return handleEnter(this, event)"></TD>
</TR>
<TR>
<TD width="190" class="body_text_black"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Email Address:</font></TD>
<TD width="242" class="body_text_black"> <input type=text name="email" onkeypress="return handleEnter(this, event)"></TD>
</TR>
<TR>
<TD width="190" height="136" class="body_text_black"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Additional Information:</font></TD>
<TD width="242" class="body_text_black"> <textarea name="addinfo" rows="8" onkeypress="return handleEnter(this, event)"></textarea>
<a href="#" class="hintanchor" onMouseover="showhint('If you have any additional information you would like us to know please fill it in here.', this, event, '150px')">[?]</a></TD>
</TR>
<TR>
<TD width="190" class="body_text_black"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Official Membership Number:</font></TD>
<TD width="242" class="body_text_black"> <input type=text name="mnumber" onkeypress="return handleEnter(this, event)"><a href="#" class="hintanchor" onMouseover="showhint('This number can be found on your official Liverpool FC Membership card. This DOES NOT influence your eligibility when joing PLFSC.', this, event, '150px')">[?]</a></TD>
</TR>
<TD colspan="2" class="body_text_black"><div align="center">
<input type="submit" value="Submit" name="Submit">
<input type="reset" value="Reset" name="Reset">
</div></TD>
</TR>
</TABLE>
</FORM> </td>

and then this is the actual php code (Named waiting_list_return.php)

<?php
$con = mysql_connect("mysql15.streamline.net","qqqqqqqqq","qqqqqqqqqqq"); //Replace with your actual MySQL DB Username and Password
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("plfsccou2", $con); //Replace with your MySQL DB Name
$fname=mysql_real_escape_string($_POST['fname']); //This value has to be the same as in the HTML form file
$lname=mysql_real_escape_string($_POST['lname']); //This value has to be the same as in the HTML form file
$address=mysql_real_escape_string($_POST['address']); //This value has to be the same as in the HTML form file
$postcode=mysql_real_escape_string($_POST['postcode']); //This value has to be the same as in the HTML form file
$dob=mysql_real_escape_string($_POST['dob']); //This value has to be the same as in the HTML form file
$email=mysql_real_escape_string($_POST['email']); //This value has to be the same as in the HTML form file
$addinfo=mysql_real_escape_string($_POST['addinfo']); //This value has to be the same as in the HTML form file
$mnumber=mysql_real_escape_string($_POST['mnumber']); //This value has to be the same as in the HTML form file

$sql="INSERT INTO wlist (fname,lname,address,postcode,dob,email,addinfo,mnumber) VALUES ('$fname','$lname','$address','$postcode','$dob','email','$addinfo','$lname'$mnumber')"; /*form_data is the name of the MySQL table where the form data will be saved.
name and email are the respective table fields*/
if (!mysql_query($sql,$con)) {
die('Error: ' . mysql_error());
}
echo "The form data was successfully added to your database.";
mysql_close($con);
?>
<!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">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
</head>

<body>
</body>
</html>

any ideas???

PMWD · 22 Jun 2009 at 21:59

I think it's because you've a comma missing between mn umber and '$lname'$mnumber' in your query. Are they two columns and two pieces of data?

Also don't post your database username and password if they are your actual details.

ace2109 · 22 Jun 2009 at 22:06

just realised i had posted that, they are 2 separate pieces of data. i will give it a go now

ace2109 · 22 Jun 2009 at 22:15

I am now getting this error

Error: Column count doesn't match value count at row 1

adren · 22 Jun 2009 at 22:33

ace2109 said:
I am now getting this error

Error: Column count doesn't match value count at row 1

If you put a comma in to fix the first error then you have 9 pieces of data going into 8 columns? I think...

ace2109 · 22 Jun 2009 at 22:39

Should have seen that, i should have only had 8 pieces of information being collected thanks for your help

ace2109 · 23 Jun 2009 at 18:50

new problem guys,
Got everything working, my form now sends the information i want to my database, but in the email field on my database, instead of showing the email address it just shows the word email????

any ideas?

Oh and one more thing guys, I only want a person to be able to sign up the my waiting list once, the best way i can think of doing this is to verify that their email has not been registered before when they hit the submit button but how would i go about doing this?

whitecrook · 23 Jun 2009 at 19:24

Look at your code! You are missing a ' before email...

Just perform a SELECT on there email anddress, if mysql_numrows is 0 then the email isn't here, if it's 1 then it's already there, if it's greater then 1 then someone has messed up somewhere ..

ace2109 · 23 Jun 2009 at 20:18

whitecrook said:
Look at your code! You are missing a ' before email...

Just perform a SELECT on there email anddress, if mysql_numrows is 0 then the email isn't here, if it's 1 then it's already there, if it's greater then 1 then someone has messed up somewhere ..

Forgive me guys but i am a complete newbie at this. Will this perform the email check for me?

mysql_connect($localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$dataemail = mysql_real_escape_string($email);
$query1 = "SELECT * FROM registration WHERE email ='$dataemail'";
$result = mysql_query ($query1);
$num_rows = mysql_num_rows($result);

if($num_rows > '0'){
$indata = "true";
}
elseif($num_rows == '0'){

$indata = "false";

}

if ($indata == "false")
{

mysql_connect($localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query = "INSERT INTO registration VALUES ('','$firstname','$lastname','$email','$xtreme','$age','$gender','$area')";
mysql_query($query);
mysql_close();
}

PMWD · 23 Jun 2009 at 20:26

You're actually missing a $ before the email, so instead of entering the variable value you're just entering 'email'.

Replace with

PHP:

$sql = "INSERT INTO wlist " .
"(fname,lname,address,postcode,dob,email,addinfo,mnumber) " .
"VALUES " .
"('$fname','$lname','$address','$postcode','$dob','$email','$addinfo','$mnumber')";

whitecrook · 23 Jun 2009 at 20:26

at a quick glance, the logic certainly looks right. Code should probaly work as well.

Depending on the data stored, you could get fancy and make the query return the reg date as well as the email , for example, and then if the email is found, say "this email address was registered on $date, from IP $ip , use another email" or whatever.

ace2109 · 23 Jun 2009 at 21:34

ok, is there a particular place i should place this within my original code

ace2109 · 23 Jun 2009 at 22:25

This line is returning errors

$num_rows = mysql_num_rows($result);

The error is

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in E:\domains\p\plfsc.co.uk\user\htdocs\waiting_list_return.php on line 30

whitecrook · 24 Jun 2009 at 00:03

post your code in [php ] [/php] tags

suarve · 24 Jun 2009 at 09:24

ace2109 said:
This line is returning errors

$num_rows = mysql_num_rows($result);

The error is

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in E:\domains\p\plfsc.co.uk\user\htdocs\waiting_list_return.php on line 30

Would guess that's because your sql query isn't returning anything. Tryu outputting your sql query and running it directly in something like phpmyadmin.

LiE · 24 Jun 2009 at 12:10

On a site note - use CSS

ace2109 · 24 Jun 2009 at 23:19

suarve said:
Would guess that's because your sql query isn't returning anything. Tryu outputting your sql query and running it directly in something like phpmyadmin.

how would i do this, remember i am a newbie to mysql and php

my full code looks like this

PHP:

<?php
$con = mysql_connect("mysql15.streamline.net","***********","**********"); //Replace with your actual MySQL DB Username and Password
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("***********", $con); //Replace with your MySQL DB Name
$fname=mysql_real_escape_string($_POST['fname']); //This value has to be the same as in the HTML form file
$lname=mysql_real_escape_string($_POST['lname']); //This value has to be the same as in the HTML form file
$address=mysql_real_escape_string($_POST['address']); //This value has to be the same as in the HTML form file
$postcode=mysql_real_escape_string($_POST['postcode']); //This value has to be the same as in the HTML form file
$dob=mysql_real_escape_string($_POST['dob']); //This value has to be the same as in the HTML form file
$email=mysql_real_escape_string($_POST['email']); //This value has to be the same as in the HTML form file
$addinfo=mysql_real_escape_string($_POST['addinfo']); //This value has to be the same as in the HTML form file
$mnumber=mysql_real_escape_string($_POST['mnumber']); //This value has to be the same as in the HTML form file

$sql="INSERT INTO wlist (fname,lname,address,postcode,dob,email,addinfo,mnumber) VALUES ('$fname','$lname','$address','$postcode','$dob','$email','$addinfo','$mnumber')"; /*form_data is the name of the MySQL table where the form data will be saved.
name and email are the respective table fields*/
if (!mysql_query($sql,$con)) {
 die('Error: ' . mysql_error());
}
echo "The form data was successfully added to your database.";
mysql_close($con);

mysql_connect("******************","*********","***********");
@mysql_select_db("plfsccou2") or die( "Unable to select database"); //enter database name
$dataemail = mysql_real_escape_string($email);
$query1 = "SELECT * FROM registration WHERE email ='$email'";
$result = mysql_query ($query1); 
$num_rows = mysql_num_rows($result);

if($num_rows > '0'){
$indata = "true";
}
elseif($num_rows == '0'){

$indata = "false";

}


if ($indata == "false")
{

mysql_connect("**************","*********","***********");
@mysql_select_db("***********") or die( "Unable to select database");
$query = "INSERT INTO registration VALUES ('$fname','$lname','$address','$postcode','$dob','$email','$addinfo','$mnumber')";
mysql_query($query);
mysql_close();
}
?>

All im looking this form to do is when a user hits the submit button, check the mysql database for the same email address. If the same email address exists then return a message saying this email address is already registered, if it has not been registered then just continue and post it to the database as normal.

thanks for your help on this one guys

PMWD · 25 Jun 2009 at 00:50

Ok first off stop dropping in connections all over the place. Create a file called db.php and structure it as follows:

PHP:

<?php
// Set the connection details
$hostname = "hostname";
$user = "username";
$password = "password";
$database = "database";
// Connect to the host using the above details
$connection = mysql_connect($hostname, $user, $password) or die("Error could not connect to the host.");
// Connect to the database using the above connection
mysql_select_db($database, $connection) or die("Error could not connect to the database.");
?>

making sure you include your connection details, that way you can reference it at the top of each page that requires a database connection. If you need to change the database name or change your password at any time, you're just updating one file rather than having to work your way through the whole of your site.

Your registration page that the form submits to should then look something like this:

PHP:

<?php
// Connect to the database
require_once "db.php";

// Check to ensure the form was submitted to this page
if(isset($_POST['fname']))
{
	$fname = mysql_real_escape_string($_POST['fname']);
	$lname = mysql_real_escape_string($_POST['lname']);
	$address = mysql_real_escape_string($_POST['address']);
	$postcode = mysql_real_escape_string($_POST['postcode']);
	$dob = mysql_real_escape_string($_POST['dob']);
	$email = mysql_real_escape_string($_POST['email']);
	$addinfo = mysql_real_escape_string($_POST['addinfo']);
	$mnumber = mysql_real_escape_string($_POST['mnumber']);
	
	// Query the database to see if the email is already in the records
	$query = "SELECT * FROM wlist WHERE email='$email'";
	$result = mysql_query($query) or die(mysql_error());
	$nor = mysql_num_rows($result);
	
	if($nor == 0)
	{
		// There is no record of the email in the database, so add the details
		$query = "INSERT INTO wlist " .
		"(fname,lname,address,postcode,dob,email,addinfo,mnumber) " .
		"VALUES " .
		"('$fname','$lname','$address','$postcode','$dob','$email','$addinfo','$mnumber')";
		$result = mysql_query($query) or die(mysql_error());
		$message = "Thank you. Your details have been added to our database.";
	}// if($nor == 0)
	else
	{
		// The email is already in the database
		$message = "Thank you. Your details are already in our database.";
	}// else to if($nor == 0)	
	
}// if(isset($_POST['fname']))
else
{
	// The form was not submitted to this page
	$message = "Please complete the registration form first";
}// else to if(isset($_POST['fname']))

// Optional: Close the connection to the database as it is no longer required
mysql_close($connection);

echo "<?xml version=\"1.0\" encoding=\"iso-8859-1\"?".">"; ?>
<!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">
<head>
<title>Registration</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
</head>

<body>
	<p><?php echo $message; ?></p>
</body>
</html>

Don't worry, you'll get the hang of it.

ace2109 · 25 Jun 2009 at 18:29

PMWD thats great it works an absolute treat mate cheers for that.

Ace