Results 1 to 3 of 3

Thread: mysql query

  1. #1
    Join Date
    Oct 2010
    Posts
    5

    Unanswered: mysql query

    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.

    Thanks, Bernie

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi Bernie,

    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:

    Code:
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Oct 2010
    Posts
    5

    mysql query

    Thanks for the advice, I thought it might be a bit complicated, but I will try your comment and see how I go with that.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •