Retrieving data from MySQL basic PHP

[WongFei]

Eric Ressler of ZuniWeb

http://www.zuniweb.com
http://forum.zuniweb.com

This article will show you how to retrieve and utilize data from a MySQL Database using simple PHP programming. This article assumes that you are familiar with MySQL tables and already have a table setup with information. You should also have a basic understanding of common PHP functions. If your table is empty, it would be best to add at least one bunk entry for testing purposes.

The method I most commonly use can be broken down into 3 portions:

Connect to the database
Pull the information from the database
Utilize the information received

The first step is very simple, but requires information about the database which you can get from your host. Since it is necessary to connect to your database before you make any request, it is useful to save the above code to a file called 'config.php' so you don't have to include all of the text each time you want to connect. It also prevents you from having to update every .php page you have if you decide to change your database settings.


1 <?
2 //define the host, username, and password
3 $host = "hostname";//usually localhost
4 $username = "username";
5 $password = "password";
6
7 //connect to the database
8 $dbcx = mysql_connect($host, $username, $password);
9 ?>



If all of the information you supplied is valid, you should now be connected to the database.

The next step is to retrieve information from a table on the database. Let's say you have a table which resemebles the following:

id username password
1 pinky mouseguts
2 Floyd grassblades
3 Train wisconsin

Here is how you would retrieve the information from the table:


1 include "config.php";//include database connect settings
2
3 $sql = mysql_query("SELECT * FROM `tablename`") or die(mysql_error());//retrieves all of the information from the table


If you wanted to select only the usernames, the code would look like this:


1 $sql = mysql_query("SELECT `username` FROM `tablename`") or die(mysql_error());//retrieves all of the usernames from the table


Or, if you wanted to retrieve only the three newest values, the code would look like this:


1 $sql = mysql_query("SELECT * FROM `tablename` LIMIT 3 ORDBER BY `id` DESC LIMIT 3") or die(mysql_error());//retrieves the three most recent entries from the table


But what if you want to retrieve all of the information for a specific user? This can also be easily achieved:


1 $username = "Pinky";//specify the user
2
3 $sql = mysql_query("SELECT * FROM `tablename` WHERE `username` = '$username'") or die(mysql_error());//retrieves all information for the user 'Pinky'


Now that we've covered some methods of acquiring the data, it's time to manipulate it. Let's say you wanted to display the ten newest users in the table. This could be achieved using the following code:


1 <?
2 include "config.php";//include database connect settings
3
4 $sql = mysql_query("SELECT * FROM `tablename` ORDER BY `id` DESC LIMIT 10") or die(mysql_error());//retrieves the ten newest entries
5
6 while($spit = mysql_fetch_array($sql))//creates a conditional loop and an array for the results
7 {
8 $username = $spit["username"];//stores the specific field 'username' from the table as a variable
9 $password = $spit["password"];//stores the specific field 'password' from the table as a variable
10
11 echo "Username: $username <br>";//displays the username
12 echo "Password:&#160; $password <br><br>";//displays the password
13 }
14 ?>


The above code would output the following from our table above:

username: pinky
password: mouseguts

username: Floyd
password: grassblades

username: Train
password: wisconsin


Although this seems basic, it is the very foundation of dynamic content.

For more information regarding PHP & MySQL, please refer to our Zuniweb Articles Forum.

Visit and join the zuniweb design forums: http://forum.zuniweb.com

Check out the source of this article: http://www.zuniweb.com/forum/index.php?PHPSESSID=c65898df4d885f0dff71ca5bc5101b8b&topic=7.0