Inserting data from a web form to MySQL Database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
implications
Forum Commoner
Posts: 25
Joined: Thu Apr 07, 2011 3:59 am

Inserting data from a web form to MySQL Database

Post by implications »

I've created this web form where the data submitted is inserted into a MySQL, but I can't seem to get the data to be inserted even though I've double checked my code. I don't know what the problem is, because everything looks alright to me.

The MySQL config and connection is in a config.php file like so:

Code: Select all

	$db_name = "name";
	$db_user = "user";
	$db_password = "pass";
	$db_table = "table";
	$db_hostname = "host";

$connect = mysql_connect("$db_hostname", "$db_user", "$db_password");
mysql_select_db("$db_name", $connect);
require_once('functions.php');
That bit of code should work because I was able to connect to the MySQL database and create a table.

The submit form code looks like this:

Code: Select all

<?php

if (isset($_POST['submit'])) {
	$question = clean($_POST['question']);
	$dateasked = NOW();
	$ip = clean($_SERVER['REMOTE_ADDR']);
	
if ($question == '') {
	$error = 'Please enter a question.';
	
} else if ($question != '') {
	$insert = "INSERT INTO `".$db_table."` (`question`, `answer`, `dateasked`, `ip`) VALUES ('$question', '$answer', '$dateasked', '$ip')";
	if (mysql_query($insert, $connect)) {
	        echo '<p>Data successfully inserted.</p>';
	    } else {
	        echo '<p>There was an error while trying to insert the data into the database.</p>';
	        die("Error:". mysql_error());
	    }
}
}
I tried submitting something through the web form and no error showed up. After I press submit, a blank page shows up. I checked the database in phpmyadmin and no data was inserted into the table. How do I get this to work?
User avatar
GimbaL
Forum Newbie
Posts: 18
Joined: Thu Apr 16, 2009 3:28 am

Re: Inserting data from a web form to MySQL Database

Post by GimbaL »

Are you sure $_POST['submit'] is actually set? If not, your code will output what you describe, a blank page (no errors no nothing).

Also this code is VERY dangerous:
implications wrote:

Code: Select all

$insert = "INSERT INTO `".$db_table."` (`question`, `answer`, `dateasked`, `ip`) VALUES ('$question', '$answer', '$dateasked', '$ip')";
You should never assume $_POST variables are safe to use like this!!
See golden security rules about user input.
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: Inserting data from a web form to MySQL Database

Post by twinedev »

Here is a sample shell I use for most basic forms. Some of this code I do a little different, but have it broken out here for easier reading/learning of how it works.

Some notes on this:

This has two items to help prevent bot spam submissions:
1. Uses a honeypot which is needed to be left blank for submit to work
2. Sets a timestamp on the form, and then when submitted makes sure it was submitted in a timely fashion. (time is set at top of file)

Please note, I do not normally directly use the actual time() value. Anyone wanting to write a bot to spam ya, will most likely figure that out. I convert this number over to a 32 char hash (not a true hash like md5) in a way it will not easily be figures out

Also, there is a <div> that will contain any errors from the server side validation. This actual tag is not part of the IF statement, so that the div always exists so if you have JS validation it can use this div to display client side validation.

All of the HTML code is stripped down for simplicity here, but it does work.

Code: Select all

