PHP is powerful on its own. You can perform calculations, store and retrieve session values, restrict access to files, process form input, send out mails (if a mail server is set up), and perform many other server-side functions, and even write and read to files on your server. However, using a database like MySQL allows for a much more complex application development by allowing you to store and retrieve information centrally and efficiently. For example, you might have a web site with a single password, or even a file containing users and passwords separated one per line. While this works, what happens when you want to store detailed itemized information about these users, such as first and last name, phone number, email address, their password (or preferably a hashed version of it!), and other details that simply are too complex to reasonably store in flat-file format? This is where databases come in handy: they provide you with any number of tables of your design to store information in columns and rows for easy access later. Databases like MySQL (the one we will use here) take over much of the complex math and logic involved in querying data sets and storing and updating stored data, and therefore you only have to pass in higher-level very English-sounding instructions (known as SQL - Structured Query Language) in order to communicate with them.
While setting up MySQL on a server can sometimes be a more complicated process than I care to address here, hopefully you're lucky enough to have used either MAMP or WAMP, as described earlier in this set of tutorials. In this case, it turns out that a collection of MySQL databases are already available to you, and you can create new databases and tables within them as needed! Going on the assumption that you're using WAMP or LAMP, or some other setup that already has access to "phpMyAdmin" (a php-based MySQL management tool), let's take a look at how to create a database, create a sample table, and insert, update, select and delete some data.
If you're using MAMP (Mac), you're in luck. phpMyAdmin is one of the tools that is available directly from the start page. When you start up MAMP, an admin box will pop up and remain on your screen as long as MAMP is running. It will have buttons like "Stop Servers", "Open start Page", "Preferences" and "Quit". Both the Apache and MySQL Servers should be Green/on position. (If not, turn them on.)
If you're using WAMP (Windows), then you should see a WAMP icon in your toolbar in the bottom right of your monitor. Click it to open up a menu, and then you should be able to navigate to a phpMyAdmin shortcut to get to a similar page.
Click "Open start Page", and your local MAMP start page should show up. Click the 'phpMyAdmin' tab to enter phpMyAdmin.
On your main phpMyAdmin screen, among other things, you'll see a form to create a database. This is fairly straightforward. Think of a name to give your database. You could call it anything, but for now you could just call it "test". You probably don't have to select any other options, but if something else is selected, you might change the collation to utf8_general_ci:
Click 'Create', and then once it runs, you should notice your database appear in the list on the left. Click it, and you'll arrive at a screen that shows you some tabs along the top such as "Structure", "SQL", etc. You'll also see a form to create a new table. Since you don't have any tables yet, that's probably a good idea. Again, you could call it just about anything (as long as it's not a reserved MySQL keyword), but "test" is never a bad idea. This time, you'll have to also indicate how many fields (another name for 'columns') you want to include in your table. In this case, you could pick any number, but let's just say 2:
Click 'Go', and you should arrive at a new screen that prompts you to enter information about each of the columns in the table that you're trying to create. Let's create two: "id" and "label":
I'd like to stop and talk VERY briefly about the settings I've given each of these columns, since this is something important that you'll have to keep in mind when you create tables for various purposes. You'll have to decide on columns to use, what kinds of data they store (integers, floats, doubles, text strings, large text blobs, binary data, etc), and then you'll have to decide on other factors, like how long texts can be, how large integers can get, and so on. You can also indicate that certain columns (like here, the "id" column) should be auto-incremented (meaning the system will just fill in the next-highest number each time a row is added, guaranteeing uniqueness) and whether the field can be NULL or not (for example, we're allowing 'label' to be NULL if we like). These concepts will be covered more extensively in a separate tutorial, but it's important that you at least be aware that these decisions are there to be made when designing database tables for an application.
Once you've created a database, you can assign permission to new and/or existing users to access the database in different ways. You can actually assign specific permission to just read, write, delete, etc, however, for simplicity, in our example, we're going to create a user and then assign permission to do absolutely anything to the database in question. The MySQL tutorial will explain permissions in more detail, and discuss why you might only assign certain ones to certain users in the future.
To create a new user, select your test database from the left menu and then select the "Privileges" tab at the top in order to reach this screen:
Click "Add a new User" to reach another screen where you can create a user and assign privileges. You'll enter several pieces of information: the User name (you pick it), the Host (you can select 'Local' and it'll fill in 'localhost' for you on your own machine - this is probably what you'll need to do while practicing, unless your setup is different than usual), and then a password that you type twice. Note that you can have phpMyAdmin generate a random password for you, which I've done in the example below. Finally, indicate that your user is getting privileges on that particular table (here my table is called "tutorials", and since I clicked the Privileges tab while viewing that table, it pre-selected this option for me) and then click the 'Check all' link next to the 'Global Privileges' header in the last section. Finally click 'Go' in the bottom right of the form to add your user.
DON'T FORGET! If you have the system auto-generate a password, make sure to copy it somewhere temporarily so that you can use it later. You're going to have to provide this password in your PHP code when you make a connection to the database.
Now that you've created a database and assigned permission to a user to access it, you can make PHP act as that user! PHP has a function called mysql_connect() that does exactly that. Once it creates a connection, it uses other functions such as mysql_select_db() and mysql_query() to find and manipulate your data in order to store and retrieve information for your application. You can find out more at php.net, but the examples that follow will provide you with everything you need to create a connection, run queries, and retrieve output from your database.
Let's get started!
PHP includes a complete set of functions for connecting to and querying a MySQL database. Whenever you intend to perform a query, you must open a connect to the database server, select the database to query, perform any queries that you wish to perform, and then close the connection. Let's look at the connection and closing code first. Before we begin, note that you'll need to provide four pieces of information to the function:
- The Server on which the database resides
- The Name of the database
- The User to log in as (you created this earlier!)
- The Password that is assigned to that User
In the example below, I have set these four values as variables in the lines preceding the connection call, and then (in the fifth line), I use the mysql_connect() function to open a connection:
- We are setting a variable called $dbLink. This is because we'll need a variable to refer to our open connection later.
- We are using the 'or' syntax to indicate what to do if the first instruction (the connection) fails: namely, we are telling the script to die and simply output the line "I cannot connect to the database" rather than trying to continue with anything else.
Once you have made the initial connection, it's time to indicate which database on the server you plan to use:
You may notice that there is some commented code in the example below. I included this to demonstrate that the specific database selection could be done two ways. The first (uncommented) is recommended in case you'd like to say something like
if (mysql_select_db("some_database")) { // Stuff to do in case the database is found }
Otherwise, if you don't need to get a true/false return value for the connection, you can simply perform a query (the one that I commented out) that instructs the server to start using a particular database. From then on out, any queries you run throughout the life of that connection will be assumed to be referring to the database that you have specified. Just keep in mind that there's no harm in going ahead and using mysql_select_db() instead of a direct query, and that's why I've gone ahead and used it in our sample code here.
Once you've connected to a database server and have selected a database to use, you can run any number of queries as seen below. However, once you're done, it's a good idea to go ahead and close the connection:
Failing to close the connection usually won't immediately cause a problem. However, if the connection is repeatedly left open, the open connections can eventually max out and cause the database to stop responding. For this reason, and for many others, it's a good idea to wrap database calls in a custom function of your own. It not only means that you guarantee a call of mysql_close() after each round trip to the database, but it also allows you to package tedious tasks such as checking result counts and compiling arrays of query results, rather than having to code that logic again and again each time you want to use it.
Between the mysql_connect()/mysql_select_db() and mysql_close() calls, you have an open database connection. This connection can be used to send queries to the database that instruct it to make changes and/or return results in an array.
- The query string to run.
- The variable that refers to the server connection that we created.
Let's look at some sample queries that don't involve retrieving any results: creating a table, inserting rows, deleting rows, and dropping (deleting) the table. What is important to note here is the mysql_query() function, which takes two arguments:
Note how I've organized these calls. I've created a string called $query that stores the query I want to run. While I could have embedded the quoted string directly inside the mysql_query() function, I find that using a variable like this is useful for several reasons:
- The code is a little neater because the mysql_query() line doesn't become cluttered.
- It allows me to dynamically piece together a query. For example, I might want to perform a search based on criteria that a user selected in a form on the previous page.
- It leaves me with a variable that I can pass into a query function of my own creation if I decide to use one.
Now that you know how to perform some queries, let's look at the syntax of the SELECT query, and then take a look at how its results are handled in PHP. This is the query that you will need to perform when you want to search for data and return it to your script for use in your application. You might also use it with in more complex subqueries as you learn more about SQL in the future.
Note the $q variable that I set this time when calling mysql_query(). This is not necessary in queries that do not return anything you need to use, but since SELECTs are performed for exactly the purpose of retrieving information that you can use in your application, you'll definitely need to set a variable to hold what the database returns. This variable will contain a pointer to the query results so that you can use the mysql_fetch_array() function to retrieve each row of data returned:
Let's walk through the logic of what we've just done in some general terms:
The $r (or whatever you want to call it) variable that stores the fetched array contains values keyed by the column names of the result set that was returned by the query. So if you call your columns 'id' and 'label' (as I did), then you can refer to the values of those fields in each row as you loop through as you would the keyed values of a normal array: $r['your_column_name'].
When we combine all of the code above, it might look like this:
... and give us this:
| ID | Label |
|---|---|
| 2 | Two |
| 3 | Three |
Recommended next: PHP Web Application Development