How to display MySQL rows using PHP

PHP is a great dynamic web language and when combined with database structures such as MySQL, the true power of the language begins to show. This tutorial will cover the basic elements in displaying data from a MySQL database using PHP.
Download the full tutorial (config.php, show.php, readme.txt, db.sql)

First, lets create our config.php file to store our database information:

<?php
$host = "localhost"; 

if you’re not sure, it’s probably localhost

<code>
$user = "your_username"; 
</code>

your mysql username

<code>$pw  =  "your_password"; </code>

your mysql username password

<code>$db     = "database_name"; </code>

the name of the database we’re going to be pulling data from

<code>mysql_connect("$host", "$user", "$pw");
mysql_select_db("$db");

$query = "CREATE TABLE table_name( `id` INT (11),
	`first_name` VARCHAR (75),
	`last_name` VARCHAR (150),
	`food` VARCHAR (150),
	`video_game` VARCHAR (150),
	`phone` INT (10),
         PRIMARY KEY(id))";

$query .= "
INSERT INTO `friends` (`id`, `first_name`, `last_name`,
`food`, `video_game`, `phone`)
VALUES('1','Joe','Smith','Sushi','Skate 2','1234567890');

INSERT INTO `friends` (`id`, `first_name`, `last_name`, 
`food`, `video_game`, `phone`)
VALUES('2','Erin','Lowe','Pizza','NBA2K11','2147483647');
";

$result = mysql_query($query)
?></code>

In case the table has not been created yet, the table structure is defined and executed here using the $query and $result variables.
The full code:

&lt;?php
$host = "localhost"; 
$user = "your_username"; 
$pw  =  "your_password";
$db     = "database_name";

mysql_connect("$host", "$user", "$pw");
mysql_select_db("$db");

$query = 'CREATE TABLE table_name( '.
         'id INT NOT NULL AUTO_INCREMENT, '.
         'name VARCHAR(20) NOT NULL, '.
         'PRIMARY KEY(id))';

$result = mysql_query($query)
?&gt;

Now it’s time to pull the entires from the database. Start off by creating a new file, lets call it show.php.

<code>
&lt;?php
include('config.php');

$sql = "SELECT * FROM `table`";
 $result = mysql_query($sql);
  while($r = mysql_fetch_array($result)) {
   $id = $r['id'];
   $name = $r['name'];
     echo "$id - $name<br>";
  }
?&gt;
</code>

Understanding this code is simple. First, we include the config.php file we wrote before so that we don’t have to re-state the database information on every page.

Now the first query, $sql. The $sql variable will hold the MySQL query. The * after SELECT tells the database to retrieve all of the columns in the table.

The next variable, $result, will hold the executed MySQL query. When setting $result = mysql_query($sql), the $result variable will execute the query $sql anytime it is called.

Next, we use a while loop to cycle through the query in order to store all of the results. We declare the $r variable to represent the “row” in the database table using mysql_fetch_array. This way all of the data will be stored as an array, which allows for the following lines to make sense.

<code>
$id = $r['id'];
$name = $r['name'];
 echo "$id - $name<br>";
</code>

We set the $id variable to the id column and the $name variable to the name column. After that, we simply “echo out” our results. That’s an in depth explanation of a simple MySQL query using PHP.

Download the full tutorial (config.php, show.php, readme.txt)

This article has 1 comments

  1. Pingback: Using LIMIT & WHERE clauses with MySQL & PHP at Michael Loring

Leave a Comment

Your email address will not be published. Required fields are marked *