Web DB Tutorial : Insert Data into DB
We need to get some data into the database to make it useful. To do so we need an HTML form to collect the data, and then insert it into the database using PHP. Both of these functions can be done on the same page, since the form can submit to itself.
The PHP script will check if the POST method is used (meaning the form was submitted), if so insert into the database, else a GET method is used so display the HTML form. I find it easier to have the form and database part on the same page. One less file to keep track of.
Here is the HTML for the form:
<b>Category: </b> <input type="text" name="category" size=40>
<b>Site Name:</b> <input type="text" name="sitename" size=40>
<b>Site Url: </b> <input type="text" name="siteurl" value="http://" size=40>
<b>Description: </b> <textarea name="description" rows=5 cols=40></textarea>
<p><input type="submit" value="Add Link"></p>
</form>
The above HTML has the table elements removed since they are used
just for display purpose, the form below is displayed using a table.
This form gathers the information we want to store into the database and submits back to the same page it is on. We now need the PHP code to process this information.
This PHP second part is run only when the request method on the page is a post. This is when the form is submitted back to the page.
The first block of code is setting up the connection to the database. This is the same used on the create table page.
$usr = "---username---";
$pwd = "---password---";
$db = "linksdb";
$host = "localhost";
$cid = mysql_connect($host,$usr,$pwd);
mysql_select_db($db);
if (!$cid) { print "ERROR: " . mysql_error() . "\n"; }
?>
We need to create our SQL statement for the insert. The SQL format for an INSERT is:
(column_name1, column_name2, ...) VALUES
(data1, data2, ... )
PHP variables were previously automatically initialized, with versions greater than 4.2 register global was turned off by default. So you may need to use the following to set the variables:
$sitename = $_POST["sitename"];
$siteurl = $_POST["siteurl"];
$description = $_POST["description"];
So the PHP code to submit the query and checking for any errors is:
if ($_SERVER['REQUEST_METHOD'] == "POST") {
$sql = " INSERT INTO links ";
$sql .= " (category, sitename, siteurl, description) VALUES ";
$sql .= " ('$category', '$sitename','$siteurl','$description') ";
$result = mysql_query($sql, $cid);
if (mysql_error()) { print "Database ERROR: $sql " . mysql_error(); }
print "New Link Added";
}
|
Escaping Data When creating SQL statements string values are delimited using apostrophes (see above code). So what happens when there is an apostrophe in the data you are trying to insert? A SQL error will occur if for example the description variable included an apostrophe. Since you don't know what the user will type in you must assume they are entering all sorts of bad data. To insert an apostrophe into the database using SQL you need to escape the apostrophes.
This can be done using the "\" character. For example, if you want to insert the phrase
"what's up?" into a database the SQL code would look like:
INSERT INTO mytable (phrases) VALUES ('what\'s up?')
In PHP there is a built-in function which automatically adds all the necessary slashes.
This function is called addslashes()
So before you insert any user entered data in the database you should be sure to do this.
An example, combining with setting the POST variable would be:
$description = addslashes($_POST["description"]);
Note: This does not insert the slashes into the database just one. So when you pull the data out of the database it will only contain the apostrophe. |
Putting this together with the HTML code above gives us the complete script. Download this script here. (insert_link.phps) Load the page on your server and insert a couple of links into the database created previously. We will need some data in the database for the next section, viewing the data.
| Sections: | Getting Started | Create Database | Insert Data | View Data | Manage Data | Resources |