web blazonry web development scripts and tutorials Get best price on Unlocked Samsung Galaxy A21
   PHP       Name Generators       Perl       CSS       Javascript       Java       MySql       How Tos       Resources   

MySQL Home

MySQL Tutorial
  Getting Started
  Create Database
  Insert Data
  View Data
  Manage Data

Bookmark and Share





MySQL PHP Database Tutorial: Insert Data

Next, we need to get some data into the database. To do so, we use an HTML form to collect the data, and then insert the data into the database using PHP. Both these functions can be done on the same page.

The form can submit to the same page it is on. This page checks if the POST method is used. If so, insert into the database. I find it easier to have the form and database insert operation on the same page. Makes one less file to keep track of.

Here is the HTML for the form:

<form name="fa" action="insert_link.php" method="post">
<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 form looks like the one below. This tutorial does not use an actual database, so this form is not "hooked up" to anything. It is to show you what we are working with. Also note that the above HTML snippet has the HTML formatting table elements removed because the the snippet is for display purposes only. The actual, underlying HTML used to display the form below uses an HTML table.

Category:
Site Name:
Site URL:
Description:


This form gathers the data we want to insert into the database and submits back to the same page.

Now we need the PHP code to process this information. The first block of code sets up the connection to the database. This is the same as the code we used on the create table page.

<?php
  $usr = "---username---";
  $pwd = "---password---";
  $db = "linksdb";
  $host = "localhost";

  $cid = mysql_connect($host,$usr,$pwd);

  if (!$cid) { echo("ERROR: " . mysql_error() . "\n"); }
?>

We need to create our SQL statement for the insert. The SQL format for an INSERT is:

INSERT INTO -tablename-
(column_name1, column_name2, ...) VALUES
(data1, data2, ... )

Where the first column name matches to the first data in each of the sets. Remember, PHP variables are automatically initialized with the form field names submitted to the page. So the PHP code to submit the query and check for errors is:

<?php

if ($_SERVER['REQUEST_METHOD'] == "POST") {

// the following 4 lines are needed if your server has register_globals set to Off
$category = $_POST['category'];
$sitename = $_POST['sitename'];
$siteurl = $_POST['siteurl'];
$description = $_POST['description'];

$SQL = " INSERT INTO links ";
$SQL = $SQL . " (category, sitename, siteurl, description) VALUES ";
$SQL = $SQL . " ('$category', '$sitename','$siteurl','$description') ";
$result = mysql_db_query($db,"$SQL",$cid);

if (!$result) {
    echo("ERROR: " . mysql_error() . "\n$SQL\n"); }

echo ("New Link Added\n");

}

mysql_close($cid);
?>

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. Because you do not 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 "double-up" the apostrophes. That is, put two apostrophes in the text where you want just one. For example, to insert the phrase "what's up?" into a database, the SQL code looks like:

INSERT INTO mytable (phrases) VALUES ('what''s up?')

In PHP there is a string function which allows you to do just this on variables quite easily: str_replace This function replaces one value with another in a string. So before you insert data in the database you should replace all single apostrophes with double-apostrophes. For the example variable, the PHP code is:

$description = str_replace("'","''",$description);

Note: This does not insert two apostrophes into the database, just one. So when you pull the data out of the database, it will contain only single apostrophes.


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: View Data from Database

Related Links


Tutorial Sections

 

 

Newest Pages
Test Google Ads Ver. 2
Free Linux Admin Books
Free Linux Books for Programmers
Free Books for Linux on the Desktop
Free PHP Books
Free JavaScript Books
Free Java Books - Advanced
Free Java Books - Basic
Free Perl Books
Free Python Books
Quote of the Day (PHP)
Debugging Part 2
How to Test Google Ads
Most Popular Pages
Baby Name Generator
U.S. Name Generator
Wu Name Generator
Popup Windows (JavaScript)
Upload and Resize an Image (PHP)
How To Install Apache + PHP + MySQL
Intro to Web Databases (PHP, MySQL)

Least Popular Pages
iNews Applet (Java)
Java Resources
Site Monitor (Perl)
PHP Resources
 
 

  privacy policy     ||     © 1997-2016. astonishinc.com   All Rights Reserved.