Adding data through form interface, into MySQL

anevins · October 23, 2010

I'm trying to add some data into my database through a form interface, but this form submits even when the page loads. This means the form submits regardless of the validation I have set in place, so every time the page is loaded, the database receives empty fields. Could you guys please help me with my problem?

Here's my code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">;

<html xmlns="http://www.w3.org/1999/xhtml">;

<head>

<title>Form to add quotes</title>

</head>

<body>

<?php

//defining variables

$genre = "";

$error_quote="";

$error_author="";

$error_date="";

$error_url="";

$error_genre="";

$quote = "";

$author="";

$q_date="";

$url="";

$output_form = true;

if (isset($_POST['submit'])) {

$output_form = true;

if (trim($quote)=='' OR strlen(trim($quote)) < 2 OR strlen(trim($quote)) > 16)

{

$error_quote="Please enter a <b>Quote</b> between 2 to 16 characters long <br/>";

}

if(trim($author)=='' OR strlen(trim($author)) < 2 OR strlen(trim($author)) > 16)

{

$error_author="Please enter an <b>Author</b> between 2 to 16 characters long <br/>";

}

if(trim($q_date)=='' OR strlen(trim($q_date)) < 2 OR strlen(trim($q_date)) > 24)

{

$error_date="Please enter a <b>Date</b> between 2 to 24 characters long <br/>";

}

if(trim($url)=='' OR strlen(trim($url)) < 2 OR strlen(trim($url)) > 246)

{

$error_url="Please enter a <b>URL</b> between 2 to 246 characters long <br/>";

}

if(!isset($genre) OR $genre=='')

{

$error_genre=" - Please select a <b>Genre</b>. <br/>";

}

else {

$output_form = false;

}

if ($output_form)

{

?>

<table>

<tr><td>Quote</td><td><input type="text" name="quote" value="<?php if (isset($quote)) echo $quote ; ?>"></td><td><?php echo $error_quote;?></td></tr>

<tr><td>Author</td><td><input type="text" name="author" value="<?php if (isset($author)) echo $author ; ?>"></td><td><?php echo $error_author;?></td></tr>

</table>

<p>Genre:</p><p> <input type="radio" name="genre" value="humour"

<?php if (isset($genre) AND $genre=="humour")

echo $genre; ?> />Humour <?php echo $error_genre;?>

<input type="radio" name="genre" value="politics"

<?php if (isset($genre) AND $genre=="politics")

echo $genre; ?> />Politics <?php echo $error_genre;?>

<input type="radio" name="genre" value = "romance"

<?php if (isset($genre) AND $genre=="romance")

echo $genre; ?>/>Romance <?php echo $error_genre;?>

</p>

</form>

<?php

}

$dbhost = 'localhost';

$dbuser = '...';

$dbpass = '...';

$dbname = 'anevins';

// make a connection to the database

$conn = mysql_connect($dbhost, $dbuser, $dbpass)

OR die('Connection failed: '. mysql_error());

// select the database

mysql_select_db($dbname)

OR die('Database select failed: '. mysql_error());

// set up the query to insert the new data

$query = "INSERT INTO quotes (id, quote, author, q_date, url, genre)

VALUES ('', '$quote', '$author', '$q_date', '$url', '$genre')";

$result = mysql_query($query) OR die('Query failed: ' . mysql_error());

echo "<p>Thank you for adding your quote</p>";

mysql_close($conn);

exit();

?>

</body>

</html>

Vitamin · October 23, 2010

That is because you are not allowing the user to enter there data.

Try and do something like this.

<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">

On that line do something like

<form method="post" action="uploaddata.php">

Then make a new php file and name it uploaddata.php

Then on that file do all your my_sql work.

anevins · October 23, 2010

Thank you for taking the time to reply, but I want to work with the same php file. The form is not showing because I've set the php to display it as false when the form submits. Since the form submits on page load, the form disappears.

Pikachu2000 · October 23, 2010

The form isn't submitting when the page loads. You just don't have any logic in place to stop the query from executing if the form hasn't been submitted.

Vitamin · October 23, 2010

change

if ($output_form) {

to

if (!$output_form) {

That will at least get your form to display, then you should have a better understanding of whats going on.

anevins · October 23, 2010

I've tried this, thanks Vitamin.

What is the missing logic, Pikachu?

Pikachu2000 · October 23, 2010

Your query needs to be within the main if( isset($_POST['submit']) ) { conditional. You should also be setting a flag if any field validation fails, and allow the query to run only if the form has been submitted, and there are no validation errors. So basically:

if( isset($_POST['submit']) ) {
     // validate form fields
     if( ~no validation errors~ ) {
          // set up and execute query.
     }
} else {
     // redisplay form for corrections and resubmission
}

anevins · October 23, 2010

I can't figure out how to put this logic into place, with my validation for the if statement for if no validation errors.

Pikachu2000 · October 23, 2010

In each validation operation, add a 'flag' variable and set it of the validaition fails.

if( isset($_POST['submit']) ) {

if (trim($quote)=='' OR strlen(trim($quote)) < 2 OR strlen(trim($quote)) > 16) {

$error_quote="Please enter a <b>Quote</b> between 2 to 16 characters long <br/>";

$error_flag = TRUE;

}

if( $error_flag !== TRUE ) {

// execute query

}

anevins · October 23, 2010

Thank you, I've tried setting these flags with the additional if statement, but I still get the same problem.

Pikachu2000 · October 23, 2010

Post your current code, please.

anevins · October 23, 2010

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<head>

<title>Form to add quotes</title>

</head>

<body>

<?php

//defining variables

$genre = "";

$error_quote="";

$error_author="";

$error_date="";

$error_url="";

$error_genre="";

$quote = "";

$author="";

$q_date="";

$url="";

$output_form = true;

if (isset($_POST['submit'])) {

if (trim($quote)=='' OR strlen(trim($quote)) < 2 OR strlen(trim($quote)) > 16)

{

$error_quote="Please enter a <b>Quote</b> between 2 to 16 characters long <br/>";

$error_flag = true;

}

if(trim($author)=='' OR strlen(trim($author)) < 2 OR strlen(trim($author)) > 16)

{

$error_author="Please enter an <b>Author</b> between 2 to 16 characters long <br/>";

$error_flag = true;

}

if(trim($q_date)=='' OR strlen(trim($q_date)) < 2 OR strlen(trim($q_date)) > 24)

{

$error_date="Please enter a <b>Date</b> between 2 to 24 characters long <br/>";

$error_flag = true;

}

if(trim($url)=='' OR strlen(trim($url)) < 2 OR strlen(trim($url)) > 246)

{

$error_url="Please enter a <b>URL</b> between 2 to 246 characters long <br/>";

$error_flag = true;

}

if(!isset($genre) OR $genre=='')

{

$error_genre=" - Please select a <b>Genre</b>. <br/>";

$error_flag = true;

}

if ($error_flag != true){

$dbhost = 'localhost';

$dbuser = '...';

$dbpass = '...';

$dbname = 'anevins';

// make a connection to the database

$conn = mysql_connect($dbhost, $dbuser, $dbpass)

OR die('Connection failed: '. mysql_error());

// select the database

mysql_select_db($dbname)

OR die('Database select failed: '. mysql_error());

// set up the query to insert the new data

$query = "INSERT INTO quotes (id, quote, author, q_date, url, genre)

VALUES ('', '$quote', '$author', '$q_date', '$url', '$genre')";

$result = mysql_query($query) OR die('Query failed: ' . mysql_error());

echo "<p>Thank you for adding your quote</p>";

}

else {

$output_form = false;

}

if ($output_form)

{

?>

<table>

<tr><td>Quote</td><td><input type="text" name="quote" value="<?php if (isset($quote)) echo $quote ; ?>"></td><td><?php echo $error_quote;?></td></tr>

<tr><td>Author</td><td><input type="text" name="author" value="<?php if (isset($author)) echo $author ; ?>"></td><td><?php echo $error_author;?></td></tr>

</table>

<p>Genre:</p><p> <input type="radio" name="genre" value="humour"

<?php if (isset($genre) AND $genre=="humour")

echo $genre; ?> />Humour <?php echo $error_genre;?>

<input type="radio" name="genre" value="politics"

<?php if (isset($genre) AND $genre=="politics")

echo $genre; ?> />Politics <?php echo $error_genre;?>

<input type="radio" name="genre" value = "romance"

<?php if (isset($genre) AND $genre=="romance")

echo $genre; ?>/>Romance <?php echo $error_genre;?>

</p>

</form>

<?php

}

?>

</body>

</html>

lenstanbera · October 23, 2010

I've pasted your code, created database and table accordingly, and when I run it, this is what it gave me:

see attachment

[attachment deleted by admin]

anevins · October 23, 2010

That is correct, you see the thank you message appears even when nothing is entered into the form, when nothing is submitted. Your database should have a new row each time you refresh the page, which isn't what I wanted.

Pikachu2000 · October 23, 2010

I didn't test this with a database, but it should work. See my comments within the code . . .

<?php
//defining variables
$error_quote="";
$error_author="";
$error_date="";
$error_url="";
$error_genre="";

if (isset($_POST['submit'])) {
array_map('trim', $_POST); // trim() the entire $_POST array at once.

if ( !isset($_POST['quote']) || strlen($_POST['quote']) < 2 || strlen($_POST['quote']) > 16 ) { // if form field 'quote' value not set, or too short or too long, error
	$error_quote="Please enter a <b>Quote</b> between 2 to 16 characters long <br/>";
	$error_flag = true;
} else { // otherwise, assign value of form field 'quote' to the $quote variable.
	$quote = $_POST['quote'];
}

if( !isset($_POST['author']) || strlen($_POST['author']) < 2 || strlen($_POST['author']) > 16) { // this is same as above
	$error_author="Please enter an <b>Author</b> between 2 to 16 characters long <br/>";
	$error_flag = true;
} else {
	$author = $_POST['author'];
}

if( !isset($_POST['q_date']) || strlen($_POST['q_date']) < 2 || strlen($_POST['q_date']) > 24) { // same as above
	$error_date="Please enter a <b>Date</b> between 2 to 24 characters long <br/>";
	$error_flag = true;
} else {
	$q_date = $_POST['q_date'];
}

if( !isset($_POST['url']) || strlen($_POST['url']) < 2 || strlen($$_POST['url']) > 246) { // same as above
	$error_url="Please enter a <b>URL</b> between 2 to 246 characters long <br/>";
	$error_flag = true;
} else {
	$url = $_POST['url'];
}
if( !isset($_POST['genre']) || !in_array($_POST['genre'], array( 'humour', 'politics', 'romance')) ) { // if form field 'genre' not set, or isn't one of the only three permitted values, error
	$error_genre=" - Please select a <b>Genre</b>. <br/>";
	$error_flag = true;
} else { // else assign value to variable
	$genre = $_POST['genre'];
}

if ($error_flag !== true) { // If there are no validation errors, run the DB query.
	$dbhost
	= 'localhost';
	$dbuser
	= '...';
	$dbpass
	= '...';
	$dbname
	= 'anevins';
	// make a connection to the database
	$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Connection failed: '. mysql_error());
	// select the database
	mysql_select_db($dbname) OR die('Database select failed: '. mysql_error());
	// set up the query to insert the new data
	$query = "INSERT INTO quotes (id, quote, author, q_date, url, genre) VALUES ('', '$quote', '$author', '$q_date', '$url', '$genre')";
	$result = mysql_query($query) OR die('Query failed: ' . mysql_error());
	if( mysql_affected_rows() > 0 ) { // check to see if a row was actually inserted.
		echo "<p>Thank you for adding your quote</p>";
		$redisplay = FALSE; // if a record was inserted, set $redisplay = FALSE to prevent form redisplay
	} else {
		echo "Sorry, your quote was not added to the database.";
	}
}
}
if( $redisplay !== FALSE ) { // Only redisplay form if $redisplay has not been set to FALSE
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Form to add quotes</title>
</head>
<body>
<form method="post" action=""> <!-- Using echo $_SERVER['PHP_SELF'] is a known XSS vulnerability. Use action="" instead -->
<table>
<tr><td>Quote</td><td><input type="text" name="quote" value="<?php if (isset($quote)) echo $quote ; ?>"></td><td><?php echo $error_quote; ?></td></tr>
<tr><td>Author</td><td><input type="text" name="author" value="<?php if (isset($author)) echo $author ; ?>"></td><td><?php echo $error_author;?></td></tr>
<tr><td>Date</td><td><input type="text" name="q_date" value="<?php if (isset($q_date)) echo $q_date ; ?>"></td><td><?php echo $error_date;?></td></tr>
<tr><td>URL</td><td><input type="text" name="url" value="<?php if (isset($url)) echo $url ; ?>"></td><td><?php echo $error_url;?></td></tr>
</table>
<p>Genre:</p>
<p>
<input type="radio" name="genre" value="humour"	<?php if (isset($genre) AND $genre=="humour") echo $genre; ?> />Humour</br> <?php echo $error_genre;?>
<input type="radio" name="genre" value="politics" <?php if (isset($genre) AND $genre=="politics")echo $genre; ?> />Politics<br />
<input type="radio" name="genre" value = "romance" <?php if (isset($genre) AND $genre=="romance") echo $genre; ?>/>Romance<br />
</p>
<p><input type = "submit" name="submit" value = "add quote" /></p>
</form>
</body>
</html>
<?php
}
?>

anevins · October 23, 2010

Brilliant, thanks Pikachu and everyone for taking the time to help me out.

Pikachu's code works perfectly.

lenstanbera · October 24, 2010

hello. I took pikachu2000 code and tested. Got problems with undefined variables. I've changed the code to:

<?php

//defining variables

$error_quote=NULL;

$error_author=NULL;

$error_date=NULL;

$error_url=NULL;

$genre=NULL;

$quote=NULL;

$author=NULL;

$date=NULL;

$url=NULL;

$genre=NULL;

$redisplay = TRUE;

if (isset($_POST['submit'])) {

array_map('trim', $_POST); // trim() the entire $_POST array at once.

if ( !isset($_POST['quote']) || strlen($_POST['quote']) < 2 || strlen($_POST['quote']) > 16 ) { // if form field 'quote' value not set, or too short or too long, error

$error_quote="Please enter a <b>Quote</b> between 2 to 16 characters long <br/>";

$error_flag = true;

} else { // otherwise, assign value of form field 'quote' to the $quote variable.

$quote = $_POST['quote'];

}

if( !isset($_POST['author']) || strlen($_POST['author']) < 2 || strlen($_POST['author']) > 16) { // this is same as above

$error_author="Please enter an <b>Author</b> between 2 to 16 characters long <br/>";

$error_flag = true;

} else {

$author = $_POST['author'];

}

if( !isset($_POST['q_date']) || strlen($_POST['q_date']) < 2 || strlen($_POST['q_date']) > 24) { // same as above

$error_date="Please enter a <b>Date</b> between 2 to 24 characters long <br/>";

$error_flag = true;

} else {

$q_date = $_POST['q_date'];

}

if( !isset($_POST['url']) || strlen($_POST['url']) < 2 || strlen($$_POST['url']) > 246) { // same as above

$error_url="Please enter a <b>URL</b> between 2 to 246 characters long <br/>";

$error_flag = true;

} else {

$url = $_POST['url'];

}

if( !isset($_POST['genre']) || !in_array($_POST['genre'], array( 'humour', 'politics', 'romance')) ) { // if form field 'genre' not set, or isn't one of the only three permitted values, error

$error_genre=" - Please select a <b>Genre</b>. <br/>";

$error_flag = true;

} else { // else assign value to variable

$genre = $_POST['genre'];

}

if ($error_flag !== true) { // If there are no validation errors, run the DB query.

$dbhost

= 'localhost';

$dbuser

= 'lenstanbera';

$dbpass

= '27yriloba';

$dbname

= 'anevins';

// make a connection to the database

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Connection failed: '. mysql_error());

// select the database

mysql_select_db($dbname) OR die('Database select failed: '. mysql_error());

// set up the query to insert the new data

$query = "INSERT INTO quotes (id, quote, author, q_date, url, genre) VALUES ('', '$quote', '$author', '$q_date', '$url', '$genre')";

$result = mysql_query($query) OR die('Query failed: ' . mysql_error());

if( mysql_affected_rows() > 0 ) { // check to see if a row was actually inserted.

echo "<p>Thank you for adding your quote</p>";

$redisplay = FALSE; // if a record was inserted, set $redisplay = FALSE to prevent form redisplay

} else {

echo "Sorry, your quote was not added to the database.";

}

if( $redisplay !== FALSE ) { // Only redisplay form if $redisplay has not been set to FALSE

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<head>

<title>Form to add quotes</title>

</head>

<body>

<table>

<tr><td>Quote</td><td><input type="text" name="quote" value="<?php if (isset($quote)) echo $quote ; ?>"></td><td><?php echo $error_quote; ?></td></tr>

<tr><td>Author</td><td><input type="text" name="author" value="<?php if (isset($author)) echo $author ; ?>"></td><td><?php echo $error_author;?></td></tr>

</table>

<p>Genre:</p>

<p>

<input type="radio" name="genre" value="humour" <?php if (isset($genre) AND $genre=="humour") echo $genre; ?> />Humour<br />

<input type="radio" name="genre" value="politics" <?php if (isset($genre) AND $genre=="politics")echo $genre; ?> />Politics<br />

<input type="radio" name="genre" value = "romance" <?php if (isset($genre) AND $genre=="romance") echo $genre; ?>/>Romance<br />

</p>

</form>

</body>

</html>

<?php

}

?>

but now I get following message when I click submit:

Notice: Undefined variable: www.example.com in C:\xampp\xampp\htdocs\sql site\formphp2.php on line 40

Notice: Undefined variable: error_flag in C:\xampp\xampp\htdocs\sql site\formphp2.php on line 53

Thank you for adding your quote

_________________________________

any suggestions?

lenstanbera · October 24, 2010

hi, I've made some changes to previous code:

<?php

//defining variables

$error_quote=NULL;

$error_author=NULL;

$error_date=NULL;

$error_url=NULL;

$error_flag = true;

$genre=NULL;

$quote=NULL;

$author=NULL;

$date=NULL;

$url=NULL;

$genre=NULL;

$redisplay = TRUE;

if (isset($_POST['submit'])) {

array_map('trim', $_POST); // trim() the entire $_POST array at once.

if ( !isset($_POST['quote']) || strlen($_POST['quote']) < 2 || strlen($_POST['quote']) > 16 ) { // if form field 'quote' value not set, or too short or too long, error

$error_quote="Please enter a <b>Quote</b> between 2 to 16 characters long <br/>";

$error_flag = true;

} else { // otherwise, assign value of form field 'quote' to the $quote variable.

$quote = $_POST['quote'];

}

if( !isset($_POST['author']) || strlen($_POST['author']) < 2 || strlen($_POST['author']) > 16) { // this is same as above

$error_author="Please enter an <b>Author</b> between 2 to 16 characters long <br/>";

$error_flag = true;

} else {

$author = $_POST['author'];

}

if( !isset($_POST['q_date']) || strlen($_POST['q_date']) < 2 || strlen($_POST['q_date']) > 24) { // same as above

$error_date="Please enter a <b>Date</b> between 2 to 24 characters long <br/>";

$error_flag = true;

} else {

$q_date = $_POST['q_date'];

}

if( !isset($_POST['url']) || strlen($_POST['url']) < 2 || strlen($$_POST['url']) > 246) { // same as above

$error_url="Please enter a <b>URL</b> between 2 to 246 characters long <br/>";

$error_flag = true;

} else {

$url = $_POST['url'];

}

if( !isset($_POST['genre']) || !in_array($_POST['genre'], array( 'humour', 'politics', 'romance')) ) { // if form field 'genre' not set, or isn't one of the only three permitted values, error

$error_genre=" - Please select a <b>Genre</b>. <br/>";

$error_flag = true;

} else { // else assign value to variable

$genre = $_POST['genre'];

}

if ($error_flag !== true) { // If there are no validation errors, run the DB query.

$dbhost

= 'localhost';

$dbuser

= 'lenstanbera';

$dbpass

= '27yriloba';

$dbname

= 'anevins';

// make a connection to the database

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Connection failed: '. mysql_error());

// select the database

mysql_select_db($dbname) OR die('Database select failed: '. mysql_error());

// set up the query to insert the new data

$query = "INSERT INTO quotes (id, quote, author, q_date, url, genre) VALUES ('', '$quote', '$author', '$q_date', '$url', '$genre')";

$result = mysql_query($query) OR die('Query failed: ' . mysql_error());

if( mysql_affected_rows() > 0 ) { // check to see if a row was actually inserted.

echo "<p>Thank you for adding your quote</p>";

$redisplay = FALSE; // if a record was inserted, set $redisplay = FALSE to prevent form redisplay

} else {

echo "Sorry, your quote was not added to the database.";

}

if( $redisplay !== FALSE ) { // Only redisplay form if $redisplay has not been set to FALSE

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<head>

<title>Form to add quotes</title>

</head>

<body>

</body>

<table>

<tr><td>Quote</td><td><input type="text" name="quote" value="<?php if (isset($quote)) echo $quote ; ?>"></td><td><?php echo $error_quote; ?></td></tr>

<tr><td>Author</td><td><input type="text" name="author" value="<?php if (isset($author)) echo $author ; ?>"></td><td><?php echo $error_author;?></td></tr>

</table>

<p>Genre:</p>

<p>

<input type="radio" name="genre" value="humour" <?php if (isset($genre) AND $genre=="humour") echo $genre; ?> />Humour<br />

<input type="radio" name="genre" value="politics" <?php if (isset($genre) AND $genre=="politics")echo $genre; ?> />Politics<br />

<input type="radio" name="genre" value = "romance" <?php if (isset($genre) AND $genre=="romance") echo $genre; ?>/>Romance<br />

</p>

</form>

</body>

</html>

<?php

}

?>

____________________________

and get tis error:

Notice: Undefined variable: www.example.com in C:\xampp\xampp\htdocs\sql site\formphp2.php on line 41

lenstanbera · October 24, 2010

sorry guyz, struggling a lot with this code. I pasted pikachu2000 code, jut updated database connection details, and I get following error:

Notice: Undefined variable: redisplay in C:\xampp\xampp\htdocs\sql site\formphp3.php on line 70

___________________________

any help will be appreciated

Pikachu2000 · October 24, 2010

Just need to add 2 lines:

//defining variables
$error_quote="";
$error_author="";
$error_date="";
$error_url="";
$error_genre="";
$redisplay = TRUE;  //  < --- ADD THIS LINE

if (isset($_POST['submit'])) {
   array_map('trim', $_POST); // trim() the entire $_POST array at once.
   $error_flag = FALSE;  //  < --- ADD THIS LINE

lenstanbera · October 24, 2010

hi Pikachu2000, thanks for sharing your knowledge, and sparing time solving the script errors with us mate.

I'll test the code and will post findings.

Sign In

Adding data through form interface, into MySQL

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information