<?php

	require_once('file_that_connects_to_database.php');

	define ('MINUTES_TO_SUBMIT',60); // HOW MANY MINUTES THEY HAVE TO SUBMIT THE FORM (prevent Curl calls)

	$aryErr = array(); // Will hold error messages

	// Note, I use a honeypot to help prevent bot spam see end of <form> code
	if (count($_POST)>0 && isset($_POST['hidPostHash']) && isset($_POST['URL']) && $_POST['URL']=='') { // Form submitted

		// Clear any fiels with just whitespace back to being blank for validation
		foreach($_POST as $key=>$val) { if (is_string($val)) ($_POST[$key]=trim($val)); }

		// BEGIN: Validation

			// Required fields

			if (!isset($_POST['txtFirstName']) || strlen($_POST['txtFirstName'])<3) {
				$aryErr['FirstName'] = 'First Name must be at least 3 characters';
			}
			if (!isset($_POST['txtLastName']) || strlen($_POST['txtLastName'])<3) {
				$aryErr['LastName'] = 'First Name must be at least 3 characters';
			}
			if (!isset($_POST['drpState']) || (int)$_POST['drpState']==0) {
				$aryErr['State'] = 'You must select a state';
			}

			// Optional Fields that require certain formats (dates/phone numbers)

			if (isset($_POST['txtDOB']) && $_POST['txtDOB']!='' && !preg_match('%^\d?\d[-/]\d?\d[-/]\d\d(\d\d)?$%',$_POST['txtDOB'])) {
				$aryErr['DOB'] = 'Date of birth must be left blank or be in MM-DD-YYYY format';
			}
			else {
				// Note, other checking should be done to make sure a valid date at least 13 years ago
			}


			// Make sure they submitted it in a timely fashion, (ie not a programmed bot) if no other errors

			if (count($aryErr) == 0) {
				$tsOriginalForm = (int)$_POST['hidPostHash']; // SEE MOTES IN MY POST

				if ($tsOriginalForm + (MINUTES_TO_SUBMIT*60) < time()) {
					$aryErr['FORM'] = "You took too long submitting this form, try again";
				}
			}

		// END: Validation

		if (count($aryErr)==0) { // All submited data is good

			// This block is based upon that all fields that should go to DB are prefixed with either:
			//    txt = text data
			//    drp = numeric data from a dropdown/option list which will be a foreign key to other table
			//    If you have others, just keep adding elseif's to handle those
			//  You have a field called tsCreated which is a DATETIME format for when record was added
			//  You have a field called ipCreated which is varchar(15) for the IP that submitted the info

			$SQL1 = 'INSERT INTO `tblName` (';
			$SQL2 = ') VALUES (';
			foreach ($_POST as $key=>$val) {
				$strPrefix = substr($key,0,3); // used more than once, store it
				if (strlen($key)>4) {
					if ($strPrefix=='txt') {
						$SQL1 .= '`'.substr($key,3).'`,';
						$SQL2 .= "'".addslashes($val)."',";
					}
					elseif ($strPrefix=='drp') {
						$SQL1 .= '`'.substr($key,3).'`,';
						$SQL2 .= (int)$val.",";  // (int) forces to be an interger value, so no need for quotes
					}
				}
			}
			// Note for the following both $SQL1 and $SQL2 are already ending in a comma for next field/value
			$SQL = $SQL1 . '`tsCreated`,`ipCreated`' . $SQL2 . "NOW(),'".$_SERVER['REMOTE_ADDR']."')";

			// This is basic for purpose of sample code...
			mysql_query($SQL) or die("Error Saving Data...");

			// I do a redirect to the thank you page so that can easier track that it was properly submitted in statistics
			header('location:/thank-you-page.php');
			exit;

		}

	}
	else {
		// The form was not submitted, for ALL drpWhatever fields, define their defaults.
		// The function below handles txt fields, so no need to prefine them unless you want them filled

		$_POST['drpState'] = 35; // For me, auto selects Ohio
	}

	// Note, need to load up an array of states here... either from Database or an include...
	$aryStates = array(35=>'Ohio',36=>'Oklahoma',37=>'Oregon',38=>'Pennsylvania');

	$strPostHosh = time(); // SEE NOTES IN MY POST

	function echo_value($key) {
		if (isset($_POST['txt'.$key])) {
			echo htmlspecialchars($_POST['txt'.$key]);
		}
	}

?>
<html>
<head>
	<title>My Site - Give me some data!</title>
