PHP – Writing Output from SQL Database to Browser

Content of the lesson:

Introduction

In the previous lessons we learnt about the construction of a SQL query (for example function SELECT) and the way how to connect PHP to MySQL database. In this lesson we are going to learn how to write the content of a SQL table inside our browser via PHP.

To be able to use the examples shown in this lesson you need the database which we used in the previous lessons. The whole database can be downloaded in this file: databaze.txt.

Sending a Query via Function MySQL_Query

We will return back to the query for selecting articles from the database (the process of writing the content of table inside browser will be explained on this sample). This query was described in the lesson SQL and Command SELECT. Our task is to choose headlines (heading), short descriptions (descr) and the creation dates (createdate) of all articles in our database. The result is sorted according to the creation date and then the result is displayed.

SELECT 
    heading, 
    descr, 
    createdate 
FROM 
    renome_tarticle 
ORDER BY 
    createdate

For sending the query we should use the function MySQL_Query which needs one argument - our variable with the query.

Before starting the communication with database you have to connect to it. Use the function dbconn which we created in the lesson PHP and Functions.

Before the description of the function, you can see the general notation:

// Link to the file where the function to connect to database is defined
require("utils.php");

// Connection with the database
$spojeni = dbconn();

// SQL query SELECT
$sql = "SELECT ";
$sql = $sql."heading, descr, createdate ";
$sql = $sql."FROM renome_tarticle ";
$sql = $sql." ORDER BY createdate";

// Executing our SQL query
$vysledek = MySQL_Query($sql);

At the beginning we use a link to file with our function for connecting to database and we connect the database (the identifier of the connection is saved into the variable $spojeni). Then we create the SQL query inside a new variable $sql. The command could be written using one line only ($sql = "SELECT header, descr, createdate FROM renome_tarticle ORDER BY createdate"), the result would be the same. The variant we used is visually better (you will appreciate this when using longer queries). When using this method you should not forget to add a space between all commands, otherwise the query cannot be executed.

In case you defined your query, you have to execute it. You can use the function MySQL_Query which needs one parameter - the variable with the text of query ($sql in our case). The result is then saved to the variable $vysledek.

The result of the function MySQL_Query does not contain the final data but only an identifier of the result - you can verify this by the command print($vysledek) - you will get the text Resource id #N (the number of identifier will be written instead of N). You have to process this to get the final list or articles.

Loding Data into Array

If we have the identifier of result we can load data from the result. We should use the function MySQL_Fetch_Array. Input parameter for this function is our variable $vysledek. After executing this command, one record is saved to the variable. When executed again, the next record is saved to the variable. In case that no more records are available, the function returns false.

To be able to get all records, you should use the function MySQL_Fetch_Array inside a while cycle - cycle will be repeated until there are remaining records to be loaded. In case no more records are available, the function returns false and the while cycle is terminated. The PHP source code is available below this text:

...

if ($vysledek) {
	while($zaznam = MySQL_Fetch_Array($vysledek)) {
		print_r($zaznam);
	}
} 
else
{ 
	print("The query for database failed.");	
}

...

We nested the whole while cycle inside a condition which begins with the row if($vysledek). This condition runs the while cycle only in case that the query was completed (otherwise the variable $vysledek is false).

Inside the while cycle we write the content of the array using the function print_r. This function allows you to display the whole content of an array (this row is added to simplify the solution. We will replace it in the next part to be able to format the output).

Displaying Content of Each Record

We used the function MySQL_Fetch_Array to save each record to the variable $zaznam and we are able to access all values using the same procedure as for an array (single columns of table are saved to the array in the order as you placed them inside the query). You only have to add the name of variable and the number of item inside angular brackets.

...

if ($vysledek) {
	while($zaznam = MySQL_Fetch_Array($vysledek)) {
			print("<h2>".$zaznam[0]."</h2>");
			print("<p>".$zaznam[1]."</p>");
	}
} 
else
{ 
	print("The query for database failed.");	
}

...

Using the number of each item is not very practical (consider using a large table). We will make one more adjustment - replace the number indexes with names of columns inside the table.

...

if ($vysledek) {
	while($zaznam = MySQL_Fetch_Array($vysledek)) {
			print("<h2>".$zaznam["heading"]."</h2>");
			print("<p>".$zaznam["descr"]."</p>");
	}
}
else
{ 
	print("The query for database failed.");	
}

...

Counting the Results of Query

The current output contains a list of articles (headlines + short description). You might want to count all articles which are in the list. You can count all results by adding a new variable $pocet inside the while cycle.

...

if ($vysledek) {
	$pocet = 0;
	while($zaznam = MySQL_Fetch_Array($vysledek)) {
			print("<h2>".$zaznam["heading"]."</h2>");
			print("<p>".$zaznam["descr"]."</p>");
            $pocet = $pocet + 1;
	}
    print("Number of found articles: ".$pocet);
}
else
{ 
	print("The query for database failed.");	
}

...

You can see the number of found articles after this step, but it is displayed at the end of page which is not ideal. We will make another adjustment to display the number of found articles before the list of articles. You can achieve this by changing the while cycle not to write items to browser but to save them inside a variable. You can write the whole variable to browser after writing the number of articles. The script is available below this text:

...

if ($vysledek) {

	$pocet = 0;
    $htmlclanky = "";

	while($zaznam = MySQL_Fetch_Array($vysledek)) {
			$htmlclanky .= "<h2>".$zaznam["heading"]."</h2>";
			$htmlclanky = "<p>".$zaznam["descr"]."</p>";
            $pocet = $pocet + 1;
	}
    
    print("Number of found articles: ".$pocet);
    print($htmlclanky);

}
else
{ 
	print("The query for database failed.");	
}

...

There is one more situation which can occur because the query might find no article. You have to add a condition which checks the content of the variable $htmlclanky. In case this variable is empty (you can compare the variable with an empty string), a text with information that no article was found will be displayed.

...

if ($vysledek) {
	$pocet = 0;
    $htmlclanky = "";
	while($zaznam = MySQL_Fetch_Array($vysledek)) {
			$htmlclanky .= "<h2>".$zaznam["heading"]."</h2>";
			$htmlclanky = "<p>".$zaznam["descr"]."</p>";
            $pocet = $pocet + 1;
	}
   
    if ($htmlclanky == "")
    {
    	print("No article was found.");
    }
    else
    {
    	print("Number of found articles: ".$pocet);
    	print($htmlclanky);
    }
}
else
{ 
	print("The query for database failed.");	
}

...

You can see the entire source code of our PHP script at the end of this lesson.

...

require("utils.php");

$spojeni = dbconn();

$sql = "SELECT ";
$sql = $sql."heading, descr, createdate ";
$sql = $sql."FROM renome_tarticle ";
$sql = $sql." ORDER BY createdate";

$vysledek = MySQL_Query($sql);

if ($vysledek) {

	$pocet = 0;
    $htmlclanky = "";

	while($zaznam = MySQL_Fetch_Array($vysledek)) {
			$htmlclanky .= "<h2>".$zaznam["heading"]."</h2>";
			$htmlclanky = "<p>".$zaznam["descr"]."</p>";
            $pocet = $pocet + 1;
	}
    
    if ($htmlclanky == "")
    {
    	print("No article was found.");
    }
    else
    {
    	print("Number of found articles: ".$pocet);
    	print($htmlclanky);
    }

}
else
{ 
	print("The query for database failed.");	
}

...

Additional Texts

Questions

1. What is the meaning of the function MySQL_Query?
2. How can you display the result of a query inside browser?