Bringing data from the MySQL database to PHP pages

How to read data from a database with PHP

One of the main objectives of creating a table in a database is that all (or part of) the data contained in it can be read and displayed within a page of our website.

We will learn the necessary steps to read the data stored in a table of a database and then we will show them inside the HTML code of a page.

The steps that will be followed are the following:

1. That the PHP interpreter program be identified before the MySQL manager program (that obtains authorization to request data).
2. Once authorized, we will prepare the order of the SQL language that we want to execute to bring the data that interests us.
3. We will execute the SQL command, and we will obtain a data package as a response.
4. We will go through the data package through a loop that is extracted from one record at a time, a record that we will decompose into fields that we will show until there is no more data to show.

We will learn these steps with a concrete example: we will build a page that shows the contents of the table "messages" created in the article by name: "Creating the first database".

Test the connection

The PHP interpreter program - which processes our PHP code - must demonstrate to the MySQL manager program that it has authorization to request data.

To try to establish a connection between these two programs, we will use the mysql_connect function, which requires that we complete three parameters: host, user and MySQL key, in that order, consequently the basic structure of this function will be:

<?php
mysql_connect("host", "user", "key");
?>

As of version 5.5.0 of PHP, the mysql_connect function has become obsolete, therefore, it is recommended to use mysqli_connect or PDO :: __ construct (). It is also recommended to take a look at each of the extensions for MySQLi functions

What does each of these parameters mean?

1. Host is the computer on which the MySQL manager program is installed, typically, it will be the same computer on which our PHP interpreter is running, whether we are doing it locally in our house, or a hosting. Thus, the first parameter of the mysql_connect function must say localhost (the local host, the same one in which our PHP code is running). In the rare case that MySQL is on another computer than the one that is running PHP, we must place the IP address of that machine as the first parameter
2. User is the name of a MySQL software user who has authorization to execute queries. For the majority of emulators that are installed locally (XAMP EASYPHP, APPSERV), the user to use the database is called "root", but in a hosting the user name will be different, either because the user created it. administrator of the hosting, or because we create it ourselves from a panel of administration of the Web site (this varies much in each hosting, reason why we leave it so that they consult it to the technical support of his supplier company). It is usually the user with whom we identify to enter the phpMyAdmin
3. Password: is the password of the user with whom we are identifying before the MySQL program. For most emulators that are installed locally the key is an empty field, but in a hosting we will have to ask for it or create it, as in the example of the user that was explained in the previous step

For this reason, locally our mysql_connect function would look like this:

<?php
mysql_connect("localhost", "root", "key");
?>

In the rare case of an intranet with different machines for the PHP interpreter and the MySQL manager program, something like the following will remain (obviously the data is not real):

<?php
mysql_connect(123.456.10.1,"user", "password");
?>

Since mysql_connect is a function that can only return "true" or "false" (Boolean), it is possible to wrap it within a conditional that will evaluate if MySQL authorizes us to send queries, or not:

<?php
if (mysql_connect ("localhost", "root", "clave") ){
echo "<p>MySQL has given PHP permission to execute queries with that user and password</p>";
}else{
echo "<p>MySQL does not know that user and password, and rejects the connection attempt</p>";
}
?>

With this we have the first step ready. Our PHP interpreter program is already authorized to make queries to the MySQL manager program. Now, it's time to create the query that we will execute.

The "SELECT" command: understanding the SQL language

In order for the MySQL manager program to provide us with a set of data, we must learn to tell it so that it understands us and, for this, we will use commands from the SQL language (Structured Query Language or structured query language).

In this case we will need to read data from a table, the order will be SELECT (select). Through this order, we will ask the MySQL manager to deliver exactly those we want to show on our pages.

The basic syntax of the SELECT command is as follows:

SELECT field1, field2, field3, FROM table

This will be translated as "Select the indicated fields, from the indicated table".

With an example it will be more than simple to understand its use:

SELECT name, email FROM messages

This command will select the fields (columns) name and email through all the records in our table called messages

That is, it will return all records (rows) of the table, but "cut" vertically, extracting only the two columns requested (name and email), consequently, we would get the following (we can insert several records before doing this):

Pepe	pepe@pepe.com
Carlos	carlos@garcia.com
Alberto	alberto@perez.com

