Pin Me

PHP MySQL Basics

written by: Darrin Koltow•edited by: Michele McDonough•updated: 2/16/2010

This article will show you how to use PHP to execute SELECTS, INSERTS, UPDATES and DELETES against a MySQL database.

  • slide 1 of 6

    PHP MySQL

    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.

    1 

  • slide 2 of 6

    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.

    <?php

    mysql_connect("YOUR_USERNAME.YOUR_HOSTS_MYSQL_WEB_ADDRESS", "YOUR_USERNAME", "YOUR_PASSWORD") or die(mysql_error());

    mysql_select_db("YOUR_DATABASE_NAME") or die(mysql_error());

    $query="CREATE TABLE movies (Title varchar(80), Director varchar(80), YearReleased int);";

    $rc = mysql_query($query);

    echo $rc;

    ?>

    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.

  • slide 3 of 6

    Inserting data

    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.)

  • slide 4 of 6

    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.

  • slide 5 of 6

    Selecting data

    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.

    <?php

    mysql_connect("YOUR_USERNAME.YOUR_HOSTS_MYSQL_WEB_ADDRESS", "YOUR_USERNAME", "YOUR_PASSWORD") or die(mysql_error());

    mysql_select_db("YOUR_DATABASE_NAME") or die(mysql_error());

    $query="SELECT * from movies";

    $rc = mysql_query($query);

    while ($movie = mysql_fetch_assoc($rc)) {

    echo $movie['Title'].", ";

    echo $movie['Director'].", ";

    echo $movie['YearReleased']."<br>";

    }

    mysql_free_result($rc);

    ?>

  • slide 6 of 6

    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.

    <?php

    showForm();

    //Pull the query off the form and hand it to MySQL

    $q = $_POST["query"];

    if ($q != "" ) {

    doQuery($q); // and show its results

    }

    ////////////////////////////////

    function doQuery ($q) {

    mysql_connect("YOUR_USERNAME.YOUR_HOSTS_MYSQL_WEB_ADDRESS", "YOUR_USERNAME", "YOUR_PASSWORD") or die(mysql_error());

    mysql_select_db("YOUR_DATABASE_NAME") or die(mysql_error());

    $rc = mysql_query( $q);

    while ($movie = mysql_fetch_assoc($rc)) {

    $k = array_keys($movie);

    foreach ($k as $fieldname) {

    echo $fieldname.": ".$movie[$fieldname].", ";

    }

    echo "<br>";

    }

    mysql_free_result($rc);

    }//end, doQuery

    function showForm () {

    echo '

    <html>

    <head>

    <title></title>

    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

    </head>

    <body>

    <form action="queryStuff.php" method="POST" >

    <textarea name="query" >

    select * from movies;

    </textarea>

    <br>

    <input type="submit" >

    </form>

    </body>

    </html> ';

    }

    ?>

    Read more about integrating MySQL with PHP here.

    References

    The PHP Hypertext Processor Home Page