Day Four: Displaying A Result Set with PHP

By now, we should know a little bit about building a web page with PHP. We also should have a very basic understanding of databases and SQL.  

Setting a Database Connection

Getting Started

It is easy to get started with relational programming. There are many low cost databases on the market. Many enterprise quality databases, like Oracle, will let students install the program for learning purposes free of charge.

If you use Windows, you might want to try Microsoft Access. If you prefer Linux box, you would probably want to start with mySQL.

It is probably best to install more than one database program on your local system; so that you can 

Before we can run queries, we must open a connection to the database. To do this you will need to know your user name, password and location of your mySQL database. You will then need to run the commands mysql_connect() to open a connection the database, and mysql_select_db() to connect to the database.

  

I had difficulties setting up my connection string. In this class I want to focus on code and not on debugging the database connection. So I will skip this part of the process.

Personally, I don't like including the connection string in my programs. I put the connection string in its own private secured include file. By having my connection string in only one place, it is easy to make periodic changes to the password. I've found that hiding my connection makes my code cleaner since I do not need to fill it with connection information. 

I will assume that you are doing the same, and will focus on the code to write queries.

The commands for writing queries are: mysql_query($sql) and mysql_fetch_row($result). It is also helpful to know the mysql_error() function. This function simply returns any error messages from the mySQL server. The pattern for writing a query is as follows:

$sql="SELECT column1, column2 FROM Table WHERE key=1";
if ($result=mysql_query($sql)) {
  if ($row=mysql_fetch_row($result)) {
    echo "Column 1 is ".$row[0]."<br>\n";
    echo "Column 2 is ".$row[1]."<br>\n";
  }
} else {
  echo "<!-- SQL Error ".mysql_error()." -->";
}

Writing queries is basically a matter of remembering the above syntax. To be honest, the above code is doing some strange things. The code ($result=mysql_query($sql)) sends the SQL statement to the database server. If the operation is a success, then the program returns true. If it fails, the program returns false. I use an else statement to catch errors from the SQL command, and echo the error as an HTML comment.

Now for the inner part of the structure. The command mysql_fetch_row($result) returns a row of data. It puts the row in a PHP array. As you may recall from a previous discussion, the index of an array begins with 0. This query returned two columns: column1 will be in $row[0] and column2 will be in $row[1].


PHP returns data from the query in named pairs. You can also access the results of a query with the syntax $row["column1"] and $row["column2"]. I had this method fail on me once; so I have been referencing the columns by index.

As I mentioned in discussion on database theory, a SQL SELECT statement always returns a table. In our first example, we only returned the first row of data from the query. In this second example, we will loop through an entire dataset, and print the data in a table. 

$sql="SELECT column1, column2 FROM Table WHERE key=1";
// open the table and print a header row.
echo "<table align=center width=300>"
echo "<tr><th>Column 1</th><th>Column 2</th></tr>\n";
if ($result=mysql_query($sql)) {
  while ($row=mysql_fetch_row($result)) {
    echo "<tr><td>".$row[0]."</td>";
    echo "<td>".$row[1]."</td></tr>\n";
  }
} else {
  echo "<!-- SQL Error ".mysql_error()." -->";
}
echo "</table>";

I added some code to open and close the table. However, the only really big difference between the two programs is that I replaced the if statement in the inner query with a while loop. The while loop will loop through all the rows of the dataset, and return false when it reaches the end.

What I would like to do at this point is stop, and have every one execute a few queries, and practice printing the information in different HTML formats. 

index -- next
descmath
, plusroot, search, rgreetings, sponsors