Hi I have a mysql/php site and what i am trying to do is get a query to add totals. there are aprox 800 records. I have a branch field, renewal date field amongst others. I need the branches to be listed with the total amount of members in each branch at a specified time. You can see what I am trying to do here. I am using dreamweaver with a recordset for each query resulting in about 70 recordsets. Is there an easier way to do these queries??. The code can be found here.
Any help is appreciated.
without going too much into your code (and there's a lot) I see that you basically are repeating the same set of SQL queries for each branch location. You could immediately reduce the number of queries by simply using a group by function as follows:
SELECT branch, count(1) as numrows
FROM members WHERE renewal_date > NOW()
GROUP BY branch;
This will return the values of each branch and the number of rows that meet the criteria. One thing to note though is that for branches that do not have any rows meeting the criteria, nothing will be returned. So in the output you show, the branch Ballarat, new members for this month will not show a row because not records exist that meet that criteria. So you will need to do a bit of coding in PHP to get these records working.
It is possible to get this working as a single query but it will lots of subquerying and makes the overall query complicated. For first effort try this and if it is not enough come back to us.