PHP MySQL Tutorial

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    PHP MySQL Tutorial

    Connect to MySQL

    Interacting with MySQL makes PHP a far more powerful tool. In this tutorial we will go through some of the most common ways PHP interacts with MySQL. To follow along with what we are doing, you will need to create a database table by executing this command:

    Code:
    CREATE TABLE friends (name VARCHAR(30), fav_color VARCHAR(30), fav_food VARCHAR(30), pet VARCHAR(30)); 
    INSERT INTO friends VALUES ( "Rose", "Pink", "Tacos", "Cat" ), ( "Bradley", "Blue", "Potatoes", "Frog" ), ( "Marie", "Black", "Popcorn", "Dog" ), ( "Ann", "Orange", "Soup", "Cat" )
    This will create a table for us to work with, that has friends' names, favorite colors, favorite foods, and pets.

    The first thing we need to do in our PHP file is connect to the database. We do that using this code:

    PHP Code:
    <?php 
    // Connects to your Database 
    mysql_connect("your.hostaddress.com""username""password") or die(mysql_error()); 
    mysql_select_db("Database_Name") or die(mysql_error()); 
    ?>
    Of course you will replace server, username, password, and Database_Name with the information relevant to your site. If you are unsure what these values are, contact your hosting provider.

    Retrieve Data

    Next we will retrieve the information from the database table we created called "friends"

    Code:
    // Collects data from "friends" table 
    $data = mysql_query("SELECT * FROM friends") 
    or die(mysql_error());
    And we will then temporally put this information into an array to use:

    Code:
    // puts the "friends" info into the $info array 
    $info = mysql_fetch_array( $data );
    Now let's print out the data to see if it worked:

    PHP Code:
    // Print out the contents of the entry 
    Print "<b>Name:</b> ".$info['name'] . " "
    Print 
    "<b>Pet:</b> ".$info['pet'] . " <br>"
    However this will only give us the first entry in our database. In order to retrieve all the information, we need to make this a loop. Here is an example:

    PHP Code:
    while($info mysql_fetch_array$data )) 

    Print 
    "<b>Name:</b> ".$info['name'] . " "
    Print 
    "<b>Pet:</b> ".$info['pet'] . " <br>"

    So let's put all the these ideas together to create a nicely formatted table with this final php code:

    PHP Code:
    <?php 
    // Connects to your Database 
    mysql_connect("your.hostaddress.com""username""password") or die(mysql_error()); 
    mysql_select_db("Database_Name") or die(mysql_error()); 
    $data mysql_query("SELECT * FROM friends"
    or die(
    mysql_error()); 
    Print 
    "<table border cellpadding=3>"
    while(
    $info mysql_fetch_array$data )) 

    Print 
    "<tr>"
    Print 
    "<th>Name:</th> <td>".$info['name'] . "</td> "
    Print 
    "<th>Pet:</th> <td>".$info['pet'] . " </td></tr>"

    Print 
    "</table>"
    ?>
    SQL Queries with PHP

    Now that you have done one query, you can do more complicated queries using the same basic syntax. If you have forgotten the queries, you can review them in the MySQL glossary.

    Let's try to do a query of our database for people who have cats for a pet. We will do this by adding a WHERE clause to set pet equal to Cat.

    PHP Code:
    <?php 
    // Connects to your Database 
    mysql_connect("your.hostaddress.com""username""password") or die(mysql_error()); 
    mysql_select_db("Database_Name") or die(mysql_error()); 
    $data mysql_query("SELECT * FROM friends WHERE pet='Cat'"
    or die(
    mysql_error()); 
    Print 
    "<table border cellpadding=3>"
    while(
    $info mysql_fetch_array$data )) 

    Print 
    "<tr>"
    Print 
    "<th>Name:</th> <td>".$info['name'] . "</td> "
    Print 
    "<th>Color:</th> <td>".$info['fav_color'] . "</td> "
    Print 
    "<th>Food:</th> <td>".$info['fav_food'] . "</td> "
    Print 
    "<th>Pet:</th> <td>".$info['pet'] . " </td></tr>"

    Print 
    "</table>"
    ?>
    Create Tables

    Following this same structure, we can connect to a database and create new tables. At the end we will print a line, so we know that it is done executing:

    PHP Code:
    <?php 
    // Connects to your Database 
    mysql_connect("your.hostaddress.com""username""password") or die(mysql_error()); 
    mysql_select_db("Database_Name") or die(mysql_error()); 
    mysql_query("CREATE TABLE tablename ( name VARCHAR(30), 
    age INT, car VARCHAR(30))"
    ); 

    Print 
    "Your table has been created"
    ?>
    I find this method is often used when installing a PHP program someone else has written. Often an install file includes a way for the user to update the MySQL database from the browser. This allows people less familiar with the code to install the program more easily.

    Insert Into Tables

    We can use the same method of using SQL commands to populate our database as we did to create it. Here is an example:

    PHP Code:
    <?php 
    // Connects to your Database 
    mysql_connect("your.hostaddress.com""username""password") or die(mysql_error()); mysql_select_db("Database_Name") or die(mysql_error()); 
    mysql_query("INSERT INTO tablename VALUES ( 'Bill', 29, 'Ford' ), ( 'Mike', 16, 'Beetle' ), ( 'Alisa', 36, 'Van' )"); 

    Print 
    "Your table has been populated"
    ?>
    Thank you for reading.
    BakGat
    Code:
    class Counter {
    public:
      void Count();
      int  ReadDisplay();
    private:
      int  CurrentCount;
    };








    Back up my hard drive? How do I put it in reverse?
    My Community
    BakGat
    sigpic

    #2
    Nice tutorial thankx frnd

    Comment


      #3
      lol remove the mysql_error from mysql_connect and mysql_select_db...don't show your database username ;)
      www.inbuzunar.mobi - Your mobile portal pocket

      Comment

      Working...
      X