</head>
<body>
	<h1>Submit me some data!</h1>
	<p>Please use the following form to sign up/suggest something/contact us... whatever...</p>
	<div id='form-error'>
		<?php if (count($aryErr)>0): ?>
			<p>The following errors were found:</p>
			<?php echo '<ul><li>',implode('</li><li>',$aryErr),'</li></ul>'; ?>
		<?php endif; ?>
	</div>
	<form method="post" action="#">
		<p>All fields marked with a * are required.</p>

		<label for="txtFirstName">First Name *</label>
		<input name="txtFirstName" id="txtFirstName" value="<?php echo_value('FirstName'); ?>" />
		<br />
		<label for="txtLastName">First Name *</label>
		<input name="txtLastName" id="txtLastName" value="<?php echo_value('LastName'); ?>" />
		<br />
		<label for="txtDOB">Date of Birth <em>(MM-DD-YYYY)</em></label>
		<input name="txtDOB" id="txtDOB" value="<?php echo_value('DOB'); ?>" />
		<br />
		<label for="drpState">State *</label>
		<select name="drpState" id="drpState">
			<option value="0" style="font-style: italic">--Select One--</option>
			<?php
				foreach($aryStates as $key=>$val) {
					if ($key == $_POST['drpState']) {
						echo '<option value="',$key,'" selected="selected">',htmlspecialchars($val),"</option>\n";
					}
					else {
						echo '<option value="',$key,'">',htmlspecialchars($val),"</option>\n";
					}
				}
			?>
		</select>
		<br />
		<label for="txtComments">Comments</label>
		<textarea cols="50" rows="4" name="txtComments" id="txtComments"><?php echo_value('Comments'); ?></textarea>

		<!-- SEE NOTES IN POST -->
		<div style="margin-left: -8872px; height: 10px;">This field must be left blank <input type="text" name="URL" value="" /></div>

		<input type="hidden" name="hidPostHash" value="<?php echo $strPostHosh; ?>" />
		<input type="submit" name="submit" value="Save Data" />
	</form>
</body>
</html>
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Inserting data from a web form to MySQL Database

Post by social_experiment »

Code: Select all

$insert = "INSERT INTO `".$db_table."` (`question`, `answer`, `dateasked`, `ip`) VALUES ('$question', '$answer', '$dateasked', '$ip')";
$sql = mysql_query($insert);

if ($sql) 
{
 // data written to database
}
else 
{
 // data not written database, issue error
}
I find it easier to assign the mysql_query() function to a variable and check that variable. As the other posters mention, you should look into validating / escaping any input received from your form. Look at mysql_real_escape_string() for escaping input.

On the form that processes the input, do you include config.php?
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
implications
Forum Commoner
Posts: 25
Joined: Thu Apr 07, 2011 3:59 am

Re: Inserting data from a web form to MySQL Database

Post by implications »

twinedev wrote:Here is a sample shell I use for most basic forms. Some of this code I do a little different, but have it broken out here for easier reading/learning of how it works.

Some notes on this:

This has two items to help prevent bot spam submissions:
1. Uses a honeypot which is needed to be left blank for submit to work
2. Sets a timestamp on the form, and then when submitted makes sure it was submitted in a timely fashion. (time is set at top of file)

Please note, I do not normally directly use the actual time() value. Anyone wanting to write a bot to spam ya, will most likely figure that out. I convert this number over to a 32 char hash (not a true hash like md5) in a way it will not easily be figures out

Also, there is a <div> that will contain any errors from the server side validation. This actual tag is not part of the IF statement, so that the div always exists so if you have JS validation it can use this div to display client side validation.

All of the HTML code is stripped down for simplicity here, but it does work.

Code: Select all