If instead we would like to bring "all the fields" of the table (very frequent task and, that in case of having many fields the table, it could be quite tedious to complete their names one by one) we can use a character that fulfills the wildcard functions to symbolize "all the fields in the table".

This character is the asterisk, with which the order would be:

SELECT * FROM messages

This order would bring all the columns (fields) of the messages table (and, of course, all the records too):

1	Pepe	pepe@pepe.com	Bla, bla, bla...
2	Carlos	carlos@garcia.com	Bla, bla, bla...
3	Alberto	alberto@perez.com	Bla, bla, bla...

In short, to finish this second step, we can store the SQL command in a variable (until the moment we need it):

<?php
$query= "SELECT * FROM messages";
?>

Now that we have prepared our SQL order, it is time to move on to the third step and have the PHP interpreter send it to the MySQL manager, obtaining some response.

Execute the order and get an answer

For PHP to send an SQL query to the MySQL manager we will use two functions that the PHP language has.

The first is called mysql_select_db (select a database) and the second mysql_query (make a query -query- to a MySQL database).

The first one, mysql_select_db, is used only once per page, to indicate to which database we will conduct queries from there on.

It is only necessary to specify in parentheses which database we will request to execute this query (we recommend that, until now, at no time do we mention our database called "courses" seen in the article by name "Creating the first database").

For example:

<?php
mysql_select_db ("courses");
?>

After selecting the database, we execute the query itself with mysql_query.

This function requires that we complete a single parameter: the SQL query that we will execute.

Therefore, the order, in this case, would be provisionally like this:

<?php
mysql_query($query);
?>

Note that we specify the variable where we have recently stored the command of the SQL language that we are going to execute ($ query)

But if we were to execute this code as it is here, we would miss the main objective of this query, which was to obtain data from the table

We are running this query, which in return should send us a "package" of data, but ... we are ignoring this that it gives us and we let it get lost in the air.

It is necessary that we receive the data it returns to us, at the same instant in which we execute the query, that is: we must assign to some variable the result of executing the mysql_query function, so that this variable is the one that contains the received response.

For example:

<?php
$data=mysql_query($query);
?>

In this way, we complete the third step and are ready for the fourth and final step.

Integrate data into HTML code

It only remains to loop through the data package and generate the HTML code that includes the data that we will show on the screen.

Let's start by saying that $ data is not a common variable, nor a matrix, but is a result of a query or, more simply, a "closed package" of data that we can not use directly, but we will have to decompress it so that it can be to show.

The data package contains several rows (the messages of Pepe, García, Pérez, etc.), but, in turn, each row contains several fields (id, name, email and message).

It will be necessary to take with tweezers a horizontal row at a time from the package that is $data, and once we have selected a row, we will have to transfer it inside a matrix (we can call it $row) that contains in each cell, a field of that row: $row["id"], $row["name"], $ row["email"] and $row ["message"].

Exactly this is what a function called mysql_fetch_array does: it takes as parameter a package $data that we have and it extracts a complete row, which we must assign to an array that we will call $row.

The code would be like this:

<?php
$row=mysql_fetch_array($data);
?>

This moment, $row is a matrix (or array, that's why ... fletch_array) that contains the first full row of the table, with the values of its four fields located in table cells of the array $row.

The alphanumeric indexes of this array $ row, which contains a record of the table, are the names that had the fields in the table.

At this time,

• $row["id"] contains a "1",
• $row["nombre"] contains Pepe,
• $row["email"] contains "pepe@pepe.com" y
• $row["mensaje"] "Bla, bla, bla...".

But what will happen to the rest of the rows that we have not read? The mysql_fetch_array function only reads one row each time it is executed.

To be able to read not only the first one, but also the rest of the rows, we should make a loop, that executes this reading and allocation of one row at a time, repetitively, while there is still some data to allocate (to be a loop). of type while, since we do not know in advance how many rows will return the query).

As soon as a row is stored in the matrix, we must show it immediately (write on the screen with a print or echo), since one row after the loop, the content of $ row will be erased to allow to store the values of the next row of the board.

The condition of the loop will be "while we can decompress a new row of the data package with mysql_fetch_array, store it in a matrix" ... Let's think carefully and see what that part would look like:

