Adding Values from Mysql Rows and saving as var to be used later


#1

Hello all,
I have a DB that is a history of items checked out,
hw_id, type, chk_out
D29W4826, WT41N0, 2018-01-02 12:00:00
D29W2F9D, MC92N0, 2018-01-02 12:01:02
D29W4826, WT41N0, 2018-01-02 03:00:00

notice that each ID can be checked out multiple times a day …

i have a query:
SELECT COUNT(hw_type) AS Count,hw_type AS Type,hw_id AS Device FROM checkinout WHERE chk_out BETWEEN “2018-8-01 00:00:00” AND “2018-8-30 00:00:00” AND hw_type IN(“WT41N0”) GROUP BY hw_id, hw_type ORDER BY Count Desc

The above code results in the below:

Device Usage
Total Devices Used = 211
Total Devices Onhand = 212
Daily Utilization Used = 100%

Device Type Count
D29W4826 WT41N0 35
D29W2F9D WT41N0 34
D29W5F96 WT41N0 33

This is the PHP i am using to generate this report:

<?php
			$conn = new mysqli($hostname, $username, $password, $database);
			if ($conn->connect_error) {
				die("Connection failed: " . $conn->connect_error);
			} 
			$sql = 'SELECT COUNT(hw_type) AS Count,hw_type AS Type,hw_id AS Device FROM `checkinout` WHERE chk_out BETWEEN "2018-8-01 00:00:00" AND "2018-8-30 00:00:00" AND hw_type IN("WT41N0") GROUP BY hw_id, hw_type ORDER BY Count Desc';
			$result = $conn->query($sql);
			$row_cnt = mysqli_num_rows($result);
			$onhand = "212";
			$test = round($row_cnt/$onhand*100);
			echo '<br>Total Devices Used = '.$row_cnt;
			echo '<br>Total Devices Onhand = '.$onhand;
			echo '<br>Daily Utilization Used = '.$test.'%';
			
			while($row = $result->fetch_assoc()) {
				echo "<tr><td>".$row['Device']."</td>";
				echo "<td>".$row['Type']."</td>";
				echo "<td>".$row['Count']."</td></tr>";
			}
			$conn->close();
		?>

What i am trying to accomplish is get the SUM of the Count column so i can get a per day avg Utilz of the devices over time as you can see the $test var is wrongly calculated as it does not have the correct numbers.


#2
$sql = 'SELECT COUNT(hw_type) AS Count,hw_type AS Type,hw_id AS Device FROM `checkinout` WHERE chk_out BETWEEN "2018-8-01 00:00:00" AND "2018-8-30 00:00:00" AND hw_type IN("WT41N0") GROUP BY hw_id, hw_type ORDER BY Count Desc';
			

This query creates a count of the type. Just add a counter in your while loop to add the count to total it up.
You could create a select using two selects as your inputs and then, you would not have to use a counter in
a loop. Something like this might work… (Not tested as I do not have your database to test against!)

$sql = 'SELECT SUM(A.Count) AS TotalCount, * FROM
      ( SELECT COUNT(hw_type) AS Count,hw_type AS Type,hw_id AS Device FROM `checkinout` WHERE chk_out BETWEEN "2018-8-01 00:00:00" AND "2018-8-30 00:00:00" AND hw_type IN("WT41N0") GROUP BY hw_id, hw_type ORDER BY Count Desc ) A'

This takes the query you already have and assign it to the results named “A”. Then, you can use that to select the sum from giving you a total of all counts. The SELECT part gets both the total count and the other info from the previous query. So, test it and let us know if it works for you.


#3

That worked like a charm, cannot believe i didnt think of that … /facepalm. Thank You!


#4

Glad it worked out for you! We will see you in your next post…