Adding data via a form to a database

7724

Hi All,

Created a form, which I want to add to a mysql database named employees

<?

$Host = "localhost";
$User = "*******";
$Password = "******";
$DBname = "******";
$TableName = "employees";
$Link = mysql_connect($Host,$User, $Password);
$Result = mysql_db_query($DBname,$Query,$Link);

if(isset($_POST['Submit']))
{
$query = "INSERT INTO employee VALUES ('$firstname','$lastname','$dob','$password','$house','$town','$county','$country','$postcode')";
$msg = "New record is saved";
}

mysql_close();
?>
<html>
<head>
<title>Add Details to Database</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body bgcolor="#FFFFFF">
<?php echo $msg?>
<form action="" method="post">
First Name: <input type="text" name="firstname"><br>
Last Name: <input type="text" name="lastname"><br>
Date Of Birth: <input type="text" name="dob"><br>
Password: <input type="text" name="password"><br>
House: <input type="text" name="house"><br>
Town: <input type="text" name="town"><br>
County: <input type="text" name="county"><br>
Country: <input type="text" name="country"><br>
Postcode: <input type="text" name="postcode"><br>
  <input type="Submit" name="Submit" value="Submit">
</form>
</body>
</html>

When I hit submit the record states Record is saved but it does not actually save the data entered to the database?

Can anyone please advise?

Thanks

Chris

Houdini

Did you select a database? You might want to change

$Result = mysql_db_query($DBname,$Query,$Link);

$Select= mysql_select_db($DBname,$link)
  or die("Could not select database.".mysql_error());

also put an error check into the connection like this

$Link = mysql_connect($Host,$User, $Password)
  or die("Could not connect to server ".mysql_error());

7724

Thanks, but still problems.

Bit further on now, but...

This is driving me potty! Trying to add data to a MYSQL database via a form, but when I hit submit I get an error:

Query failed: Duplicate entry '' for key 1

Here's my db layout:

(employeeid varchar(8) not null,
password varchar(6) not null,
dob date not null,
firstname varchar(20) not null,
lastname varchar(20) not null,
house varchar(30) not null,
town varchar(30) not null,
county varchar(30) not null,
country varchar(30) not null,
postcode varchar(10) not null,
PRIMARY KEY (employeeid)); - //Not AN AUTOINCREMENT

...And my code

Can you please advise?


<?php 

$Host = "localhost";
$User = "********";
$Password = "*******";
$DBname = "*******";
$TableName = "******";
$Link = mysql_connect($Host,$User, $Password); 
$Result = mysql_db_query($DBname,$Query,$Link);

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

  $query = "INSERT INTO employee (employeeid,password,dob,firstname,lastname,house,town,county,country,postcode) VALUES ('$employeeid','$password','$dob','$firstname','$lastname','$house','$town','$county','$country','$postcode')";
  $msg = "New record is saved"; 

  $Link = mysql_connect($Host,$User, $Password); 
  mysql_select_db($DBname); 
  $result = mysql_query($query) or die('Query failed: ' . mysql_error()); 
  mysql_close(); 
} 

?> 

<html> 
<head> 
<title>Add  Details to Database</title> 
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> 
</head> 

<body bgcolor="#FFFFFF">
<?php echo $msg?>
<form action="" method="post"> 
Employee ID: <input type="text" name="employeeid"><br> 
Password: <input type="text" name="password"><br> 
Date of Birth: <input type="text" name="dob"><br> 
First Name: <input type="text" name="firstname"><br> 
Last Name: <input type="text" name="lastname"><br> 
House: <input type="text" name="house"><br> 
Town: <input type="text" name="town"><br> 
County: <input type="text" name="county"><br> 
Country: <input type="text" name="country"><br> 
Postcode: <input type="text" name="postcode"><br> 
  <input type="Submit" name="Submit" value="Submit"> 
</form> 
</body> 
</html>

n_wattam

on your inset line at the top your varibles are wrong, you do not have the '$' on the front of them all, by looks of it you have just copyied the same as your form varible names.

I'm just grabbing some code i have which submits form data to a database, which you can use as an example and then change it to your needs if need's be

n_wattam

excuse some of my bad habbits 🙂, however this should work well for you. I terminate the php at the start of big amounts of HTML and then re open the php for ease of editing etc.

