[RESOLVED] Inserting form data in MySQL with PHP

cisforcasey

Hi,

I've very new to php. I'm trying to develop a webpage that takes form data and submits it via php to a mysql database. I've read a number of tutorials and I just can't get it to work. Instead of printing a success message, my page just reloads. Here is my code:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<?php
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
// CONNECTION VARIABLES
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
// the hostname of your MYSQL server, typically localhost
$hostname = "localhost";
// the username to access your MYSQL database
$username = "user";
// the password to access your MYSQL database
$password = "pass";
// the name of the MYSQL database
$database = "rainbarrel";

// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
// CONNECTION
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
// $Connection variable is used to hold the
// connection function, and connection string.
// the variable also contains the error handing
// on the ‘or die’ syntax
$connection=mysql_connect ($hostname, $username, $password);
if (!$connection) {  die('Not connected : ' . mysql_error());}   

//Additionally, we select the database
// which we are going to be using.
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
  die ('Can\'t use db : ' . mysql_error());
} 
?>

<html>
<head>
	<title>Rain Barrel Workshop Sign-Up</title>
</head>

<body>
<?php
if (!isset($_Post['submit'])){
?>
<form Name="SignUp" Method="POST" action="">
<table>
<strong>Rain Barrel Workshop Sign-Up Form</strong><br>
Please fill in all of the below fields to sign up for 
the next rain barrel workshop (fields marked with an asterisks are optional).<br><br>

<tr><td>First Name:</td><td><input type="text" name="fName" size="30"></td></tr>

<tr><td>Last Name:</td><td><input type="text" name="lName" size="30"></td></tr>

<tr><td>Street Address:</td><td><input type="text" name="address" size="50"></td></tr>

<tr><td>Zip Code:</td><td><input type="text" name="zipCode" size="5" maxlength="5"></td></tr>

<tr><td>Watershed:</td><td>watershed result</td></tr>

<tr><td>Organization*:</td><td><input type="text" name="org" size="50"></td></tr>

<tr><td>Phone (ex. 2151234567):</td><td><input type="text" name="phone" size="10" maxlength="10"></td></tr>

<tr><td>E-mail*:</td><td><input type="text" name="email" size="25"></td></tr>

<tr><td>Type of building the barrel will be installed at:</td>
<td><select name="bType">
<option value="Detached">Detached</option><option value="SemiDetached">Semi-Detached</option>
<option value="Row">Row</option><option value="Apt">Apartment</option><option value="other">Other</option></td></tr>

<tr><td>The approximate number of downspouts this building has:</td><td><input type="text" name="downspout" size="25"></td></tr>

<tr><td>What do you intend to use the collected rain water for?</td><td><textarea name="use" rows="5" cols="40"></textarea></td></tr>

<tr><td>Where did you hear about this workshop from?</td><td><textarea name="refer" rows="5" cols="40"></textarea></td></tr>

<tr><td>Do we have permission to follow-up with you <br> regarding the installation and use of the rain barrel?</td>
<td><select name="rights">
<option value="Y">Yes</option><option value="N">No</option></td></tr>

</table>
<br>
<input type="submit" name="submit" value="Submit Form">
</form>

<?php
}else{

$fName = $_POST[‘fName’];
$lName = $_POST[‘lName’];
$address = $_POST[‘address’];
$zipCode = $_POST[‘zipCode’]; 
$watershed = $_POST[‘watershed’]; 
$lat = $_POST[‘lat’]; 
$long = $_POST[‘long’]; 
$phone = $_POST[‘phone’]; 
$email = $_POST[‘email’]; 
$bType = $_POST[‘bType’]; 
$downspout = $_POST[‘downspout’]; 
$use = $_POST[‘use’];
$refer= $_POST[‘refer’]; 
$rights = $_POST[‘rights’]; 
$org = $_POST[‘org’]; 

$strSQL = "INSERT INTO 'signup_list' (fname,lname,address,zipcode,watershed,lat,long,phone,email,btype,downspout,use,refer,rights,org) Values ('" . $fName . "','" . $lName . "','" . $address . "', '" . $zipCode . "', '" . $watershed . "', '" . $lat . "', '" . $long . "', '" . $phone . "', '" . $email . "', '" . $btype . "', '" . $downspout . "', '" . $use . "', '" . $refer . "', '" . $rights . "', '" . $org . "')" ;  

$strResult = mysql_query($strSQL) or die(mysql_errno() . " : " . mysql_error() . "  <strong>Insert Failed</strong>");  

echo "You have been signed up! Thanks!";
}
?>
</body>
</html>

