Retrieving Data from MySQL via PHP

So far, we have looked at connecting to a MySQL database via Perl and Python. Next, we will look at connecting via PHP. I am assuming that you have already installed PHP or have the ability to run PHP scripts on your server, and that you have MySQL installed as well.

This example will use the same address table from the previous post, Inserting Data into MySQL with Perl.

Here is the SQL statement that we used to create the table:

SET NAMES latin1;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `address` (
`name_first` varchar(30) NOT NULL,
`name_last` varchar(30) NOT NULL,
`address_01` varchar(40) NOT NULL,
`address_02` varchar(40) NOT NULL,
`address_city` varchar(30) NOT NULL,
`address_state` varchar(20) NOT NULL,
`address_postal_code` varchar(12) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

SET FOREIGN_KEY_CHECKS = 1;

Be sure to populate your address table with some dummy data.

insert into `address` values('John','Smith','100 Main Street','Suite 500','Buffalo','NY','14201'),
 ('Dave','Jones','500 Second Avenue','Suite 100','Atlanta','GA','30303'),
 ('Tom','Watson','123 Golf Course Lane','Suite 1','Macon','GA','31066'),
 ('Jack','Nicklaus','400 Laurel Oak Dr','Suite 49','Suwanee','GA','31044');

Here is a very simple PHP script which will connect to MySQL and display the results of a query. We aren’t going to build any HTML tables to display the data – we are simply going to perform a query and return the results. Be sure to change the database values in the PHP script for the variables host_name_or_IP_Address, username, password and database_name. Insert the following script into your HTML body tag.

<?php

//connect to the database
mysql_connect ("host_name_or_IP_Address","username","password") or die ('Cannot connect to MySQL: ' . mysql_error());
mysql_select_db ("database_name") or die ('Cannot connect to the database: ' . mysql_error());

//query
$query = mysql_query("select name_first, name_last from address") or die ('Query is invalid: ' . mysql_error());

//write the results

while ($row = mysql_fetch_array($query)) {
	echo $row['name_first'] . " " . $row['name_last'] . "
";
	
// close the loop
}

?>

Here are the results when executed in a browser:

 

---

	Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world's most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
	Tony is the author of Twenty Forty-Four: The League of Patriots   Visit http://2044thebook.com for more information.