In this tutorial I’m going to show you the proper way to connect to a MySQL database, and how to input and retrieve data from it using PHP. Knowing how to properly query a database is a basic function of any programming language, and so it is important that you know how to do it with PHP and MySQL. Here’s the video I created for you guys:

As I promised in the video, I told you I’d provide you with step by step instructions including the source code, so here goes:

Setting Up Your Database

Stop rushing…Stop rushing…Before you get started coding you have to set up your database If you’re still in a rush go ahead and download the already set up database here: db_source Although knowing how to set up your MySQL database is important, and doing it will help you understand more about what’s happening when we query the database using PHP.

1. Make Sure to Create a user and password in phpMyAdmin.

2. Create Your Database named: tutorial

3. Create a Table with the name: animals

4. Create 5 Fields for your table:

   • Picture – Text Field
   • Name – Text Field
   • Species – Text Field
   • Gender – Char(1 character)
   • ID – INT(5 characters…also make it your primary key)

5. Input our data into the table. Click Insert on the phpMyAdmin table menu then input this data:

Entry 1
Picture: <img src='gizmo.jpg' />
Name: Gizmo
Species: Dog
Gender: F
ID: 00001
Entry 2
Picture: <img src='fluffy.jpg' />
Name: Fluffy
Species: Cat
Gender: F
ID: 00002

Retrieving Data

Yay! It’s time to dig into some code. Go ahead and create a folder which contains the 3 pictures I provided you with earlier, and create a file called: mysql-retrieve.php. Now open up mysql-retrieve.php and get ready to code

1. Set up your file by including the php opening and closing tags:

<?php
?>

Make sure that all code stays in between these tags. You do not have to have body, header, or html tags…all code goes in between the php tags.

2. Now open the MySQL Connection:

mysql_connect("localhost", "Put_Your_Username_HERE", "Put_Your_Password_HERE") or die(mysql_error());
mysql_select_db("tutorial") or die(mysql_error());

3. Query The Database:

$data = mysql_query("SELECT * FROM animals")
or die(mysql_error());

4. Fetch Your Data:

while($info = mysql_fetch_array( $data ))

The loop is important because it allows all of the entries from the table to be displayed. If there is no loop statement, then only the first record will be pulled from the database.

5. Print The Data

{
print "<div style='text-align:left; padding-bottom:30px; margin-left:20px;'>";
print "Picture:&nbsp;" . $info["Picture"] . "<br />";
print "Name:&nbsp;" . $info["Name"] . "<br />";
print "Species:&nbsp;" . $info["Species"] . "<br />";
print "Gender:&nbsp;" . $info["Gender"] . "<br />";
print "ID:&nbsp;" . $info["ID"];
print "</div>";
}

6. Save & TEST IT! If You did everything right you should see 2 entries on the screen.

Inputting Data

Okay, now that you’ve learned how to retrieve your data…let’s learn how to input data using php.

1. Create a file in your folder called: mysql-input.php and open it

2. Set Up Your PHP opening and closing tags:

<?php
?>

All code goes between the opening and closing tags!

3. Connect To Your Database:

mysql_connect("localhost", "Your_Username", "Your_Password") or die(mysql_error());
mysql_select_db("tutorial") or die(mysql_error());

4. Query Your Database:

mysql_query("INSERT INTO animals VALUES ( '<img src=\'ralph.jpg\' />', 'Ralph', 'Dog', 'M', 00003)");

5. Print a Confirmation Statement

print "Your table has been populated";

6. You’re Finished!

In Conclusion

I hope this tutorial showed you guys just how easy it is to manipulate data using PHP and MySQL. If you have any questions feel free to use the contact form located on our main menu, or just add a comment below.

Thanks For Reading