Database Layout

CREATE TABLE newsevents (
id int(11) NOT NULL auto_increment,
title varchar(250) NOT NULL default '',
date varchar(100) NOT NULL default '',
content text NOT NULL,
pdf varchar(200) NOT NULL default '',
publishstart varchar(100) NOT NULL default '',
publishfinish varchar(100) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM AUTO_INCREMENT=101 ;

Code, i have php echo and var statements in the values on the forms, as this code is to read exisiting records in also and edit aswell as to create.

	<?
	define("DB_SERVER", "localhost");
	define("DB_USER", "");
	define("DB_PASS", "");
	define("DB_NAME", "");

$db = mysql_connect(DB_SERVER, DB_USER, DB_PASS);
mysql_select_db(DB_NAME,$db);
?>

<?
if ($buc1 == "SUBMIT") {
	$query = "UPDATE newsevents SET 
	title='$new_title', 
	date='$new_date', 
	content='$new_content', 
	pdf='$upload', 
	publishstart='$new_publishstart', 
	publishfinish='$new_publishfinish' 
	";	
	mysql_query($query);

	$buc1 = "";
} else {
	?>
	<form action='<? echo $PHP_SELF; ?>' method='post'>
		<span class='default-content'><strong>Title</strong></span>
		<input name='new_title' type='text' value='<? echo $newsevents['title']; ?>' size='20' class='default-content'>
		<span class='default-content'><strong>Date</strong></span>
		<input name='new_date' type='text' value='<? echo $newsevents['date']; ?>' size='20' class='default-content'><br>
		<br>
		<span class='default-content'><strong>Publish Start</strong></span>
		<input name='new_publishstart' type='text' value='<? echo $newsevents['publishstart']; ?>' size='20' class='default-content'>
		<span class='default-content'><strong>Publish Finish</strong></span>
		<input name='new_publishfinish' type='text' value='<? echo $newsevents['publishfinish']; ?>' size='20' class='default-content'><br>
		<br>
		<span class='default-content'><strong>Upload PDF</strong></span>
		<input name='upload' type='file' class='default-content'> : <? echo $newsevents['pdf']; ?><br>
		<br>
		<span class='default-content'><strong>Content</strong></span><br>
		<textarea name='new_content' cols=80 rows=20 wrap=OFF id='new_content' class='default-content'><? echo $newsevents['content']; ?></textarea>
		<br>
		<input name='buc1' type='submit' value='SUBMIT'>
	</form>
	<?
}
?>

saraadmin

As usual when working with SQL, its important to check your queries using some debugging output:

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

  $query = "INSERT INTO employee (employeeid,password,dob,firstname,lastname,house,town,county,country,postcode) VALUES ('$employeeid','$password','$dob','$firstname','$lastname','$house','$town','$county','$country','$postcode')";

  // DEBUG:
  echo("\n\n<p><b>DEBUG:</b> query=\"$query\"</p>\n\n");
  // END DEBUG

  $msg = "New record is saved";

  $Link = mysql_connect($Host,$User, $Password);
  mysql_select_db($DBname);
  $result = mysql_query($query) or die('Query failed: ' . mysql_error());
  mysql_close();
}

Now, I may be behind the times, but I don't think that just by POSTing values from an HTML form they become available as variables in a PHP script. (Correct me if I'm wrong, this may be a new feature).

I think that your query string needs to be contructed like this:

// make an array of the field names:
$fields = array("employeeid", "password", "dob", "firstname", "lastname", "house", "town", "county", "country", "postcode");

// start the query string using the fields array
$query = "INSERT INTO employee (" . join(",", $fields) . ")";

// create an empty array for the POSTed values
$values = array();

// iterate through the field names
foreach ($fields as $field_name)
{
    // and for each one, retrieve the corresponding POSTed
    // value and add it to the values array
    $values[] = "\"" . $_POST[$field_name] . "\"";
}

// complete the query string by adding the VALUES
// clause using the values array:
$query .= " VALUES (" . join(", ", $values) . ")";

// DEBUG:
echo("\n\n<p><b>DEBUG:</b> query=\"$query\"</p>\n\n");
// END DEBUG

// execute query as before...