<?php

	require_once('file_that_connects_to_database.php');

	define ('MINUTES_TO_SUBMIT',60); // HOW MANY MINUTES THEY HAVE TO SUBMIT THE FORM (prevent Curl calls)

	$aryErr = array(); // Will hold error messages

	// Note, I use a honeypot to help prevent bot spam see end of <form> code
	if (count($_POST)>0 && isset($_POST['hidPostHash']) && isset($_POST['URL']) && $_POST['URL']=='') { // Form submitted

		// Clear any fiels with just whitespace back to being blank for validation
		foreach($_POST as $key=>$val) { if (is_string($val)) ($_POST[$key]=trim($val)); }

		// BEGIN: Validation

			// Required fields

			if (!isset($_POST['txtFirstName']) || strlen($_POST['txtFirstName'])<3) {
				$aryErr['FirstName'] = 'First Name must be at least 3 characters';
			}
			if (!isset($_POST['txtLastName']) || strlen($_POST['txtLastName'])<3) {
				$aryErr['LastName'] = 'First Name must be at least 3 characters';
			}
			if (!isset($_POST['drpState']) || (int)$_POST['drpState']==0) {
				$aryErr['State'] = 'You must select a state';
			}

			// Optional Fields that require certain formats (dates/phone numbers)

			if (isset($_POST['txtDOB']) && $_POST['txtDOB']!='' && !preg_match('%^\d?\d[-/]\d?\d[-/]\d\d(\d\d)?$%',$_POST['txtDOB'])) {
				$aryErr['DOB'] = 'Date of birth must be left blank or be in MM-DD-YYYY format';
			}
			else {
				// Note, other checking should be done to make sure a valid date at least 13 years ago
			}


			// Make sure they submitted it in a timely fashion, (ie not a programmed bot) if no other errors

			if (count($aryErr) == 0) {
				$tsOriginalForm = (int)$_POST['hidPostHash']; // SEE MOTES IN MY POST

				if ($tsOriginalForm + (MINUTES_TO_SUBMIT*60) < time()) {
					$aryErr['FORM'] = "You took too long submitting this form, try again";
				}
			}

		// END: Validation

		if (count($aryErr)==0) { // All submited data is good

			// This block is based upon that all fields that should go to DB are prefixed with either:
			//    txt = text data
			//    drp = numeric data from a dropdown/option list which will be a foreign key to other table
			//    If you have others, just keep adding elseif's to handle those
			//  You have a field called tsCreated which is a DATETIME format for when record was added
			//  You have a field called ipCreated which is varchar(15) for the IP that submitted the info

			$SQL1 = 'INSERT INTO `tblName` (';
			$SQL2 = ') VALUES (';
			foreach ($_POST as $key=>$val) {
				$strPrefix = substr($key,0,3); // used more than once, store it
				if (strlen($key)>4) {
					if ($strPrefix=='txt') {
						$SQL1 .= '`'.substr($key,3).'`,';
						$SQL2 .= "'".addslashes($val)."',";
					}
					elseif ($strPrefix=='drp') {
						$SQL1 .= '`'.substr($key,3).'`,';
						$SQL2 .= (int)$val.",";  // (int) forces to be an interger value, so no need for quotes
					}
				}
			}
			// Note for the following both $SQL1 and $SQL2 are already ending in a comma for next field/value
			$SQL = $SQL1 . '`tsCreated`,`ipCreated`' . $SQL2 . "NOW(),'".$_SERVER['REMOTE_ADDR']."')";

			// This is basic for purpose of sample code...
			mysql_query($SQL) or die("Error Saving Data...");

			// I do a redirect to the thank you page so that can easier track that it was properly submitted in statistics
			header('location:/thank-you-page.php');
			exit;

		}

	}
	else {
		// The form was not submitted, for ALL drpWhatever fields, define their defaults.
		// The function below handles txt fields, so no need to prefine them unless you want them filled

		$_POST['drpState'] = 35; // For me, auto selects Ohio
	}

	// Note, need to load up an array of states here... either from Database or an include...
	$aryStates = array(35=>'Ohio',36=>'Oklahoma',37=>'Oregon',38=>'Pennsylvania');

	$strPostHosh = time(); // SEE NOTES IN MY POST

	function echo_value($key) {
		if (isset($_POST['txt'.$key])) {
			echo htmlspecialchars($_POST['txt'.$key]);
		}
	}

