PHP & MySQL Query Result Loops

by Jonathan Christensen on Jan 25, 2010, 11:49 PM

Static content is dying, but dynamic sites are emerging left and right. Lets focus on using PHP and merge it with mySQL so we can pull some dynamic data!


The items needed in-order to make this tutorial work would be:

  • PHP 4+
  • mySQL 4+
  • Access to admin in the mySQL database
  • FTP program (if working remotely)
  • Basic knowledge of syntax

  1. First, lets begin with developing the database. This will require creating a new database if you do not already have one available. Let’s create a database called: example.

  2. Once the database is created, we will then need to create a table inside the database called: records. Below is a example SQL code to use:

    1
2
3
4
5

    CREATE TABLE  'example'. 'records' (
'id' INT( 5 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
'name' VARCHAR( 25 ) NOT NULL ,
'email' VARCHAR( 255 ) NOT NULL
) ENGINE = MYISAM

    The following code will create 3 columns in the database: id (int / 5 characters), name (varChar / 25 characters), and email (varChar / 100 characters). Setting id to “auto increment” means it will automatically come up with a new row-number as they are injected. This will allow us later on to pull specific records using this id field.

  3. Next, lets begin with creating our first file: db.inc.php. This will house our connection to the database, please be sure to use your own mySQL logins.

    1
2
3
4
5
6
7
8
9
10
11
12
13
14

    <?
$hostname="localhost";
$mysql_login="root";
$mysql_password="yourpass";
$database="example";
 
if (!($db = mysql_connect($hostname, $mysql_login , $mysql_password))){
  die("Can't connect to mysql.");
}else{
  if (!(mysql_select_db("$database",$db)))  {
    die("Can't connect to db.");
  }
}
?>

    The code there is somewhat more advanced, and too much to go over (im taking for granted you know some basic html/mysql commands).

  4. Now lets create the file that will be pulling the data and listing the resulting data! Create the file: results.php in the same folder as “db.inc.php”. In that file it will have:

    1
2
3
4
5
6
7
8
9
10
11

    <?
require_once('db.inc.php');
 
$query  = "SELECT * FROM records";
$result = mysql_query($query);
 
while($row = mysql_fetch_assoc($result))
{
	print "<div id=\"row\">$row[name] | $row[email]</div>";
}
?>

    After we required the “database” connection file, we then started to code our mySQL “query”: SELECT * FROM records. The asterisk means it will pull all the columns from the database.

    We are then running the mySQL command “mysql_query” which will run that command through the database and grab all the rows and throws them into a array! Once it has them all, it stores them into that variable “$result” for later use in our application.

    PHP’s “while” function is very valuable. It will repeat itself as long as there is records, once it runs out it will stop looping. As you can see in the code, it is looping the DIV “row” each time. Inside that DIV you see two php “variables”: $row[name] and $row[email]. The item that is inside the bracket is the “column” in the database. As said earlier, each row has been turned into an array, here is it turned into a usable item.

  5. Last step is to return to your database program, and create a few records. If you want to just “inject” some SQL please use this:

    1
2
3
4
5
6
7
8

    INSERT INTO  'example'. 'records' (
  'id',
  'name',
  'email'
)
VALUES (
  NULL ,  'jonathan',  'youremail@email.com'
);

Tags: , ,

This entry was posted on Monday, January 25th, 2010 at 11:49 PM by Jonathan and is filed under Design & Development, PHP, Tutorials. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a comment, or trackback from your own site.

Comment

All Fields Required