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.
About these ads

One Response to Retrieving Data from MySQL via PHP

  1. I am not sure where you are getting your info, but good topic. I needs to spend some time learning more or understanding more. Thanks for fantastic info I was looking for this information for my mission.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 33 other followers

%d bloggers like this: