[SOLVED] Unknown Column Name error??

[laPistola]

Im doing a MONTHNAME() as month and trying to use it in WHERE to only show results thats with the selected month but im getting the error unknown column name

 

The query is

SELECT *, TIME_FORMAT('`time`','%H:%i') as fTime, MONTHNAME(`date`) as `month`, YEAR(`date`) as `year`, DATE_FORMAT('`date`','%d/%m/%y ') as fDate FROM gigs WHERE `month` = $cMonth AND `year` = $cYear ORDER BY `date` ASC

 

Am i doing something wrong?

 

Thank you in advance

[MadTechie]

can you  do a structure dump so i can recreate the table gigs

and what are you trying to find ?

everything on a set month ?

[MadTechie]

Quick example

 

SELECT * FROM `test0001` WHERE YEAR(`mydate`)=2009 AND MONTH(`mydate`)= 6

 

your statement should be

SELECT *, TIME_FORMAT(`time`,'%H:%i') as fTime, MONTHNAME(`date`) as `month`, YEAR(`date`) as `year`, DATE_FORMAT('`date`','%d/%m/%y ') as fDate FROM gigs WHERE MONTHNAME(`date`) = $cMonth AND YEAR(`date`) = $cYear ORDER BY `date` ASC

[laPistola]

this is the whole database upto yet

 

CREATE TABLE `gigs` (
  `gid` int(11) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `time` time NOT NULL,
  `venue` text NOT NULL,
  `city` text NOT NULL,
  PRIMARY KEY (`gid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

INSERT INTO `gigs` (`gid`, `date`, `time`, `venue`, `city`) VALUES
(1, '2009-07-05', '22:00:00', 'The Oddies', 'Royton'),
(2, '2009-07-07', '19:00:00', 'The Brambles', 'Bury'),
(3, '2009-08-02', '14:00:00', 'The Lodge', 'Cheetham Hill Rd, Dukinfield'),
(4, '2009-06-07', '21:00:00', 'Tapios Bar & Restaurant', '???');

 

basicly yes, the user selects a month they would like to see the gigs list for which is passed by full month name in the url then into the var $cMonth via a $_GET['month'], doing it using MONTHNAME was for the sole reason the month name is to be displayed on the page so thought i would kill two birds in one.

 

Thank you for looking into it!

[MadTechie]

Assuming

$cMonth = 7

and

$cYear = 2009

 

this

SELECT *, TIME_FORMAT(`time`,'%H:%i') as fTime, MONTHNAME(`date`) as `month`, YEAR(`date`) as `year`, DATE_FORMAT('`date`','%d/%m/%y ') as fDate FROM gigs WHERE MONTHNAME(`date`) = 07 AND YEAR(`date`) = 2009 ORDER BY `date` ASC

returns

gid date time venue city fTime month year fDate

1 2009-07-05 22:00:00 The Oddies Royton 22:00 July 2009 NULL

2 2009-07-07 19:00:00 The Brambles Bury 19:00 July 2009 NULL

 

so

 

your statement should be

SELECT *, TIME_FORMAT(`time`,'%H:%i') as fTime, MONTHNAME(`date`) as `month`, YEAR(`date`) as `year`, DATE_FORMAT('`date`','%d/%m/%y ') as fDate FROM gigs WHERE MONTHNAME(`date`) = $cMonth AND YEAR(`date`) = $cYear ORDER BY `date` ASC

 

EDIT:

the mistake was assuming the SELECT alias would remain in the WHERE clause which they don't.

[laPistola]

At the min $cMonth is echoing June (as it should) your correct about year.

 

i did before try using MONTHNAME(`month`) in the where and got the same column error i now realise i was an idiot cus month should have been date, while waiting for your reply i did try

 

WHERE MONTHNAME(`date`) = '$cMonth' AND YEAR(`date`) = '$cYear'

 

which worked but didn't return the two records only one?

 

just trying your query and it returned the unknown june column error??

 

so i put ' around the var as below:

'$cMonth'

 

which worked but still only returned the one row not two?

[MadTechie]

the single quote are not needed,

can you run this query via PMA

to check what results your getting

 

 SELECT * , TIME_FORMAT( `time` , '%H:%i' ) AS fTime, MONTHNAME( `date` ) AS `month` , YEAR( `date` ) AS `year` , DATE_FORMAT( '`date`', '%d/%m/%y ' ) AS fDate
FROM gigs
WHERE MONTHNAME( `date` ) =7
AND YEAR( `date` ) =2009
ORDER BY `date` ASC
LIMIT 0 , 30 

as on mine i get 2 results

 

also check the values of $cMonth and $cYear (echo the query)

 

[laPistola]

I found out why it was only returning the one, we both missed it, are you ready for this

 

(1, '2009-07-05', '22:00:00', 'The Oddies', 'Royton'),
(2, '2009-07-07', '19:00:00', 'The Brambles', 'Bury'),
(3, '2009-08-02', '14:00:00', 'The Lodge', 'Cheetham Hill Rd, Dukinfield'),
(4, '2009-06-07', '21:00:00', 'Tapios Bar & Restaurant', '???');

 

the var $cMonth contains June (the current month as it should) there is only one record inserted for june, DOPE

 

it should have been

(3, '2009-06-02', '14:00:00', 'The Lodge', 'Cheetham Hill Rd, Dukinfield'),

 

anyway the final query worked fine which is

SELECT *, TIME_FORMAT(`time`,'%H:%i') as fTime, MONTHNAME(`date`) as `month`, YEAR(`date`) as `year`, DATE_FORMAT(`date`,'%d/%m/%y') as fDate FROM gigs 
WHERE MONTHNAME(`date`) = '$cMonth' AND YEAR(`date`) = $cYear 
ORDER BY `date` ASC

 

 

Thank you for all the help

[MadTechie]

I was testing 7 = July

in anycase solved