SUM() Table queries

I’m not new to PHP, but I’ve been away from it for a long while and a lot of the code I used for my older sites (when I was actively coding in PHP) has been deprecated. So I’m going through learning what’s changed… and I’m having a real problem with trying to get the SUM of entries from a database.

The query I’m running is this:

// Create Pilot Roster Query //
$sql = "SELECT * FROM pilots ORDER BY pilot_callsign ASC";
$result = mysqli_query($connect, $sql);

while ($prow = mysqli_fetch_array($result, MYSQLI_BOTH)) {
	$p_id = $prow['pilot_id'];
	$p_callsign = $prow['pilot_callsign'];
	$p_squadron = $prow['pilot_squadron'];
	
	// Get Squadron Info //
	$sqd = "SELECT * FROM squadrons WHERE squad_id = $p_squadron";
	$resultsqd = mysqli_query($connect, $sqd);
	
	while ($psqd = mysqli_fetch_array($resultsqd, MYSQLI_BOTH)) {
		$pilotsa = $psqd['squad_desig'];
		$pilotsb = $psqd['squad_name'];
	}
	
	// Get Flight Hours  //
	$flt = "SELECT SUM(log_dayhrs) FROM logbook WHERE log_pilotid = $p_id";
	$fltresults = mysqli_query($connect, $flt);

	while ($frow = mysqli_fetch_array($fltresults, MYSQLI_BOTH)) {
		$lb_dhrs = $frow['SUM(log_dayhrs)'];

		echo "$lb_dhrs <-<br>";
	}
	
	$pilotlist .= "
		<b>$p_callsign</b> from <b>$pilotsa $pilotsb</b> has <b>$lb_dhrs</b> total flight hours.<br>";
}

echo "The following Squadron Data is as follows:<br><br>$pilotlist";

The third WHILE query in there has been driving me nuts. It’s supposed to grab the SUM() of all the integers in the ‘log_dayhours’ field in the table where the pilot’s ID is $p_id. However, it’s simply not working. I’ve tried changing the SUM(log_dayhours) to SUM(log_dayhours) as SUM also, but that doesn’t work either. The result of the query ends up as a NULL value.

The thing that really doesn’t make sense is that if I take just the SQL query from that section and plug it into PHPMyAdmin’s SQL query, it works - it gives me a single result with the total of all 3 table entries (in this case the “1” is one of the log_pilotid numbers):

SELECT SUM(log_dayhrs) FROM logbook WHERE log_pilotid = "1"

I’m not understanding what’s happening here at all.

The log_dayhrs field in the table is a SMALLINT field, and the log_pilotid field is an INT.

Clearly I’m not doing something right, but I don’t know what that might be. I have a bunch of older sites using some deprecated code that do this exact same thing with no problems… this code is, in fact, a copy / edit from one of those sites that works.

When struggling to find solutions I use the following debug function:

//==================================
// Usage:
//     debug($val, __line__); // second parameter optional 
//==================================
function debug($var='No $var', $title='NO $title')
{
  $sVar = print_r($var, TRUE); 

  $style = 'width:88%; 
			margin:2em auto; 
			background-color:snow; 
			border:solid 1px red;
			';		    

	$gType = gettype($var);
	echo '<dl style="' .$style .'">';
		echo '<dt>' .$title .' ==> gettype(' .$gType .') </dt>';
    echo '<dd><pre>' .$sVar .'</pre>';
	echo '</dl>';
}

Edit:
The function will display booleans, strings, arrays objects, etc ,
Also the data will be prettified by using var to add linefeeds

Also try adding this line before opening the database:

 mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

@John_Betong, var_dump will give the same exact info without all the code overhead. Just sayn.

OP, an SQL JOIN is your friend here. Post or PM an SQL dump of your DB schema with a few sample records and I will show you how to do this.

3 Likes

I tried var_dump(…) on an array and was most surprised!!!

/var/www/a-test-to-see-if-it-works.tk/public_html/index.php:89:
array (size=5)
  0 => string 'one' (length=3)
  1 => string 'two' (length=3)
  2 => string 'three' (length=5)
  3 => string 'four' (length=4)
  4 => string 'five' (length=4)
1 Like

I wondered whether that was because you needed to use a non-reserved word for the alias, but it worked for me in the admin screen on a test table. Did you remember to change your code to use $frow['SUM'] as well?

In this extra queries where you are retrieving squadron details and the like, will there be more than one row returned? If not, the while() loop seems superfluous, though probably won’t do any harm. Likewise setting it to return MYSQLI_BOTH when you only use the named fields is not really needed.

But as @benanamen said above, you could probably do a single query with some JOINS to get all this in one go.

Here is part of the query showing a couple SUMs and JOINS. You have mismatched data from not having foreign key constraints. Fix your DB and Data and send me the new DB dump.

For anyone reading this post that doesn’t know about Mysql’s ANY_VALUE you will want to learn about it here.

* The AS’s are optional.

SELECT
	p.*,
	ANY_VALUE ( l.log_timestamp ) log_timestamp,
	ANY_VALUE ( l.log_server ) log_server,
	ANY_VALUE ( l.log_notes ) log_notes,
	ANY_VALUE ( s.squad_name ) squad_name,
	SUM( l.log_dayhrs ) log_dayhrs,
	SUM( l.log_nighthrs ) log_nighthrs 
FROM
	caw1_pilots AS p
	LEFT JOIN caw1_logbook AS l ON p.pilot_id = l.log_pilotid
	LEFT JOIN caw1_greenie AS g ON p.pilot_id = g.greenie_id
	LEFT JOIN caw1_squadrons AS s ON p.pilot_squadron = s.squad_id 
GROUP BY
	p.pilot_id

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.