PHP Fundamentals: Add data to a database
Now that you've learned how to pull data out of a database and display it on a PHP page, I'm sure the next notion in your mind is how you can add and manipulate data. Granted going to phpMyAdmin can be easy for some, but it's not the most convenient.
Adding and manipulating data will take a few articles, so today we'll get into adding new data through a web page form. Editing and deletion will come later.
First, set up your database table
Download the example files and use the included dbTableSetup.txt file to set up the blank database table used in this exercise through phpMyAdmin. If you don't remember how to do this, please give the Introduction to this series a quick look.
Just like before, enter your database information into the dbConnect.php file so you can connect this form to the database.
Matching form elements with database elements
When you look at addForm.php, you'll see it plays out like any typical web form. I set up a handful of scenarios so you can get an idea of how I like to handle different types of form elements and how to set up your database to coincide with those elements.
With text input boxes, I'd either send the data to a VARCHAR entry with the 255-character limit, or a TEXT entry. Usually I'll use VARCHAR since I like to keep text input boxes for short amounts of data, like a title to a blog entry, a name, or a headline.
Drop-down select lists can use a variety of solutions. VARCHAR or CHAR is ideal for text-based solutions for the data. In this case I used CHAR since the data are terms like s1, s2, etc. You could also use INT or DECIMAL if you were adding numbers instead. I did this in the case of the radio buttons.
With check boxes, you'll notice how I have a separate column in the database table for each item. Granted the three items are all pertaining to one question, but remember a check box isn't a single selection like a drop-down or radio button. They are simply on/off switches, so in each column you'll see either the data or a NULL (empty) value. Like with radio buttons and drop-downs, you don't have to stick with text-based entries like VARCHAR or CHAR. You can also use INT or DECIMAL if you're feeding numbers.
With scrolling textareas, I generally stay away from the 255-character limit of VARCHAR. I'll send the data into a TEXT entry so I have the vast space it gives. Plus you might be adding in HTML code in that data.
The PHP part of the form
You'll notice I have the action of the form pointing to the very same spot as the form. I used to make a separate page to "handle" the data and add it to the database, but found it more efficient to keep it all on one page.
The PHP code above the web form is placed there purposely. I encapsulated it all in a simple if statement:
When you come to the page, this will first check if there is any data sitting in the POST method. Of course there won't be, so you'll simply see the form. However, when you submit data, the if statement will then be judged true, and thus the PHP code within it will be run.
When the if statement is satisfied, the first thing you need to do is pull the data down from the POST method. Here's how:
$variable = $_POST['postDataItem'];
$_POST will take the item stored in the POST method and connect it to a variable. You set up one of these for each piece of data you pull down.
You'll notice I named the form elements form01, form 02, etc. I do this on purpose and suggest you do the same. I personally like to name the variables off the columns in the database, but I do not do it in the web form because it will leave a security hole open when it comes to hackers. You're basically giving them your database table setup if they see it in your form element names.
From here, the next piece of the puzzle is to manipulate any of this data as you see fit before you add it into the database. In this case, I set up two lines utilizing htmlspecialchars to change the input box and the textarea so it would fix any quotation marks.
In this stage, you might need to do more. You might want to throw in scripts to remove all the HTML or change the case of the content, or combine items together. The sky's the limit based on your own needs.
Now comes the point we finally put the data into the database. Here's how:
$Result = mysql_query("INSERT into dbTableName (dbVar01, dbVar02, dbVar03) values ('$phpVar01', '$phpVar02', '$phpVar03')");
The variable $Result can be any variable you like. It's meant to simply be used later to help check and make sure things worked. Using the mysql_query function, you run the INSERT statement in MySQL. It will always say to insert into the name of your database table. You place the names of your database columns in the first set of parentheses with commas separating them, and in the second set of parentheses you place your PHP variables that are holding the data. Encapsulate these variables in single quotation marks and separate these with commas as well.
Be sure order these in the same manner as they are in your database table. That means if you have the columns in your table as "name", "address" and "phone", but you put this:
$Result = mysql_query("INSERT into phpDemo (phone, name, address) values ('$phone', '$name', '$address')");
You'll get an error. The function will only put in data column by column as listed. It can only go forward, not backward. You can skip columns, as you see I did not put an id entry, but you can't go backward.
The last piece of the puzzle is a visual means to see if your script worked or not. A simple if-else statement to see if $Result contains a finished mysql_query function or not. The idea of the exclamation mark before $Result is to check it as a boolean, a simple "yes" or "no" answer. It's a great way to simply check if a variable has a value or not, rather than look for a finite "something" to put on the other side of a ==.
Now that you can put data into a database, the inevitable need to edit or delete this data will definitely arrive. I'll show you how to make a page where you can pull all your entries, then change or delete said data at your leisure.
Questions? Ask here in comments or drop me an email.