How to query between single dates

[Travis959]

I have two columns in my MySQL table that list a month (01,02,03,etc) and a year on each row. The user can choose between what months and what years they would like to pull data from, so I cannot change how the table is laid out.

 

However in my query, I have this:  AND month BETWEEN '$startmonth' AND '$endmonth' AND year BETWEEN '$startyear' AND '$endyear';

 

Which works fine whenever the first month they choose is before the second month they choose. For instance, it works fine if you pick January 2011 as the first option and September 2011 as the second option. However this query will not work if you try to do September 2005 as the first option and January 2011 as the second option.

 

Does anyone know of a way to fix this?

 

[PFMaBiSmAd]

Assuming you have the leading zero's stored with the month -

 

AND CONCAT(year,month) BETWEEN '$startyear$startmonth' AND '$endyear$endmonth'

[Travis959]

That works perfect. Thank you.

 

Another quick question, is there a way to add up all the columns that have the same value in a field? For instance, I have a field with a name in it. There is about 20 rows with the same name in this field, but different values in other fields. I was GROUPing by this name field, but that only gives me the values from the first row. Can I add all the different values up and still GROUP the fields?

[PFMaBiSmAd]

An example showing what you want would help.

[Travis959]

 

In the image above you can see the numerous rows that have "CUSTOMER ALW" in field5. If I wanted to combine all these into one row, and total field9 and field10 separately (like if I used SUM on each field), how would I go about doing that?

 

The reason I want to combine, is because this query is a part of a WHILE loop that has to display whatever fields it queries, and I need it to only show one row, but have all the values totaled together. Hope this explains a little better.

 

[attachment deleted by admin]

[PFMaBiSmAd]

Should work -

SELECT SUM(field9) as total9, SUM(field10) as total10 FROM your_table .... GROUP BY field5

 

this query is a part of a WHILE loop

 

^^^ That's generally a bad idea and takes a lot more server resources. If you are doing something like looping over each company name or looping over dates or ranges of dates, you can generally do this all in 1 (one) query and then simply display the results the way you want. What's your overall code from the first query statement to end of the main while loop?