Store values in array before processing from database?

hej613
hej613 used Ask the Experts™
on
Morning Experts - I have a page I am looking to write to replace some OLD code on one of our applications - Basically its a head count for a jail - I was wondering how to best accomplish it.  For example, I need a count of all males and females currently in prison, then I need a count of all sentenced and unsentenced males/females in the prison, finally I need a count of all people currently in prison by location code... I am curious if its better to write server SQL queries to get the data (several small count statements) or just one main large select statement and somehow process through the code with PHP and update counters as I go?

For example some of the fields I have in the database (just the ones I'm interested in)

Gender
Location
Sentenced status

I would think I could just do a
select * from JMS_01MAST where active_booking is not null

and in the while statement somehow do
if (gender = 'M')
{
$m_total++;
}
else if (gender = 'F')
{
$f_total++;
}

Then I could redo that same statement somehow and count the other columns?

I know there must be a way to get this all in one big pass, but I cant figure it out...
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
in an effort to give a bit better data, hereis my first pass at the bare bones php...

<?php
extract($GLOBALS);
set_time_limit(0);
require("dbconn.php");

//create date for adding to the report
$today = date("Ymd");

//reset count to 0 and declare variables.
$m_total = 0;
$f_total = 0;

//SQL statement
$sql = 'select firstname, lastname, gender, race, jm1_mni_jktnum
		from jms_01mast, mni_01mast
		where jm1_mni_link = dbnumber
		and jm1_mni_link_seq = dbseq
		and jm1_active_booking is not null';


$results = $myConn->query($sql);

//Check for errors
if(DB:: isError($results))
{
	echo $results ->getDebugInfo();
	exit;
}

//Loop through query counting m_total and f_total in one pass
while ($row = $results->fetchRow(DB_FETCHMODE_ASSOC))
{
	
}


//Display results
echo "Total Males:";

?>

Open in new window


The goal would be get a count of male and females in this one pass
Most Valuable Expert 2011
Top Expert 2016

Commented:
Why not use separate queries?  The number of inmates is not a large number in SQL terms and little will be gained by trying to write one big fat query, when what you really want is three separate results sets.  You can make three queries, get three results sets, and then with the results sets you could build the web page.  I think it would be easier that way.
Why not just issue a count query for each count you need. That'd be far more efficient than retrieving and looping through ALL the results, especially if you're going to do it multiple times. You can get both male and female in one query by using group by gender.
SELECT COUNT(*) FROM inJail WHERE sentenced = 1 GROUP BY gender

Open in new window

That will give you 2 rows in the result set, one count for male and one for female.
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Most Valuable Expert 2011
Top Expert 2016

Commented:
@Derokorian:  I've never benchmarked it, but I am curious.  Would it be faster to SELECT COUNT(*) or SELECT the auto_increment id and use mysql_num_rows()?  What's the best practice here?
I have always read and seen COUNT to be faster especially on larger tables. It seems you would then be getting a result set from the DB then using PHP to count it when you can just ask the DB to count it for you. I'll build a test and post the results.
Most Valuable Expert 2011
Top Expert 2016
Commented:
Thanks.  If the MySQL server is on a separate processor, I can certainly see the utility of off-loading all of the work that can be off-loaded!
Testing with a table size of 5000 rows. Each test type (mysql count, mysql num_rows, mysqli count, etc) was run 500 times each.
Result Set 1:
Testing COUNT(*) vs num_rows using MySQL, MySQLi, and PDO...
Testing with mysql on localhost...
Creating and filling table... Complete!
Beginning Test of Count(*)... Complete!
Beginning test of num_rows (or similar)... Complete!
MySQL COUNT(*)
	Min:	0.046968 ms
	Max:	1.871109 ms
	Avg:	0.060625 ms
	Median:	0.056028 ms
MySQL num_rows
	Min:	3.751040 ms
	Max:	7.720947 ms
	Avg:	4.073465 ms
	Median:	4.025936 ms
MySQLi COUNT(*)
	Min:	0.046015 ms
	Max:	0.577211 ms
	Avg:	0.055756 ms
	Median:	0.054121 ms
MySQLi num_rows
	Min:	3.568888 ms
	Max:	6.212950 ms
	Avg:	3.795136 ms
	Median:	3.802061 ms
PDO COUNT(*)
	Min:	0.046968 ms
	Max:	1.315832 ms
	Avg:	0.060505 ms
	Median:	0.056028 ms
PDO rowCount()
	Min:	3.883839 ms
	Max:	6.447792 ms
	Avg:	4.139980 ms
	Median:	4.125118 ms
Test complete. Performing cleanup...Complete!

Open in new window


Result Set 2:
Testing COUNT(*) vs num_rows using MySQL, MySQLi, and PDO...
Testing with mysql on remost host...
Creating and filling table... Complete!
Beginning Test of Count(*)... Complete!
Beginning test of num_rows (or similar)... Complete!
MySQL COUNT(*)
	Min:	39.001942 ms
	Max:	323.285103 ms
	Avg:	67.754380 ms
	Median:	48.503876 ms
MySQL num_rows
	Min:	135.227919 ms
	Max:	1208.184958 ms
	Avg:	313.135995 ms
	Median:	273.215055 ms
MySQLi COUNT(*)
	Min:	38.088799 ms
	Max:	290.810108 ms
	Avg:	65.513473 ms
	Median:	47.893047 ms
MySQLi num_rows
	Min:	212.701082 ms
	Max:	1173.928976 ms
	Avg:	318.914172 ms
	Median:	269.827843 ms
PDO COUNT(*)
	Min:	39.821863 ms
	Max:	379.276037 ms
	Avg:	70.623504 ms
	Median:	48.815012 ms
PDO rowCount()
	Min:	220.091105 ms
	Max:	1131.749868 ms
	Avg:	314.975175 ms
	Median:	276.443005 ms
Test complete. Performing cleanup...Complete!

Open in new window

As you can see, COUNT(*) is 3-7x faster on avg.
Mohamed AbowardaSenior Software Engineer

Commented:
You have different ways:
1. After executing SQL statement, you can use mysql_num_rows() to count the number of the results.
2. Use COUNT() in you SQL statement.
3. You can use the following to go through EACH row in your table:
$sql = mysql_query("select * from JMS_01MAST where active_booking is not null");
while($row = mysql_fetch_assoc($sql))
{
          // Perform anything on each row here...
}

Open in new window

Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
I think it is ROLLUP you should be looking at. Same as doing individual count statements - db server is optomised for doing this sort of query which is why it makes more sense to do it on the db server than in script. If the ROLLUP does not work 100% look at writing a stored procedure also should be quicker than script.
Most Valuable Expert 2011
Top Expert 2016

Commented:
@Derorkorian: Thanks, that's good to know about count(*), especially if we had a large results set.  I would expect that the WHERE clause could influence the time it takes to run the queries, especially if it addressed a column that was not indexed ;-)

@hej613: In the code snippet at line 2 it says extract($GLOBALS);.  What does that do?

Best to all, ~Ray

Author

Commented:
Thanks for all the great feedback!  

Ray - I used a template from one of the other reports in the system they extract a bunch of variables they use throughout the application - I need to remove that line, I'm not using it.

As I'm not really familiar with the PHP /Database is it possible to process information as its working through the database?

For instance, could I:

while ($row = $results->fetchRow(DB_FETCHMODE_ASSOC))
{
if(row['JM1_GENDER'] == 'M')
m_total++;
if(row['JM1_GENDER']=='F')
f_total++;
	
}

and get a total counts of males/females in that one query?

Open in new window

Most Valuable Expert 2011
Top Expert 2016
Commented:
Yes, that looks like a familiar way of doing things.  Of course the success of that strategy will depend on a SELECT query that included the column JM1_GENDER in the results set.
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Did you see my comment on ROLLUP - that is what you want to be looking at.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial