whats up with this query/select?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
wmguk
Forum Newbie
Posts: 16
Joined: Wed Jan 30, 2008 6:16 am

whats up with this query/select?

Post by wmguk »

Couple of questions, how would i use % - basically if $month isnt assigned, how can i say show everything?

Also i get this error on running this query :
Query Error:6: Unknown column 'year' in 'where clause'
Query: SELECT 'YEAR(orderdate) AS year', 'MONTH(orderdate) AS month', '*' FROM orders WHERE year=true AND month=true AND status=true AND login=true
however this is datetime, i already have these working...

Code: Select all

$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 :(

this is my complete query section

Code: Select all

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>" );
 
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Re: whats up with this query/select?

Post by Chris Corbyn »

I haven't every seen the YEAR() and MONTH() functions before. I assume the version of MySQL you're trying to run the queries on is not compatible.

Try using DATE_FORMAT() instead and just pulling out the year "%Y".
wmguk
Forum Newbie
Posts: 16
Joined: Wed Jan 30, 2008 6:16 am

Re: whats up with this query/select?

Post by wmguk »

Chris Corbyn wrote:I haven't every seen the YEAR() and MONTH() functions before. I assume the version of MySQL you're trying to run the queries on is not compatible.

Try using DATE_FORMAT() instead and just pulling out the year "%Y".
ah i could try that lol, i will have a lookski, but the other 5 SQL's all work and some are using YEAR and MONTH, so it cant be that.. its odd :(
Post Reply