<?php
while ($row=mysql_fetch_array($data)){
echo "<p>";
echo "-"; //a separator
echo $row ("nombre");
echo "-"; // a separator
echo $row ("email");
echo "-"; // a separator
echo $row ("message");
echo "<p>";
?>

To finish, let's see a complete example, passed in clean of the four steps together:

<?php
// 1) Connection
if ($conexión = mysql_connect("localhost", "root", "clave")){
echo "<p>MySQL has given PHP permission to execute queries with that user </p>";

// 2) Prepare the SQL command
$query="SELECT * FROM message";

// 3) Execute the order and obtain data
mysql_select_db("courses");
$data=mysql_query($query);

// 4) Go Printing the resulting rows
while($row=mysql_fetch_array($data)){
echo "<p">;
echo $row["id"];
echo "-"; // a separator
echo $row["name"];
echo "-"; // a separator
echo $row ["email"];
echo "-"; // a separator
echo $row["message"];
echo "</p>";
}

}else{
echo "<p>MySQL does not know that username and password </p>";
}
?>

Now we are ready to show the data stored in our tables.

We will do this again thousands of times, changing "products" by "message", "employees", "comments", or whatever you have to show on a page of our site.

Since already the way to show this data within the HTML code will vary; in some cases, we will need to show a text (paragraphs like those in the previous example); in others, we will need to generate option labels within a select element; other times, rows of a table; or radio type buttons, or check boxes.

The ideal is that we go creating a series of functions, which receive as parameters the name of the table and the fields that will be selected, plus some specific data (label, name, element to show selected, etc.) and that returns the block Full HTML loaded with the data of the table.

Supplements of the SQL SELECT command

We have already seen the minimum syntax of the SELECT command in the SQL language, which allows us to select certain fields of a table.

But now let's go a little further and we will know a conditional modifier called WHERE, which is very useful to increase its power when extracting data with precision from the database (all our SQL queries will have a WHERE).

To put this concept into practice, we will create a new table in our database.

We will call it "employees", and will have the following fields:

CREATE TABLE employees (
id TINYINT (3) NOT NULL AUTO_INCREMENT PRIMARY KEY,
names VARCHAR(40) NOT NULL,
lastname VARCHAR (40) NOT NULL,
age TINYINT (2) NOT NULL,
country VARCHAR (30) NOT NULL,
specialty VARCHAR (30) NOT NULL
) ENGINE = MYISAM

We can write this command inside the SQL code execution window of phpMyAdmin, and the table will be created identical to the original:

Once the table is created, we will load several records with different values in the different fields, to be able to use them in the conditional selection orders.

We recommend uploading the following data, since they were chosen especially to show significant results in the consultations that we will do next:

id	name	lastname	age	country	speciality
1	Pedro	Fernandez	34	España	Matemáticas
2	José	García	28	México	Sistemas
3	Guillermo	Pérez	32	España	Contabilidad
4	Alberto	Maza	45	México	Matemáticas
5	Luis	Puente	43	Argentina	Sistemas
6	Claudio	Lopéz	41	España	Medicina
7	Mario	Juárez	41	México	Sistemas
8	Alan	Flores	25	Perú	Sistemas

The conditional WHERE

At our SELECT orders, it will be very common for us to add conditions, so that we return a smaller set of results: only those records that meet the condition. The condition will take into account the value of any of the fields.

For example, imagine that we need a list of employees from the previous table, but not all, but only those whose specialty is "Mathematics".

The syntax of the WHERE conditional expression is similar to that of an if: it is an assertion in the form of a comparison, whose elements will be a field of the table, a comparison operator and a value against which it is compared:

... WHERE operator field 'value'

What translated into a more concrete example, could be:

... WHERE specialty='Mathematics'

And by completing the SELECT order with this condition, we would have a complete order:

SELECT name, lastname FROM employees
WHERE specialty='Mathematics'

If we write this command in the window of execution of SQL code of the phpMyAdmin, and click on Continue, we will obtain as a result just the data that we needed:

We obtained only the first and last name of the employees whose "specialty" field had a value exactly equal to "Mathematics".

Operators to use WHERE

The operators that we have in a WHERE are very similar to the ones we use in PHP conditional, except the last one, called LIKE.

Operator	Means
=	Equal
>	Great than
<	Lest than
>=	Greater than or equal
<=	Less than or equal
<>	Other than
LIKE	That includes... (now we will analyze it)

The operator LIKE allows to establish conditions of "similarity" much less strict than the simple equality of the complete content of a field allowed by the already known equal sign.

For example, if instead of needing a list of employees whose specialty is exactly "Mathematics", we will need a list of employees whose specialty "start with M", we could use this operator. It serves to find a character (or more) at the beginning, in the middle or at the end of a field.

It uses a "wildcard" that is the sign of% (percentage), which symbolizes the potential presence of characters in that direction.

It is used in the following way:

1. ... WHERE specialty LIKE 'M%'
   We will return those records in which the specialty field contains a letter "M" at the beginning of the field, regardless of whether any other thing follows (or even if nothing follows). In our example table, it will include whoever has "Medicine" and Mathematics "as a specialty
2. ... WHERE specialty LIKE '%M%'
   It will return the records that in the specialty field contain a letter "M" (uppercase or lowercase, it is indistinct) at the beginning, in the middle or at the end of the field. In our table, we will return to those who have specialties of "Medicine", "Mathematics" and "Systems".
3. ... WHERE specialty LIKE '%M'
   It will select those in the specialty field that contain a letter "M" just at the end, as the last letter of the field (or as the only letter of the field), but not followed by another letter.
   It is essential that the value sought, together with the character of "%", be enclosed in single quotes.

This LIKE operator is the one typically used in any search field that inquires into a field of texts or news titles, comments, forum messages, product names, etc.

The important thing is that it allows us to find the searched word in part of the title or name of the product.

For example, if we search ... WHERE name LIKE '% TV%', we will find records that in the name of a product contain "TV LG 42 inches", or "Mini TV wristband", or "LED monitor with TV tuner ", which will be impossible to select with the normal operator" = "(which requires that the complete content of the field be the same as what was searched).

Let's now see a useful operator for ranges: it is the BETWEEN operator, which allows us to specify a minimum limit and a maximum limit. Let's test it with the age of our employees.

If we need a list of employees between 40 and 50 years old, we would obtain it with this order:

SELECT * FROM employees WHERE age BETWEEN 40 and 50

We can indicate the exclusion of a range by NOT BETWEEN:

SELECT * FROM employees WHERE age NOT BETWEEN 18 and 40

We can also join several conditions through the logical operators AND and OR.

Let's see an example of OR (it will return both the employees that in its field "country" figure the value "Spain" as those that are "Argentina"):

SELECT * FROM employees WHERE country= 'España' OR country= 'Argentina'

On the other hand, when combining conditions with AND, the results will be more specific only those records that meet all the conditions linked by AND:

SELECT * FROM employees WHERE country= 'Argentina' AND specialty= 'Sistemas'

Also, we can look for matches with several possible values for a field, and not with only one, using IN:

SELECT * FROM employees
WHERE country In ('México', 'Argentina', Perú')

In this way, we provide, in a single step, a list of values for the "country" field that, in this example, will return the records of employees whose country is Mexico, Argentina or Peru.

Sorting the results

At the moment of obtaining the results of an SQL query, we can wish that they are delivered to us in an orderly manner, for the value of any of the fields.

For this, the expression ORDER BY is used, to which the name of the field by which we want to order the results must follow.

Let's try to get a list of employees sorted alphabetically by the last name:

SELECT * FROM employees ORDER BY lastname

We have two additional modifiers that specify if the ordering will be made smaller or larger (ascending: ASC, it is not necessary to specify it since it is the way they were ordered by default), or descending (from highest to lowest, in which case the modifier is DESC). For example:

SELECT * FROM employees ORDER BY age DESC

Limiting the amount of results

When the number of results can be very large, it will be convenient to show only a certain number of results (as Google does in its results list).

This is widely used when displaying "paged" results (with the option to see other next or previous sets of results, using <and> or similar).

To limit the amount returned by a query, we will use LIMIT.

This modifier requires that we specify two values: from which result number to return, and how many results it will return.

With an example it will be easier to understand:

SELECT * FROM employees ORDER BY lastname LIMIT 0,3

This will return three records, starting with the first, but not the first physically in the table, but the first of the results that would have returned the query ordered by last name, that is, the last name closest to A.

If in a pager we had to make another query to bring the next three, the only thing that will change is the start value of LIMIT:

SELECT * FROM employees ORDER BY lastname LIMIT 3,3

That will return three records, after the third (taking into account the last name).

Selecting non-repeated values

We can easily obtain the unique values of a field, without taking into account its repetitions.

For example, if we want to obtain the list of specialties, without repeating any, that at least one of the employees is assigned, we can request it by adding the word DISTINCT in front of the field that will be selected.

SELECT DISTINCT specialty FROM employees

That will return the list of specialties one per record

Statistical functions

Many times, we will not need a query to return "the data" that are stored in the table, if not some statistical information "about" that data.

For example, how many records did a query select, what is the minimum or maximum value of a field, what is the sum of the values of that field throughout all the records, or what is the average value:

Function	What it returns
COUNT	The amount of registration selected by a query
MIN	The minimum value stored in that field
MAX	The maximum value stored in that field
SUM	The sum of that field
AVG	The average of that field

In case we need to consult "if there is any data in a table (we will know if its quantity is less than 1), or if we want to know how many records exist that meet a certain condition, we can use the COUNT function.

SELECT COUNT (*) FROM employees WHERE country= 'México'

This function returns, not a set of records, but a single number: the number of existing records that meet the specified condition (and if we do not specify any, it will return the total number of records in the table):

The other four functions, like COUNT, return a single number as a result.

They are used in the same way, together with the word SELECT, and when dealing with functions, they all surround in parentheses the name of the field they are going to analyze.

SELECT MIN (age) FROM employees;
SELECT MAX(age) FROM employees;
SELECT SUM (age) FROM employees;
SELECT AVG (age) FROM employees;

There are many other operators, functions and modifiers in the SQL language, so we recommend continuing to investigate on our own in some good manual specific to the SQL language.

Own functions to show data

It is extremely useful to create a "library" of functions, specialized in reading data from a database and then displaying them within different types of HTML tags. Sooner or later, we will have to perform a similar task on our sites.

We should divide all the work into several complementary functions:

1. A function that tries to establish a connection with the base and tries to select it (if any of these two steps fails, nothing else can be done)
2. A function that executes a query and obtains a "package" of data in response. You will need as an input parameter the SQL command that will be executed, and that will return a "package" of data (or false in case the data package is empty)
3. Several functions, each specialized in displaying the received data, wrapped in specific HTML tags

Let's go step by step:

1) To create a function that is responsible for the connection to the database, a prior practice -very advisable- is to define, in a single external file, all the variables related to the MySQL connection, such as the host , user, password and name of the database, since its value usually changes from one hosting to another.

If we do it this way, when we test our system in a different hosting, we will not have to manually change those data in each page of our site.

Suppose we create a file called datos.php for that purpose. Its content will be the following:

<?php
$host="localhost";
$user="root";
$key="key";
$base="courses";
?>

Then we will make an include of that file within each page where we will connect to the database, and we will use those variables in the functions. For example:

<?php
// We include that data:
include("datos.php");
// We use these variables:
if($connect=msql_connect($host,$user,$key)) {
// etc.
?>

In this way, if we change the server (between a local test server and a hosting or a client's hosting to another client's hosting), where we do not have the same names of bases, users and passwords, we will only have to modify a only file (data.php) only once, and we will not have to modify anything in the code of our pages.

The ideal is to have a file data.php in the local server with the local connection data, and other data.php in the hosting, with the connection data of the hosting.

Having prepared the connection data in a separate file, we can now create a proper function to handle the connection to the database.

Although in PHP we have a function to establish a connection with MySQL (mysql_connect), and another to select a particular database (mysql_select_db), both are independent, and this forces us to make two different conditions to validate if they achieved their task, complicating our code.

Our proposal is to unify both steps, since a failure at this level will prevent the use of the system, and the user will not care if he can not use the system because "the connection failed" or because "the database could not be selected", Both are unknown to you, and it is important to consider whether you need a specific message or not.

If we agree that it is not necessary to report in such detail, then we can unite both steps: connect and select a base.

We will then create a Boolean function (that returns true or false, so we can evaluate it within a conditional at the moment of execution).

<?php
function connectBase($host,$user,$key,$base){
if (!$link=mysql_connect($host,$user,$key)){
return false;
} elseif(!mysql_select_db($base)){
return false;
} else {
return true;

To this function, as we saw in the previous figure, we will create it within an external file called functions.php, which we will also include it within the pages by means of an include command:

include ("data.php");
include("functions.php");

However in this function there is still a problem, which we can easily check if we intentionally modify some of the connection data (try changing the host, user or password, for one that is not correct), and that is, if it fails the connection attempt, an error message in English will appear on the screen.

We can easily hide that error message if we precede the PHP function that produces the error (mysql_connect, in this case) the PHP error control operator, which is a simple at:

<?php
function connectBase($host,$user,$key,$base){
if (!$link=@mysql_connect($host,$user,$key)){
//note the arroba prefixed to the function that returned error
return false;
} elseif (!mysql_select_db($base)){
return false;
} else {
return true;
}
}
?>

This operator of error control (the arroba) we put before any expression that returns data (as, for example, a call to a function, an include order, a variable or a constant), and in this way we avoid that it is shown PHP error message in case of failure.

Returning to the function that we just created, we would use it from our pages within a conditional:

<?php
//we include the connection data and functions:
include("data.php");
include("functions.php");
// we use these variables:
if connectBase($host,$user,$key,$base){
// Here we would do the rest of operations ...
} else {
echo "<p>Service interrupted</p>"
}
?>

2) Now, it's time to create a function that allows us to query the database, obtaining a data package as an answer (or false in case of failure).

This function could be like this:

<?php
function consult($query){
if (!data=mysql_query($query) or mysql_num_
rows ($data) <1{
return false;// sif the query was rejected due to syntax errors, or no record matches the search, we return false
} else {
return $data;// if data was obtained, we return it to the point that the function was called
}
}
?>

Note the use of the mysql_num_rows function, which returns the amount of registration that a query obtained.

In our conditional, we are planning a double condition if the query itself failed (and returned false) -this is what it evaluates before the ol-, or if the number of records obtained was ... none.

We decided to combine both situations in a single condition, since we could break it down into different conditionals but at this moment we think it would be difficult to read the example.

Of the same as in our previous function, we will use our query function within a conditional, when we are sure that the connection to the base worked.

Let's see how the code of the page that goes calling these functions is going on:

<?php
// We include the connection data and functions:
include ("data.php");
include ("functions.php");
// We use these variables:
if(connectBase($host, $user, $key, $base)){
$query="SELECT * FROM employees";
if($package= consult($query)){
// Here we will call a function that shows this data
} else {
echo "<p>No data found</p>";
}
} else {
echo "<p>Service interrupted</p>";
}
?>

Finally, we just need to create different functions that take care of showing the "package" of data received, with the difference that each one will specialize in displaying the data within different HTML tags: we will see how to display data within a menu of selection, a table, radio buttons and check boxes.

Dynamic selection menu

Let's start with one of the most used HTML elements to show data from a database: the selection menus (select HTML elements).

It is very common that these elements show listings of countries, provinces, civil status, categories and any other variety of mutually exclusive options, of which the user must necessarily choose one.

The options that will be shown, as we can imagine, are the contents of a table in the database.

A function that produces the code of a select could have defined, at least, the parameters name and label, plus a "data package" obtained after making a query to the database.

We anticipate a couple of details that we will observe in this code:

• We have placed line breaks \ n where each line of HTML code ends, so that we can read it more clearly when looking at the source code received in our browser
• And we have converted the alphanumeric data received from the database to the UTF-8 character set; to show the accents and eñes without problems. For them, we have applied the PHP function called utf8_encode to the alphanumeric data

Having made these clarifications, let's see how a function that shows data within a selection menu could be:

<?php
function genera_MenuSelección ($data, $name, $label){
$code= '<label>'.$label.'</label>'."\n";
$code=$code.'<select name="'.name.'">."\n";
while($row=mysql_fetch_array ($data)){
$code= $code.'<option
value= "'.$row["id"].'">.utf8_encode($row["pais"]).'/
option>'."/n";
}
$code= $code."</select>\n";
return $code;
}
?>

In the page where we are going to call that function, we will receive in a variable the code that was accumulated by the function internally in its local variable $ code, and it will remain there (in the variable $ codeMenu, in this example) until we decide to use it. In this case, we immediately show it with an echo

Recall that we already have a $ data packet, which is what we will pass as a parameter to the function:

$codeMenu=generateMenuSelection($package, $name, $label);
echo $codeMenu;

These two lines would be placed in the part of the previous code where we left the comment that said "// Here we will call a function that shows this data"

Which will produce, in the case of our employee table, the following HTML code:

<label>employees</label>
<select name= "employees">
<option value= "1"> Pedro</option>
<option value= "2"> José</option>
<option value= "3"> Guillermo</option>
<option value= "4"> Alberto</option>
<option value= "5"> Luis</option>
<option value= "6"> Claudio</option>
<option value= "7"> Mario</option>
<option value= "8"> Alan</option>
</select>

We see the label and name values (provided as parameters when calling the function), and then the values wrapped between the opening and closing of each option label (obtained thanks to the query to the database).

This same logic, we can apply to wrap the data obtained in any other HTML tag, We will see, below, some more examples.

Generating tables, rows and data

A very common use of the data obtained from a query to a database is to display them in a table.

As we know, a table consists of a table tag that wraps the entire contents of the table, a tr tag (table row, or table row) that wraps each horizontal row and a td tag (table data, or table data) that surrounds each cell that contains data. We will have to generate these three parts with our PHP code.

Let's see how it could be a function that will show data in a table:

<?php
tabularFunction($data){
//Open the table tab once:
$code= <table border="1" cellpadding="3">';

//We are accumulating from one row "tr" per round:
while($row = @mysql_fetch_array($data) ) {

$code .= '<tr>';

//Let's accumulate as many "td" as necessary:
$code .= '<td>'.utf8_encode($row["id"]).'</td>';

$code .= '<td>'.utf8_encode($row["nombre"]).'</
td>';
$code .= '<td>'.utf8_encode($row["lastname"]).'</
td>';
$code .= '<td>'.utf8_encode($row["age"]) .'</
td>';
$code .= '<td>'.utf8_encode($row["pais"]) .'</
td>';
$code .= '<td>'.utf8_encode($row
["specialty"]).'</td>';

// close a "tr":
$code .= '</tr>';
}
// ending the loop, we close the table only once:
$code .='</table>';

return $code;
}

We can use it as the previous function, from a data package already obtained and assigned the code that manufactures a variable, which we then show in echo.

Radial buttons and check boxes

The last cases that we will see are those of the radio buttons and the check boxes.

We will start with the radio buttons: remember that to be part of the same series that excludes options, they must share the same value in the name attribute, although each button has its own unique id.

And that in addition to a fieldset and descriptive legend of the complete set, each button must have its own label:

<?php
function createRadios($data,$legends,$name){
//Open the fieldset with its legend:
$code = '<fieldset><legend>'.$legend.'</
legend'."/n";
//We are showing a label and an input per round:
while($row=@mysql_fetch_array($data)){

//A label:
$code .='<label>'.utf8_encode($row["name"]);

//One input:
$code = $code.'<input type="radio"
name="'.$name.'" id="dato'.$row["id"].'"/>'."\n";

//we close the label:
$code .='</label><br />'."\n";
}
$code .='</fieldset>'."\n";
return $code;

}
?>

We can call it inside the same file that made the connection and execute a query obtaining a data package, in the following way:

$radios=createRadios($package ,'Let's vote for the month', 'employee');
echo $radios;

Finally the check boxes: as we know, in them each name must be different, so we will form it joining a fixed part of the identifier of each record, as we did with the id in the previous example, of the buttons of radio:

<?php
function createCharts($data,$legend) {
//open the fieldset with its legend:
$code='<fieldset><legend>' .$legend.'>/
legend>' ."\n";
//We are showing a label and an input per round
while ($row=@mysql_fetch_array($data) ) {
//A label:
$code .='<label>'.utf8_encode($row["name"]);
//One input:
$code = $code.'<input type="checkbox"
name="dato'.$row["id"].'"data'.$row["id"].'"
/>'."\n";

//We close the label:
$code .='</label><br />'."\n";
}
$code .= '</fieldset>'."\n";

return $code;
}
?>

And we would call it in the following way:

$casillas = crearCasillas($paquete,'Your vote can be added to more than one employee '.'employee');

echo $cells;

It will be very useful to continue creating as many functions as we consider necessary, to show data in different ways within HTML tags, since we will need them continuously in our work with databases.

The goal of creating all the functions is to organize our code so that it is not loose (which makes it very easy to reuse).

We can notice how the amount of loose code within our pages is increasingly smaller, consisting of just a couple of includes (from the "data" and "functions" file), some conditionals that validate data and decide which to execute, and nothing else.

This way of programming modularized or structured functions, greatly facilitates maintenance, and makes future changes (always unpredictable) do not force us to modify the "pages" one by one, but only the file where is the declaration of our functions.

With this, we will already dominate enough techniques to send data from the base to our pages.

It is time to add interactivity and do the reverse route, taking data from the user's screen to our database