Connecting PHP and MySQL
August 18, 2008
I had promised to write about things I learned while creating a portal system for Asbury Theological Seminary, but I think it might be the most beneficial to begin with the basic theory of combining PHP and MySQL.
Usually when I’m looking for something other than the basics of what PHP and MySQL are, or something really advanced like “how to use PHP to do quantum mechanics” I come up short. Not that I’m usually looking for a way to do work in the astronomy field with PHP, my point is just that there isn’t always a sufficient article about intermediate subjects.
So, I’m going to assume you know something about PHP. If you don’t, take a look around the web for the basics of PHP. It’s a great scripting language capable of making your life a lot easier.
I’m also going to assume you have setup a MySQL database before. Chances are good that it’s a very simple thing to do, if your web host allows it. Normally it’s only as difficult as clicking a button and giving a little bit of information such as username and password. Your web host probably has information about it, or talk with your server administrator.
MySQL Database Layout
As it is stored, data is placed into a table. Tables represent the various aspects of a piece of information. For example, this is all of the information that you would associated with an imaginary collection of users. We’re going to call this table “users”.
You can see that each person has some things associated with them, such as a username, a first name, last name and a password. The other thing is the value “userid.” Certainly you could create this field and manage it in the same way you would manage their other information, or you could tell MySQL to make that value an integer that is automatically given the next value (auto incremental). So, for our chart if the userid field were set with these options the next userid would be 3. This is really useful because it gives us a value that won’t be replicated and that we can use to identify this person as a unique individual.
For the remainder of the article I’m going to use examples that would directly interact with these tables and information.
Easy Administration
Personally, I like to use PHPMyAdmin to manage my MySQL databases. Why? Because it’s just easier than the command line, and I can visualize what’s going on. Rather than having to type in MySQL commands I just click the button for “create a database” and then give it a name. Creating table fields is just as simple, but with more required information. So, let’s begin.
Making The Connection
You’re going to need to connect your PHP script with the information in your database. To do that we need to know your server location, your database name, your username and password. If you have that information it should be placed into variables like this:
$server = "mysql.myhost.com";
$database = "mydatabase";
$username = "myUsername";
$password = "myPassword";
Once that information has been declared you need to make the connection to the server. These two lines should take care of the calls to connect to the server and then to read the database in question. ($database)
// This is the query information, placed into a variable for processing
$connectForData = mysql_connect($server, $username, $password) or die ('Sorry, I could not connect to the MySQL database');
// This part tries to get the database information ($database) from the connection that we just made ($connectForData).
$db_checkedme = mysql_select_db($database, $connectForData) or die('Could not select database.');
Once you have declared the connection you’ll need to start forming queries. Queries can either grab information from the database, put information into it or make sure it’s up to date. And that’s really a simplistic way of explaining it, in reality MySQL can do some really complicated things that obviously won’t fit in a short, concise article.
To make a request against the database we need to put it into a variable. After the query has been formed we need to put that query into action by placing a request into a variable as well. That of course sounds really confusing, but I think it will make sense with some examples.
Putting Information into a Database
The first thing we need to do is place information in the database, to do that we need to use the insert command. We’re going to pretend that we passed some values from a form into some variables ($username, $firstname, $lastname, $passsword).
// This tells the database what you want to do.
$query = "INSERT INTO users (username, firstname, lastname, password) VALUES ('$username', '$firstname', '$lastname', '$password')";
// This ($result) passes that query to the database for action. Without this line nothing will actually happen.
$result = mysql_query($query) or die("Could not insert information into the database: " . mysql_error());
Notice that “userid” is never declared, that’s because I’m assuming that MySQL is setup to auto-increment the value for me. It also may not always be a good idea to use the insert command by itself, you may need to make sure that the information doesn’t already exist, but I’ll get to that in the update section.
Retrieving Information
To pull information from the database we’re going to use the “SELECT” command. Let’s assume you need to grab the fullname of the user with the userid of 501. We’re going to represent that arbitrary number, 501, as $someVariable.
// This is the request for the information you would like
$query = "SELECT fullname FROM users WHERE userid = '$someVariable'";
// This makes it happen
$result = mysql_query($query) or die("Could not request information from the database: " . mysql_error());
So what does that give us? Well, at the moment it doesn’t really give us anything. The information has been pulled but yet it is not available for use. We need to take the next step and represent the information as a set of variables.
There is one problem, however, with the query above. Our user list only has two users, as a result passing the userid of 501 will not return a result. I bring this up because these two things are related to the next two parts of the query.
if (mysql_num_rows($result) != "0") {
while ($row = mysql_fetch_assoc($result)) {
$fullname = $row['fullname'];
}
}
The first part of that chunk of PHP makes sure that the result of our query is not empty. The second part, beginning at the “while”, will loop through the results and present it as $row. The next part to understand is the original query we wrote “SELECT fullname FROM…” Anything that you want to retrieve should be listed there. If it has been listed there you can request it in the while loop. So, had we listed username, fullname, and lastname we could request them as $row[‘username’], $row[‘fullname’] and $row[‘lastname’]. From there we can insert the values into a variable by calling it like this:
$username = $row['username'];
We can use that variable in whatever other PHP functions or scripts that we need.
What if I have two tables of information from which I need to retrieve information? What if that information needs to be referenced between two different tables? Take a look at this table, it’s a representation of privileges for the users.
In this case the userid field is not automatically incremented, instead it is used to reference the users table. So, what needs to happen is that we need to grab the user privileges associated with that user’s id.
To make a call like that we need to change our query a bit. It would look something like this:
$query = “SELECT users.fullname, privileges.privilege FROM users, privileges WHERE users.userid = ‘$someVariable’ and privileges.userid = ‘$someVariable’”;
This should result in two items, the users’ fullname and the users’ privilege level. Of course you will still need to go through the steps to return the results as variables as I mentioned above.
You can also reference parts of a table based on information from another table. For example you could change the “WHERE” part to be:
WHERE users.userid = '$someVariable' and privileges.userid = users.userid
In this case I don’t know why you would want to do that but in other cases this is very important when the result from the second table is actually being referenced from a different value in the first. So practically speaking, if you only had the user id number and needed to look at a piece of information that had been stored by their first name elsewhere.
Updating Information
Once the data has been inserted into our records it may be appropriate to make some changes to it. To make changes we’re going to use the “UPDATE” command.
$query = "UPDATE privileges SET privilege = 'admin' WHERE userid = 'admin'";
$result = mysql_query($query) or die("Cannot update the user permissions" . mysql_error());
That query should update the user’s privilege level to administrator. Take caution with this command, if your query is not specific enough it will update information that it should not update. It also may be necessary to find out if there is already information in the database that should be updated or if it should be created with the insert command. To do that you’ll want to incorporate a select query with the conditional statement to either update or insert. An example of that would be like this:
$query = "SELECT firstname FROM users WHERE lastname = '$someVariable'";
$result = mysql_query($query) or die("Could not request information from the database: " . mysql_error());
if (mysql_num_rows($result) != "0") {
$query = "UPDATE users SET firstname = '$firstname', lastname = '$lastname' WHERE username = '$username'";
$result = mysql_query($query) or die("Cannot update the user information" . mysql_error());
} else {
$query = "INSERT INTO users (username, firstname, lastname, password) VALUES ('$username', '$firstname', '$lastname', '$password')";
$result = mysql_query($query) or die("Could not insert information into the database: " . mysql_error());
}
What happens here is that it’s going to try and grab the information with a select statement, if data exists it’s going to send the request to the update command, if it doesn’t the query is routed to the insert statement. If we add in the extra query it prevents duplicate information from being added to the database.
Deleting Information
If you don’t need a certain piece of data, or maybe you have a duplicate, you can use the “DELETE” command to remove it. You can see an example of that here:
$query = "DELETE FROM users WHERE userid = '$whichUser";
$result = mysql_query($query) or die("Cannot remove the information" . mysql_error());
That should remove whichever user is represented by the variable $whichUser. I think it goes without saying that this command is somewhat dangerous and should be used carefully. And maybe it would be a good idea to mention that any of these queries can be visualized by echoing the variable $query. That way you can see what the request is actually going to do when placed into the $result variable.
Obviously there is a lot of room to improve on this, but that’s why very large books are written about PHP and MySQL. When you’re writing a web application in PHP and MySQL it’s really just a series of these commands over and over, but with different information, variables and contexts. With all of that out of the way we can start getting into some of the more advanced things that I have in mind, but hopefully this also helps fill a gap between the basic “how-to” articles and the extreme examples.
Tagged:
Latest Photos of the Week
Recent Audio Work
