31 Mar Php – How to Retrieve Data From a Database
PHP stands for “PHP Hypertext Preprocessor”. Its primary function on the FHA website is to interpret browser requests for web pages that access the database. You may have noticed that every page on our website (except linked PDF files like the FHA Newsletter archive) has a URL of the form
https://www.fearringtonfha.org/index.php?….
where the “…” will differ for each page and type of page that is requested by the browser.
There are three parts to these URLs that are important to note:
- https tells the web server that the connection between the browser and the server is to be encrypted, in order that the browser (and the user) can be assured that the website being accessed is the one being requested, and that the data returned by the server won’t be changed while it is sent from the server to the browser.
- fearringtonfha.org is the domain name where the requested web page resides. A process called DNS (domain name service) is used by the browser to identify exactly where the domain is resident (or hosted), by looking up the IP (internet protocol) address of the hosting computer. This is a four-component number like 37.60.244.124 (which happens to be the IP address for our website).
- /index.php gives the name of the file being requested by the browser. The “php” extension for the file tells the server that there is more going on than just retrieving a file and sending it on down to the browser. It says the file index.php actually contains a program written in the PHP language, and so the whole URL is passed along to the PHP language interpreter (not compiler, if you know the difference), which reads the PHP program and executes it in order to put together the stuff the browser is expecting to be returned as a result of going to that web page.
The fourth part of the URL, the stuff following the “?”, tells the PHP program in the index.php file more about what is to be returned to the browser.
URLs can contain additional components, but these are mostly not used in Joomla. Wikipedia provides more information about these, if you’re interested.
PHP is a reasonably rich programming language with most all you’d expect in a modern, object-oriented programming language. It is recommended that you take an hour or so and familiarize yourself with PHP using our old resource, W3Schools. The following part of this article will assume you know (or can find reference to) enough PHP to read a little code.
Hint: I have found over the years that reading code is the best way of getting acquainted with a new language. To know what code is supposed to do, and to read the code that actually make it do what it’s supposed to, is very conducive to learning a new programming language. W3Schools is helpful in that it shows you working code that you can change and see the effects of the changes, so highly recommend this method!
Using PHP to read a database
Here is a PHP code snippet that will read some rows from a database. I have placed each line of PHP code in a table row on the left, and some comments on the right to tell what each row is doing. Remember: A PHP variable name always starts with a ‘$’, and a PHP statement always ends with a ‘;’.
| PHP Code | Comments |
| $tblName = “FHAbb”; | Define the variable $tblName as having the value ‘FHAbb’. |
| $whereClause = 1; | Define $whereClause as the number 1 – which is evaluated as “TRUE”, meaning there is no restriction on the content of the returned rows. The line containing this variable is superfluous and would normally be eliminated. |
| $orderBy = “postDTS DESC”; | $orderBy will be used to sort the returned rows in descending (‘DESC’) order of the posting date-time stamp column postDTS. |
| $limit = 50; | This will be the upper limit of the number of rows to be returned. If the query finds fewer than 50 rows, all will be returned. |
| $SQL = “SELECT “ . “* “ . “FROM $tblName “ . “WHERE $whereClause “ . “ORDER BY $orderBy” . “LIMIT $limit “ . “;”; | Define a string variable named $SQL as the string “SELECT ” concatenated with (the ‘.’ operator) a string “* ” concatenated with the string “FROM $tblName” (where the variable $tblName is replaced by its value, e.g., ‘FHAbb’ to create the string “FROM FHAbb ” etc, etc. All variables in the strings will be replaced by their values. After this statement is executed the variable $SQL will contain the following string: SELECT * FROM FHAbb WHERE 1 ORDER BY postDTS DESC LIMIT 50; You can run this query on the website: Admin>Query a Database, change Limit Results to No Limit, and paste the query into ,the Roll your own window, then click Run Query and view the results. |
| if ($debugging) echo(“<p>$SQL</p>”); | Sometimes we might like to see what the query looks like – if it doesn’t return what we think it should, a printout will often reveal why the results weren’t as expected. I use a variable $debugging (set to TRUE for debugging printouts or FALSE for none, to control debugging printouts. Note that in this snippet of code $debugging is not defined before its first use. Undefined variables with evaluate as FALSE, but will also cause a WARNING-type error message to be written in the site’s error log. |
| $res = mysqli_query($link, $SQL) or die(“Fail|Query failed : ” . mysqli_error($link) . “<br />” . “SQL = $SQL</br>”); | Define the query result variable $res to be an object which is the result of running the query $SQL on the linkage pathway $link (assumed to be defined above – does not appear in this snippet). If the function mysqli_query fails due to some problem with the database or the query (most often the query), it returns the value FALSE, and PHP continues to the right with the “or”boolean operation to the following function “die()”, which terminates execution, displaying the error message composed as the argument of the ‘die()’ function. If no error occurs, the “or” is never executed and PHP continues to the following line. |
| $nrows = mysqli_num_rows($res); | Sets “$nrows” to the number of rows in the query’s result. If zero, as in the cae of a search with no hits, we should issue an informative message rather than a blank page. |
So the database has been interrogated and the rows from the successful query are now available through the $res variable object. We’ll see how to read these rows and display them in the next article.