Thanks for your help!

msimonds

do you have a schema of the table I could look at?

cisforcasey

here is the schema for the table I am trying to insert data into

Field Type

ID int(11)
fname varchar(20)
lname varchar(30)
address varchar(80)
zipcode varchar(5)
watershed char(2)
lat float(10,6)
long float(10,6)
phone varchar(10)
email varchar(30)
btype varchar(13)
downspout int(11)
use text
refer text
rights char(3)
org varchar(100)

thanks for your help.

cisforcasey

I've got to the point where I can at least get an error message, which is this:

Invalid 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 'long,phone,email,btype,downspout,use,refer,rights,org) Values ('','','','','',' at line 1

I've checked the statement again and again, (based off these tutorials: http://www.w3schools.com/PHP/php_mysql_insert.asp, http://www.php-mysql-tutorial.com/mysql-insert-php.php) and I can't see what I'm doing wrong.

Here is my latest SQL query:

$SQLquery = "INSERT INTO signup_list (fname,lname,address,zipcode,watershed,lat,long,phone,email,btype,downspout,use,refer,rights,org) 
Values 
('$_POST[‘fName’]','$_POST[‘lName’]','$_POST[‘address’]','$_POST[‘zipCode’]','$_POST[‘watershed’]','$_POST[‘lat’]','$_POST[‘long’]','$_POST[‘phone’]','$_POST[‘email’]','$_POST[‘bType’]','$_POST[‘downspout’]','$_POST[‘use’]','$_POST[‘refer’]','$_POST[‘rights’]','$_POST[‘org’]')" ;

thanks again

msimonds

so do this look correct?

CREATE TABLE test (
ID int(11) default NULL,
fname varchar(20) collate latin1_general_ci default NULL,
lname varchar(30) collate latin1_general_ci default NULL,
address varchar(80) collate latin1_general_ci default NULL,
zipcode varchar(5) collate latin1_general_ci default NULL,
watershed char(2) collate latin1_general_ci default NULL,
lat float(10,6) default NULL,
long float(10,6) default NULL,
phone varchar(10) collate latin1_general_ci default NULL,
email varchar(30) collate latin1_general_ci default NULL,
btype varchar(13) collate latin1_general_ci default NULL,
downspout int(11) default NULL,
use text collate latin1_general_ci,
refer text collate latin1_general_ci,
rights char(3) collate latin1_general_ci default NULL,
org varchar(100) collate latin1_general_ci default NULL,
KEY ID (ID)
)

cisforcasey

yes that looks correct. thanks

msimonds

You had a whole mess of things wrong with your script and MySQL table, but that is fine, we all learn and I hope this helps you out.

Seems to me that two of your fields are defined with RESERVED words > LONG & USE

I had to rename those:

Here is a schema that I modified:

