MySql : Insert Data

We need to get some data into the database. To do so we use an HTML form to collect the data, and then insert it 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:

Category:
Site Name:
Site URL:
Description:

The above HTML snippet has the table elements removed because they are for display purpose only. The HTML for the form below is displayed using a table.



This form gathers the information we want to insert into the database and submits back to the same page it is on. We now need the PHP code to process this information. The first block of code is setting up the connection to the database. This is the same as used on the create table page.



This form gathers the information we want to insert into the database and submits back to the same page it is on. We now need the PHP code to process this information. The first block of code is setting up the connection to the database. This is the same as used on the create table page.

?>?>?>

The 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. 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:
?>?>?>

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.

For complte script clik the title of posting