Page 1 of 1

Weird Join

Posted: Tue Jan 25, 2005 10:11 pm
by AliasBDI
I thought that a JOIN select would do what I want but I could not pull it off. I have two tables (var_month, var_year) that I need to combine into a list. I need it to get all of the var_year records (2004, 2005, ...) and combine each var_month with every year. So when I echo it, it looks like this:

January 2005
February 2005
March 2005
...
January 2006
Febrauary 2006
March 2006
...*you get the idea.

How would that query look?

Posted: Tue Jan 25, 2005 10:16 pm
by timvw

Code: Select all

SELECT * FROM var_months, var_year

this will result in the product of the 2 tables.

Posted: Tue Jan 25, 2005 10:24 pm
by AliasBDI
But how would I output that? And sort them in date order?

Posted: Tue Jan 25, 2005 11:19 pm
by feyd
if you are storing the months by value, instead of name: ORDER BY _year, _month

if you're storing by name, tt's a pain with the SQL, but not so much with php.

As for displaying, once you have it sorted, that's easy.

Posted: Mon Feb 14, 2005 12:25 pm
by AliasBDI
Well, there is more to the problem... Here is my code for the query:

Code: Select all

<?
$thisYEAR = date("Y");
$thisMONTH = date("m");

mysql_select_db($database_econtrolsgc, $econtrolsgc);
$query_calendarLIST = "SELECT * FROM var_month, var_year WHERE var_year.active = 'Y' AND var_month.active = 'Y' AND var_year.name >= $thisYEAR AND var_month.singleDigit >= $thisMONTH ORDER BY var_year.name ASC, var_month.doubleDigit ASC"; ?>
Right now it will not show any January records in the list (because this month is passed January). The query WHERE reads "var_month.singleDigit >= $thisMONTH" which does that. I need it to run "var_month.singleDigit >= $thisMONTH" once, then all after that. In other words, I need it to get all months proceeding this month.

Posted: Mon Feb 14, 2005 12:28 pm
by feyd
look into UNION syntax.