[SOLVED] Combine Queries

[hostfreak]

I was wondering how I would combine queries that are stored in an array? The reason is to be able to order the multiple queries from the same "ORDER BY" which will be obtained from an url parameter.

 

Also, the reasons the queries are in an array is, because they use the "WHERE" clause to SELECT "WHERE year = '$year'", and there could be several years (the years are obtained from $_POST) and I don't think there is a way to do something like: WHERE year = '$year[0],$year[1]'. Thanks in advance.

[Barand]

.... WHERE year IN ($year[0], $year[1])

 

or, easier,

 

$yrs = join (',', $year);

 

.... WHERE year IN ($yrs)

[hostfreak]

I won't always be sure of the amount of years, so in this case just run it through a foreach loop and it will still work, correct?

[Barand]

see my edit to my post

[hostfreak]

Ah, once again Barand, thanks.

[Barand]

BTW, in cases where this technique can't be used, the answer to your original question is

 

SELECT a,b,c FROM table WHERE something

UNION

SELECT a,b,c FROM table WHERE something_else

UNION

SELECT ..... etc

[hostfreak]

Got rid of the arrays, ran the modified query, no result. Could it be due to me doing this:

YEAR(time_stamp) IN ($yrs)

 

I am actually comparing the years against the year found from the field 'time_stamp' (0000-00-00 00:00:00) with the mysql YEAR() function. Not sure if that matters?

[Barand]

What column type is "time_stamp" ?

[hostfreak]

BTW, in cases where this technique can't be used, the answer to your original question is

 

SELECT a,b,c FROM table WHERE something

UNION

SELECT a,b,c FROM table WHERE something_else

UNION

SELECT ..... etc

 

I had read about UNION and wanted to try it in this situation, but wasn't sure how to implement it where the queries were stored in an array then looped through as well as the results (as this is, or was if the other code works).

[hostfreak]

time_stamp is a "datetime" column type.

[Barand]

Something like

 

WHERE YEAR(time_stamp) IN (2005, 2006, 2007)

 

should work OK in that case.

 

If you echo the querystring that you are executing, what do you get?

[hostfreak]

Echoing the querystring yields:

SELECT equipment_number, plant, id, YEAR(time_stamp) as year, MONTHNAME(time_stamp) as month FROM work_orders_one WHERE equipment_number = '2' AND YEAR(time_stamp) IN (2007,2010) ORDER BY equipment_number ASC

[hostfreak]

Opps, I had a problem in my while loop. Your code works perfectly, sorry and thanks.

[hostfreak]

Also, if you have time to answer, for my own knowledge, how would one go about using UNION if the queries are contained in an array?