CREATE TABLE `test` (
  `ID` int(11) NOT NULL auto_increment,
  `fname` varchar(20) collate latin1_general_ci default NULL,
  `lname` varchar(30) collate latin1_general_ci default NULL,
  `address` varchar(80) collate latin1_general_ci default NULL,
  `zipcode` varchar(5) collate latin1_general_ci default NULL,
  `watershed` char(2) collate latin1_general_ci default NULL,
  `lat` float(10,6) default NULL,
  `longg` float(10,6) default NULL,
  `phone` varchar(10) collate latin1_general_ci default NULL,
  `email` varchar(30) collate latin1_general_ci default NULL,
  `btype` varchar(13) collate latin1_general_ci default NULL,
  `downspout` int(11) default NULL,
  `usee` text collate latin1_general_ci,
  `refer` text collate latin1_general_ci,
  `rights` char(3) collate latin1_general_ci default NULL,
  `org` varchar(100) collate latin1_general_ci default NULL,
  KEY `ID` (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=2 ;

and I had to work on the script, take a look at this one:


<?php
error_reporting(E_ERROR | E_WARNING | E_PARSE);
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
// CONNECTION VARIABLES
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
// the hostname of your MYSQL server, typically localhost
$hostname = "localhost";
// the username to access your MYSQL database
$username = "user";
// the password to access your MYSQL database
$password = "pass";
// the name of the MYSQL database
$database = "test";

//local variables from $_POST array
$fName = $_POST['fName'];
$lName = $_POST['lName'];
$address = $_POST['address'];
$zipCode = $_POST['zipCode'];
$watershed = $_POST['watershed'];
$lat = $_POST['lat'];
$long = $_POST['long'];
$phone = $_POST['phone'];
$email = $_POST['email'];
$bType = $_POST['bType'];
$downspout = $_POST['downspout'];
$use = $_POST['use'];
$refer = $_POST['refer'];
$rights = $_POST['rights'];
$org = $_POST['org'];

if (isset($_POST['submit']))
{
    $connection = mysql_connect($hostname,$username,$password);
    if (!$connection)
    {
        die('Not connected : '.mysql_error());
    }
    //Additionally, we select the database
    // which we are going to be using.
    $db_selected = mysql_select_db($database,$connection);
    if (!$db_selected)
    {
        die('Can\'t use db : '.mysql_error());
    }


$strSQL = "INSERT INTO test (ID, fname,lname,address,zipcode,watershed,lat,longg,phone,email,btype,downspout,usee,refer,rights,org) 
				     VALUES (0,'$fName','$lName','$address', '$zipCode', '$watershed', '$lat', '$long', '$phone', '$email', '$btype', '$downspout', '$use', '$refer', '$rights', '$org')";


$strResult = mysql_query($strSQL) or die(mysql_errno()." : ".mysql_error()."  <strong>Insert Failed</strong>");
echo "You have been signed up! Thanks!";
exit;
}
?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
	<title>Rain Barrel Workshop Sign-Up</title>
</head>

<body>

<form Name="SignUp" Method="POST" action="<?= $_SERVER['PHP_SELF'] ?>">
<table>
<strong>Rain Barrel Workshop Sign-Up Form</strong><br>
Please fill in all of the below fields to sign up for 
the next rain barrel workshop (fields marked with an asterisks are optional).<br><br>

<tr><td>First Name:</td><td><input type="text" name="fName" size="30"></td></tr>

<tr><td>Last Name:</td><td><input type="text" name="lName" size="30"></td></tr>

<tr><td>Street Address:</td><td><input type="text" name="address" size="50"></td></tr>

<tr><td>Zip Code:</td><td><input type="text" name="zipCode" size="5" maxlength="5"></td></tr>

<tr><td>Watershed:</td><td>watershed result</td></tr>

<tr><td>Organization*:</td><td><input type="text" name="org" size="50"></td></tr>

<tr><td>Phone (ex. 2151234567):</td><td><input type="text" name="phone" size="10" maxlength="10"></td></tr>

<tr><td>E-mail*:</td><td><input type="text" name="email" size="25"></td></tr>

<tr><td>Type of building the barrel will be installed at:</td>
<td><select name="bType">
<option value="Detached">Detached</option><option value="SemiDetached">Semi-Detached</option>
<option value="Row">Row</option><option value="Apt">Apartment</option><option value="other">Other</option></td></tr>

<tr><td>The approximate number of downspouts this building has:</td><td><input type="text" name="downspout" size="25"></td></tr>

<tr><td>What do you intend to use the collected rain water for?</td><td><textarea name="use" rows="5" cols="40"></textarea></td></tr>

<tr><td>Where did you hear about this workshop from?</td><td><textarea name="refer" rows="5" cols="40"></textarea></td></tr>

<tr><td>Do we have permission to follow-up with you <br> regarding the installation and use of the rain barrel?</td>
<td><select name="rights">
<option value="Y">Yes</option><option value="N">No</option></td></tr>

</table>
<br>
<input type="submit" name="submit" value="Submit Form">
</form>







</body>
</html>

Test this and tell me if it works, I will be glad to help you more if it does not.

Now you should also read into cleaning up your data when you are inserting it into a DB, but I will leave that part for you

Hope this helps

~Mike

cisforcasey

awesome! it works! thanks so much for the help. it seems I made a lot of stupid errors.

Casey

msimonds

I am glad that it works and don't get too bumbed about the small mistakes that you did, we were all there.

Shoot I still make mistakes all the time and ask questions

That is what this is about > getting help

Anyway I am glad

~Mike

Xager

I've found that a really good thing to do when you get mysql errors is to output the query:

echo("INSERT INTO 'signup_list' (fname,lname,address,zipcode,watershed,lat,long,phone,email,btype,downspout,use,refer,rights,org) Values ('" . $fName . "','" . $lName . "','" . $address . "', '" . $zipCode . "', '" . $watershed . "', '" . $lat . "', '" . $long . "', '" . $phone . "', '" . $email . "', '" . $btype . "', '" . $downspout . "', '" . $use . "', '" . $refer . "', '" . $rights . "', '" . $org . "')");