?>
<html>
<head>
	<title>My Site - Give me some data!</title>
</head>
<body>
	<h1>Submit me some data!</h1>
	<p>Please use the following form to sign up/suggest something/contact us... whatever...</p>
	<div id='form-error'>
		<?php if (count($aryErr)>0): ?>
			<p>The following errors were found:</p>
			<?php echo '<ul><li>',implode('</li><li>',$aryErr),'</li></ul>'; ?>
		<?php endif; ?>
	</div>
	<form method="post" action="#">
		<p>All fields marked with a * are required.</p>

		<label for="txtFirstName">First Name *</label>
		<input name="txtFirstName" id="txtFirstName" value="<?php echo_value('FirstName'); ?>" />
		<br />
		<label for="txtLastName">First Name *</label>
		<input name="txtLastName" id="txtLastName" value="<?php echo_value('LastName'); ?>" />
		<br />
		<label for="txtDOB">Date of Birth <em>(MM-DD-YYYY)</em></label>
		<input name="txtDOB" id="txtDOB" value="<?php echo_value('DOB'); ?>" />
		<br />
		<label for="drpState">State *</label>
		<select name="drpState" id="drpState">
			<option value="0" style="font-style: italic">--Select One--</option>
			<?php
				foreach($aryStates as $key=>$val) {
					if ($key == $_POST['drpState']) {
						echo '<option value="',$key,'" selected="selected">',htmlspecialchars($val),"</option>\n";
					}
					else {
						echo '<option value="',$key,'">',htmlspecialchars($val),"</option>\n";
					}
				}
			?>
		</select>
		<br />
		<label for="txtComments">Comments</label>
		<textarea cols="50" rows="4" name="txtComments" id="txtComments"><?php echo_value('Comments'); ?></textarea>

		<!-- SEE NOTES IN POST -->
		<div style="margin-left: -8872px; height: 10px;">This field must be left blank <input type="text" name="URL" value="" /></div>

		<input type="hidden" name="hidPostHash" value="<?php echo $strPostHosh; ?>" />
		<input type="submit" name="submit" value="Save Data" />
	</form>
</body>
</html>

Thanks! I've implemented some the code here and have managed to submit date + ip into the mysql database, which is much better than before since I was completely unable to connect to the mysql database. But how would I post the user submitted data to the mysql database? Would I need to assign variable to the appropriate $_POST and then INSERT INTO that variable into the db table?
social_experiment wrote:

Code: Select all

$insert = "INSERT INTO `".$db_table."` (`question`, `answer`, `dateasked`, `ip`) VALUES ('$question', '$answer', '$dateasked', '$ip')";
$sql = mysql_query($insert);

if ($sql) 
{
 // data written to database
}
else 
{
 // data not written database, issue error
}
I find it easier to assign the mysql_query() function to a variable and check that variable. As the other posters mention, you should look into validating / escaping any input received from your form. Look at mysql_real_escape_string() for escaping input.

On the form that processes the input, do you include config.php?
Wouldn't I need to add the connect variable to the mysql_query function as well? Otherwise it wouldn't be able to connect to the database.

As in mysql_query($insert, $connect). The connect variable being:

Code: Select all

$connect = mysql_connect("$db_hostname", "$db_user", "$db_password");
mysql_select_db("$db_name", $connect);
And I have included a config file in the file where the form is present.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Inserting data from a web form to MySQL Database

Post by social_experiment »

implications wrote:Wouldn't I need to add the connect variable to the mysql_query function as well? Otherwise it wouldn't be able to connect to the database.
For mysql_query() the link identifier is optional so you can use it but you could also leave it away
The Manual wrote:resource mysql_query ( string $query [, resource $link_identifier ] )
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Post Reply