hej613
asked on
Store values in array before processing from database?
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...
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...
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
That will give you 2 rows in the result set, one count for male and one for female.
@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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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:
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...
}
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@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
@hej613: In the code snippet at line 2 it says extract($GLOBALS);. What does that do?
Best to all, ~Ray
ASKER
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:
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Did you see my comment on ROLLUP - that is what you want to be looking at.
ASKER
Open in new window
The goal would be get a count of male and females in this one pass