Tim's Tutorials : PHP : Functions and Data Objects with PHP

This tutorial will be a little longer than some of my others.. So grab a beer, put on your favorite Blink 182 or Van Halen CD, and we'll plow through this stuff!

The basic overview is this. We will be creating an include file with a PHP function and SQL query in it, which will pull some data out of the database and place it into a data object for use in a web page. Then we will create a PHP web page that makes use of the data object. Yay!

The reason behind creating a separate function to access the database, then pull the data and place it into a data object, is so that anywhere in our web site we need the same data we can just include and call this function without recreating and duplicating a lot of code.

Note: In another MySQL tutorial I show how to create a database in MySQL, add a table to it, and then populate it with some data and records from the command line. You will probably want to first go through that tutorial before this one since we will be querying that example database.

View the tutorial: Creating a Database and Table in MySQL

Since lots of pages in our web site may need to connect to the MySQL database it is convenient to create a "constants.php" file to keep the database connection code, and any other code and variables that are commonly used throughout the web site. This way if you change the database password you can change it in one place instead of dozens of individual pages.

The following sample shows the database connection code. We can then include the constants.php file in any page where we need a connection to the database.

In the following code replace `myUsername' and `myPassword' in the connection string with your own MySQL server username and password. Then also replace `myDatabase' in the next line to the actual name of your own database. Also, if your MySQL database is not on the same server as your web site you should replace `localhost' with your MySQL server address or URL.

<?php
// connect to the database
mysql_connect("localhost", "myUsername", "myPassword") or die(mysql_error());

// choose the database to use
mysql_select_db("myDatabase") or die(mysql_error());
?>

You'll notice both the mysql_connect() and mysql_select_db() functions are each followed by the die() function with mysql_error() as an argument. This is so that if for some reason either fails an error will print to the screen with more details for troubleshooting.

We will put our SQL database query and PHP function code in a file called "getPeople.php", which we will then include in our web page. The code is commented to better explain what it's doing.

To begin we create a SQL query (assigned to a variable called $sql) to pull all people in the `people' table, or just an individual if a specific `peopleID' argument is supplied.

<?php
// function will take one (optional) argument if a specific person is needed
function getPeople( $peopleID = "" ) {
   // define database query and assign to a variable
   $sql = "
      select   peopleID,
               firstName,
               lastName,
               birthDate,
               recstate
      from     people
      where    recstate = 1";
   
   // if a specific person was queried
   if(!null( $peopleID )) {
      $sql .= " and peopleID = " . $peopleID;
   }

Then we will run our query through the database using the PHP `mysql_query()' function. You will notice we provide our $sql variable defined earlier, containing our query, as the sole argument.

Note: During development, and for troubleshooting, you can have your die() function also display the SQL query right after the error in your web page. To make it more clear to read you can also use the nl2br() function (it means "new line" to "<br>") to show it with any line breaks you made in the code. This is of course optional though. (Remember to remove it for live code though!)

   // run query through database
   $result = mysql_query($sql) or die( mysql_error() . "<p>" . nl2br($sql) );

We can then take the results from running our query through the database and place them into a data object. We do this by looping through the results using a `while()' loop, assigning each result to an array for that database field, and hanging it all off a data object we'll call $peopleObj. Don't worry, I'll explain the object/array stuff a little better shortly.

   // loop through results and place into a data object
   $counter = 0;
   while($resultDataObj = mysql_fetch_object( $result )) {
      $peopleObj->peopleID[$counter]  = $resultDataObj->peopleID;
      $peopleObj->firstName[$counter] = $resultDataObj->firstName;
      $peopleObj->lastName[$counter]  = $resultDataObj->lastName;
      $peopleObj->birthDate[$counter] = $resultDataObj->birthDate;
      $peopleObj->recstate[$counter]  = $resultDataObj->recstate;
      
      // increment the counter each loop through
      $counter++;
   }

And finally we will return our $peopleObj whenever this function is called from within a web page. Then we can loop through it and display its contents on the page. Woo?

   // return data object when function is called
   return $peopleObj;
}
?>

