PHP and MySQL are one of the most popular methods for creating dynamic websites.  PHP stands for Hypertext Preprocessor and is a service side scripting language.  MySQL is an open source relational database management system.  In this article, you will learn how to access a MySQL database using PHP.

Connecting with MySQL

To connect and work with MySQL from PHP, you will need the host name, database name, username, and password.  The host name is typically set to localhost or an internal ip address.

The mysql_connect function will connect to the database at hostname under the credentials of the entered username and password.  The mysql_select_db function will set the named database as the default database for queries in this session.  Each of the passed parameters can be set as variables.  In small applications, I will typically set a variable for each of the host, database name, user name, and password at the top of the script.  If you are working on a larger project or working with multiple developers, I suggest using a common set of functions and including that php file.

Getting Information From the Database

To get information from the database, you will need to create a query in SQL.  SQL stands for structured query language and is the language used to interact with most modern databases.  The retrieve data, the select query is used.

SELECT column1, column2, COLUMN 3 FROM tablename WHERE column2 = somevalue ORDER BY column1 DESC

The select keyword determines what data to retrieve from the database.  The column1, column2, and column3 are to be replaced by the columns you wish to retrieve or an asterisk (*) can be used to retrieve all columns.  The from table describes which table to select the data from.  The where clause is used to restrict the dataset according to any number of conditions.  The order by clause is used to specify the ordering of the returned result set.

Now, let's take a look at the PHP code required to retrieve the information from the database.

In the above PHP code, the SQL select statement we looked at is stored in the $sql variable.  The mysql_query function is used to execute the query against the database and retrieve the data.  The $r variable stores the data retrieved from the database.

Displaying the Retrieved Data in a Web Page

The data can be displayed in a web page in any number of ways you might find useful.

The above code will display a simple table of the data returned from the SQL query.  The mysql_fetch_object function will return a PHP object of data for each row returned.  The while loop will access each row until the end of the result set is reached.  The echo statement displays the html code to the browser to render.

Closing the Connection

mysql_close();

Make sure to run the mysql_close function when you are done with the connection.   Closing the connection will free up memory and resources to the database and server.  This will lead to a faster response time for loading the web page.