PHP Tutorial - Inserting data into a MySQL Database
Like I said, this tutorial makes things dynamic - a user will enter some stuff - in this case a their name, and a comment - and the PHP script will automatically insert it into your database.
Quick Overview
I presume you know how to make HTML forms (if not, there is a damned good tutorial at HTML Goodies ) the difference between what most people have done with forms before, is that now YOU write the script that handles the fields.
Table Structure
The database should be laid out as shown below:
| id | uname | ucomment | ip | date |
| 1 | Huscy | Wow this is great | 62.35.54.62 | Thu May 1 |
| 2 | s73ad | Amazing. | 80.629.326.26 | Fri May 2 |
The SQL needed to create this table is shown below:
CREATE TABLE comment (
uname varchar(20) NOT NULL default ”,
ucomment longtext NOT NULL,
ip varchar(20) NOT NULL default ”,
time varchar(50) NOT NULL default ”,
id int(11) NOT NULL auto_increment,
PRIMARY KEY (id)
) TYPE=MyISAM;
Here we go!
First of all, we are going to be a little sneaky and get the visitors IP address - this is so that you can block them (to a degree) when you add security features. To obtain this we use the PHP function getenv() which gets the value of an environment variable - i.e. a variable from the machine the page is being viewed by - here you can find a comprehensive list of environmental variables but for now, we’ll just use the IP address variable, called REMOTE_ADDR, as shown below:
$ip = getenv ( ‘REMOTE_ADDR’ );
We also want to get the current date, we do this by using the PHP date() function surprisingly enough! This function can also be used to get the time, by changing the letters in the brackets - you can find the possible time and date formats here .
$date = date ( ‘D M j’ );
The ID field does not need to be collected from a form or an environment variable - it is, as the SQL shows, an auto-increment field, meaning starts at 1 then adds 1 for each entry (so 1,2,3 etc.).
The Form
Setting up the HTML form is very easy, and in this case you only have to deal with one php page, as the action of the form will be the page itself… keep reading and all will become clear.
<form method= ‘get’ action= ‘ <? echo ‘$PHP_SELF’ ; ?> ‘ >
Name : <input type= ‘text’ name= ‘uname’ > <br><br>
Comment : <input type= ‘text’ name= ‘uscomment’ > <br><br>
<input type= ‘hidden’ name= ‘commented’ value= ’set’ >
<input type= ’submit’ value= ‘Post your comment’ >
</form>
Explanation
Now then, I’m sure you are wondering what the PHP is in there for - that variable, $PHP_SELF uses the current URL as the action for the form - so you can put the form handling script in the same document as the HMTL - which is especially useful when you’re script isn’t very long, as in this case. You may also have noticed the ‘hidden’ form field called ‘commented’ - this is used so we can see if the form has been set when the page loads, and if it has, then we can insert the name and comment into our database.
Check the form has been submitted
First of all we have to decide whether or not we ARE going to insert the data - but a simple if statement will take care of that! We check to see whether or not the form has been submitted using the isset() function, which, well, checks to see if the variable is set!
if(isset($_GET[‘commented’]))
{
Insert the data
}
else
{
Show the HTML form
}
The isset function above, tests whether the variable is set, and whilst $_GET[’commented‘] may be unfamiliar to anyone who has used PHP in the past, it must be used with all new versions of PHP - where register_globals is off. If it isnt obvious, the code above checks to see if a user has commented (i.e. submitted the form with the hidden ‘commented’ variable in), and if it has, then the data is inserted into the database, if not, the form to allow the user to comment is displayed.
Inserting the data
// Set Mysql Variables
$host = ‘localhost’;
$user = ‘root’;
$pass = ”;
$db = ‘ex‘;
$table = ‘comment‘;
// Set global variables to easier names
$uname = $_GET[’uname‘];
$ucomment = $_GET[’ucomment‘];
// Connect to Mysql, select the correct database, and run teh query which adds the data gathered from the form into the database
mysql_connect($host,$user,$pass) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());
$add_all = ‘INSERT INTO $table values(’$uname’,'$ucomment’,'$ip’,'$time’,”)‘;
mysql_query($add_all) or die(mysql_error());
}
Now all of this should be familiar to you if you have read my Basic Mysql Commands tutorial, and the comments explain any new concepts - there you go, a page that allows a user to input some data, and then it is added to a database
Putting it all together
| NOTE: If you copy and paste this code, changing the Mysql variables, and paste it into the text editor of your choice, it WILL work (provided you used the sql provided earlier). // If the form has been submitted <? $ip = getenv(‘REMOTE_ADDR’); $time = date(‘D M j’); if(isset($_GET[‘commented’])) |
Conclusion
There we go, a nice simple way to let a user post comment on whatever you fancy!