There are lots of technical explanations available on objects and arrays, but basically for our uses it puts all of our query results into one thing (a variable with a lot of pieces of data sort of) we can use in the page.

Basically our "data object" (named `peopleObj' in our example) is a container of sorts that holds a number of arrays. Each array then holds multiple results of one database field.

In our example representation to the right our data object `peopleObj' contains 5 arrays; peopleID, firstName, lastName, birthDate and recstate.

Each array in our example then contains 3 elements. These array elements are addressed starting with 0 and incrementing for the next array value. To better explain this, consider the following example of what they each contain:

  $peopleObj->firstName[0]  = "Tim"
  $peopleObj->firstName[1]  = "Edward"
  $peopleObj->firstName[2]  = "Billy"

Basically, the first time we looped through our SQL query results we put everything to do with "Tim Patterson" into array element 0, then everything to do with "Edward Van Halen" into array element 1, and everything to do with "Billy Sheehan" into array element 2.

  $peopleObj->peopleID[2]   = "3"
  $peopleObj->firstName[2] = "Billy"
  $peopleObj->lastName[2]   = "Sheehan"
  $peopleObj->birthDate[2] = "1953-03-19"
  $peopleObj->recstate[2]   = "1"

Now we can go ahead and use our new function in a web page we'll call "displayPeople.php" to show what it's useful for. I usually include any required files at or near the top of a web page, and then start the page header stuff.

<?php
/* include getPeople() function to display people
 * then assign what the getPeople() function returns to the variable $peopleObj
 */
include( $_SERVER['DOCUMENT_ROOT'] . "/includes/getPeople.php" );
$peopleObj = getPeople();
?>
<html>
<head>
<title>Test People Page!</title>
</head>
<body bgcolor="#FFFFFF">

To show our example data more clearly we'll lay it out in an HTML table with a border. We will loop through the data we fetched out of the database with our SQL query and display it in the table rows using a for() loop as follows.

<table border="1">
  <!-- first rows displays column headers in bold -->
  <tr>
    <td><b> peopleID </b></td>
    <td><b> firstName </b></td>
    <td><b> lastName </b></td>
    <td><b> birthDate </b></td>
    <td><b> recstate </b></td>
  </tr>

  <?php
  /* set the $counter to 0 to start
   * then loop through all records (using PHP count() function)
   * then increment the counter by 1 each loop with $counter++
   */
  for( $counter = 0; $counter < count($peopleObj->peopleID); $counter++ ) {
    echo "<tr>";
    echo "<td>" . $peopleObj->peopleID[$counter] . "</td>";
    echo "<td>" . $peopleObj->firstName[$counter] . "</td>";
    echo "<td>" . $peopleObj->lastName[$counter] . "</td>";
    echo "<td>" . $peopleObj->birthDate[$counter] . "</td>";
    echo "<td>" . $peopleObj->recstate[$counter] . "</td>";
    echo "</tr>";
  }
  ?>
</table>

</body>
</html>

Each time the for() loop cycles through the counter advances (from 0, to 1, to 2, etc..) and a new row, or record, is written to the page with the code above. It should then display something like the following.

peopleID	firstName	lastName	birthDate	recstate
1	Tim	Patterson	1971-05-02	1
2	Edward	Van Halen	1955-01-26	1
3	Billy	Sheehan	1953-03-19	1

This is just one example on how to use things like data objects and arrays in PHP to better organize your SQL queries and PHP code that might get repetetive. By separating things like SQL queries and functions into a central /includes directory, and including them in a page as needed you can make your web site a little easier to maintain.

And keeping your database connection string in one central file, and including it in a page as needed, will save you lots of trouble the first time you change your database password. Also, if you can put your constants.php file containing your database connection info a level below the web directory (and include with the direct path on the system instead of $_SERVER['DOCUMENT_ROOT']) that would be much more secure too!

About Me

Miscellaneous

Geek Stuff

Skydiving

Geek Stuff : Tutorials : PHP : Functions and Data Objects with PHP