The term “PHP MySQL” refers to MySQL database operations done with the PHP scripting language**.** PHP is often used to fetch data from and insert data into a server’s database. A database application commonly used with PHP is MySQL. In fact, PHP has functions built into its language that work specifically with MySQL.
Your first task in beginning to develop PHP scripts that call on MySQL functions is to set up your development environment. Begin creating that environment by getting a free account with a web host that runs both PHP and MySQL. You can find such a host at this address.
Once you have an account with a web host, it’s a good idea to get an integrated development environment (IDE) that helps you write PHP code. Integrated development environments help you write syntactically correct code, and also provide pop-up windows describing the functions you’re currently typing. You can download free PHP IDEs from NetBeans.org, Eclipse.org, Apatana.org and other sites.
Creating a database
You’ll need a database to execute your PHP code against, so creating that database is your next step. Because configurations vary among web hosts, you’ll need to find out from yours the specific passwords and procedures needed to create the initial database.
Once you’ve created the database, let’s apply a PHP script to create a table for it, and then some sample data for the table.
Paste the following code into your IDE.
Replace the capitalized text with your logon information, which your web host will provide to you.
Upload the script to your server, then execute it by entering its web address in a browser. You should see a “1” if the script succeeded in creating a table. If you see another number, verify that you entered the script correctly, and also verify your logon information.
Open the MySQL administration console on your web host, and click the link for your database. The resulting page should display a new table called “movies.” That’s the table your PHP script just created.
Create PHP code to insert data into the new table. Make a copy of the script from the previous section. Select the statement that begins with “$query=“CREATE TABLE…”, then paste the following statement over it:
$query=“INSERT into movies (Title, Director, YearReleased) VALUES (‘GroundHog Day’, ‘ramis’, 1993);";
Rerun the script using the instructions from the previous example, and access your web host’s MySQL administration window to verify that the data was inserted in the movies table.
Repeat the steps in this section (“Inserting Data”) to insert several rows of data into the movies table. However, change the data for each insertion; use a different movie each time. (Consult the Internet Movie Database for sample data.)
Try a DELETE statement
Try a delete on one of the table’s rows. Use the INSERT script to create a row with info for a bogus (or otherwise forgettable) movie. Then swap out the “INSERT INTO…” statement from the previous example, with this one:
$query=“DELETE from movies where (Title =‘Even Cowgirls Get the Blues’)";
Replace the title in the statement just given, with the title of your chosen movie.
After you’ve inserted several rows of movie information into the table, create PHP code to retrieve that information. As before, make a fresh copy of the preceding section’s PHP script, so you can re-use that script as needed.
Paste the following code into your IDE, then run it as before. Each row of your movie table should display in your browser.
Putting it all together
Combine all the PHP MySQL operations you’ve learned so far into one program. The following program is a single web page form that lets the user enter any SQL string, then submit it for execution by PHP against the movie database.
Paste the following code into a new PHP script, save the script as “queryStuff.php,” then upload it to your server and try it out.