whats up with this query/select?

[wmguk]

hey

 

im currently using

$sql6="SELECT 'YEAR(orderdate) AS year', 'MONTH(orderdate) AS month', '*' FROM orders WHERE year=$where_year AND month=$where_month AND status=$where_status AND login=$login";

 

however i get an error of :

 

Unknown column 'year' in 'where clause'

 

but im assigning year in the select i thought?

 

any thoughts?

[cooldude832]

what field type is orderdate?

[wmguk]

its datetime, i already have these working...

 

$sql="SELECT DISTINCT (loginid), albumname FROM orders";
$sql2="SELECT DISTINCT DATE_FORMAT(orderdate, '%M') AS month, month(orderdate) AS monthnum FROM orders ORDER BY monthnum asc";
$sql3="SELECT DISTINCT YEAR(orderdate) AS year FROM orders ORDER BY orderdate asc"; 
$sql4="SELECT DISTINCT (status) FROM orders ORDER BY status desc"; 
$sql5="SELECT * FROM album WHERE login = '$albumname'";

 

So i was thinking if they work, then the sql6 (the other query) should work

[wmguk]

this is my complete query section

 

if (empty($_POST['year'])) { $where_year = "true"; } else { $where_year = "year = '".$_POST['year']."'"; }
if (empty($_POST['month'])) { $where_month = "true"; } else { $where_month = "month = '".$_POST['month']."'"; }
if (empty($_POST['status'])) { $where_status = "true"; } else { $where_status = "status = '".$_POST['status']."'"; }
if (empty($_POST['albumname'])) { $where_albumname = "true"; } else { $where_albumname = "albumname = '".$_POST['albumname']."'"; }


$sql="SELECT DISTINCT (loginid), albumname FROM orders";
$sql2="SELECT DISTINCT DATE_FORMAT(orderdate, '%M') AS month, month(orderdate) AS monthnum FROM orders ORDER BY monthnum asc";
$sql3="SELECT DISTINCT YEAR(orderdate) AS year FROM orders ORDER BY orderdate asc"; 
$sql4="SELECT DISTINCT (status) FROM orders ORDER BY status desc"; 
$sql5="SELECT * FROM album WHERE login = '$albumname'";
$sql6="SELECT 'YEAR(orderdate) AS year', 'MONTH(orderdate) AS month', '*' FROM orders WHERE year=$where_year AND month=$where_month AND status=$where_status AND albumname=$where_albumname";


$result=mysql_query($sql)or die( "<strong>Query Error:</strong>: " . mysql_error() . "<br><strong>Query</strong>: $sql<br><br>" );
$result2=mysql_query($sql2)or die( "<strong>Query Error:2</strong>: " . mysql_error() . "<br><strong>Query</strong>: $sql2<br><br>" );
$result3=mysql_query($sql3)or die( "<strong>Query Error:3</strong>: " . mysql_error() . "<br><strong>Query</strong>: $sql3<br><br>" );
$result4=mysql_query($sql4)or die( "<strong>Query Error:4</strong>: " . mysql_error() . "<br><strong>Query</strong>: $sql4<br><br>" );
$result5=mysql_query($sql5)or die( "<strong>Query Error:5</strong>: " . mysql_error() . "<br><strong>Query</strong>: $sql5<br><br>" );
$result6=mysql_query($sql6)or die( "<strong>Query Error:6</strong>: " . mysql_error() . "<br><strong>Query</strong>: $sql6<br><br>" );

 

also, how would i use % - basically if $month isnt assigned, how can i say show everything then?

[wmguk]

still lost on this, any ideas?

[wmguk]

?

[wmguk]

any thoughts?

[redarrow]

you need to use a join and also use the date function properly...

 

you dont need so meny mysql querys......

 

please read below.

 

date mysql function

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

 

join mysql function

http://dev.mysql.com/doc/refman/5.0/en/join.html