Round Numbers to Next Whole Number in PHP

Time for a new php function, ceil().

A client needed to record the hours spent in particular activities by each member. The number of hours spent in assigned activities were to be entered in whole numbers, where partial hours were rounded up to the nearest hour. So, if someone spent an hour and twenty minutes on a particular activity they were to enter 2 hours into the web site form. However, as we all know, directions are not always read, so don’t trust your input.

Validate all database input!

What was the computer going to spit out on the reports when a person entered 1.3 instead of 2? Naturally the output was rounded down to 1 hour instead of up to 2 hours.

To round up all fractional numbers php has a function that allows us to round all fractions up to the next highest whole number: ceil(argument);

Use it like so:

ceil(5.34) produces the value 6, while ceil(5.56) also gives us 6.

You can throw a variable in the parentheses instead of decimals:

$time_spent = ceil($hours);

Using ceil() allowed me to format the data before it was inserted into the database. Now, reports of member activities will indicate the appropriate time spent.

Left Joins Produce Data Reports Using All Rows of One Table

A client needed to have several reports created that indicate the various training sessions and certifications that each member has achieved. Producing the reports required collating data from several mysql tables and that made the queries a little complex.

SELECT statements were used for the most part which combined data from up to nine different mysql tables for the most inclusive report. A problem surfaced in that the reports did not show all the members. Some of the newer members did not yet have accomplishments that would show up in the output. Instead of seeing blank values for the newbies, the output did not list those new people at all. The reports were incomplete without listing all the members.

Not all members are listed in the report.

It would be helpful to see those blanks in the report as a reminder that some of the new people still need to satisfy certain requirements.

LEFT JOINS to the rescue!

LEFT JOINs are used to combine data from several database tables that may or may not have content pertaining to all members. Using the LEFT JOIN having the members table on the left will retain all members in the final report whether or not they have entries in the associated tables.

Instead of using the regular construct, SELECT data-col1, data-col2, data-col3 FROM table1, table2 WHERE condition1 = condition2, a LEFT JOIN was used like so:

SELECT data-col1, data-col2, data-col3 FROM table1 LEFT OUTER JOIN table2 ON condition1 = condition2

where the two table names are separated by “LEFT OUTER JOIN” and the WHERE clause is changed to the ON condition.

Emphasis is given to the table named on the left, table1, so that all its rows will be retained through the SELECTing of columns even though null values are encountered.

Original code that produced a report without blank lines:

$fems = mysql_query(“SELECT `last_name`, `first_name`, MAX(`certs_ID`) AS mcrt, `fema_date`, `pin` FROM `members`, `fema`, `fema_attend`, `certs`, `sar-certs` WHERE `pin` = `mem_pin` AND `fema_ID` = `fema_ids` AND `member_pin` = `pin` AND `cert_ID` = `certs_ID` GROUP BY `last_name`, `first_name` “)

Updated code using LEFT JOINS that produced a complete report that contained blank lines:

$fems = mysql_query(“SELECT `last_name`, `first_name`, MAX(`certs_ID`) AS mcrt, `fema_date`, `pin` FROM `members` LEFT OUTER JOIN `fema_attend` ON `pin` = `mem_pin` LEFT OUTER JOIN `fema` ON `fema_ID` = `fema_ids` LEFT OUTER JOIN `sar-certs` ON `member_pin` = `pin` LEFT OUTER JOIN `certs` ON `cert_ID` = `certs_ID` GROUP BY `last_name`, `first_name` “)

All members present in the report using LEFT JOINs.

You’ll need a new LEFT JOIN for each table that you want to tack onto the SELECT statement in order to keep the blank values in the output.

Keep smiling and keep reading!

Null Values Handled Nicely with the PHP Error Suppression Operator

Working on some database reports for a client’s membership roster and such, I came across a simple solution in handling some types of errors. Error messages filled a column in one report that should have output whether certain requirements were met by individual members of the organization.

Here’s the queries and partial output in question:

$fems = mysql_query(“SELECT `last_name`, `first_name`, MAX(`certs_ID`) AS mcrt, `fema_date`, `pin` FROM `members` LEFT OUTER JOIN `fema_attend` ON `pin` = `mem_pin` LEFT OUTER JOIN `fema` ON `fema_ID` = `fema_ids` LEFT OUTER JOIN `sar-certs` ON `member_pin` = `pin` LEFT OUTER JOIN `certs` ON `cert_ID` = `certs_ID` GROUP BY `last_name`, `first_name` “) or die (“Query failed here 12”);
while ($fem = mysql_fetch_array($fems)) {
echo “<td>$fem[last_name]</td>”.” <td>$fem[first_name]</td> “;

$high_cert = mysql_query(“SELECT `cert` FROM `certs` WHERE `cert_ID` = $certif[mcert]”);
while ($high_cer = mysql_fetch_array($high_cert)) {
echo “$high_cer[cert]”;

And, here’s part of the ugly report that code section produced:

Ugly report.

What’s happening here to produce the error is that one of the queries produces some null values for the newest members of the organization who haven’t completed some requirements as of yet.

While fetching values from the $fems array an error is dumped on the screen that states:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\xampp\htdocs\org_member.php on line 766

How did I figure out what that error meant? Using the tool phpmyadmin! Once you open phpMyAdmin and have selected your database, click on the SQL tab.

phpMyAdmin and its SQL tab.

I copied and pasted the first SELECT statement in the SQL box that is labeled “Run SQL query/queries on database psar-data:”, where psar-data is the name of my database.
SQL query inside phpMyAdmin.SQL query inside phpMyAdmin.

The page that is returned after pressing Go shows the SQL query and its output that contains NULL values.

phpMyAdmin and its SQL tab and query.

Now, when the data set was subjected to the second query the null values produced the invalid result resource warning error. The second query was this:

$high_crts = mysql_query(“SELECT `cert` FROM `certs` WHERE `cert_ID` = $fem[mcrt]”);
while ($high_crt = mysql_fetch_array($high_crts)) {
echo “$high_crt[cert]”;

Outputting values to the screen from this query would be so much nicer if the error would go away and just leave a blank. This example is a perfect use for the @ operator, or PHP error suppression operator. When placed in front of a PHP function the @ operator does not output error statements and mess up your data reports.

In the case where null values are encountered by the PHP function mysql_fetch_array the ugly warning error is produced. Placing the @ operator directly in front of mysql_fetch_array, like so @mysql_fetch_array, gives the output we’re seeking.

Output using the @ operator.

Yes, that output looks more professional without the error message and shows exactly what we want. In this case the blanks are meaningful and show exactly where a member needs to complete certain requirements.

Commenting Out Sections of Code Helps PHP Troubleshooting

Yesterday, at the end of a long day in front of this machine I could not for the life of me find the cause of this error:

"Parse error: syntax error, unexpected $end in"
my file on the last line. Now, I recognize that probably means I left out or hastily copied over a quote mark or left the semi-colon off the end of a php statement.

While 512 lines is no huge file, scanning those umpteen lines for a missing character was futile even though I looked at every line at least three times. Then it occurred to me to do what all good thinkers do…let my mind wander! It’s a trick that more people should know about, where you leave the problem at hand and free your mind of it for the time being. Miraculously, a solution will present itself. Michael, where ever you are, thanks for the tip!

Coming back to the issue this morning, I figured that I could “comment out” large portions of the code to isolate the problem. At first I started to comment out entire areas of the php by using <!–– ––> around the html portions that contained the php statements, but that didn’t work. Then, I realized that I should be commenting out portions of the php, so I used /* */ around smaller and smaller sections of the php code and finally found my error, a missing endif.

Comments come in handy for troubleshooting php!