[SOLVED] AND / OR Query Help

wmguk · May 23, 2008

Hey,

I have a database containing orders, Basically I need a query that will show:

album name

month

year

delivery status

so I can select say ALL albums in March 2008 or all Delivered Items in 2006 etc etc, The drop downs are populated by a query, however I am struggling doing a 4 way or/and query.

can anyone help?

my searchresults.php page

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']."'"; }

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

$result6=mysql_query($sql6)or die( "<strong>Query Error:6</strong>: " . mysql_error() . "<br><strong>Query</strong>: $sql6<br><br>" );

I get an error message :

Query Error:6: Unknown column 'year' in 'where clause'

Query: SELECT 'YEAR(orderdate) AS year', 'MONTH(orderdate) AS month', '*' FROM orders WHERE year = '*' AND month = '*' AND status = '*' AND albumname = '*'

any ideas?

Thanks in advance, there must be a simple and / or query I can use?

BlueSkyIS · May 23, 2008

fixing invalid SQL:

$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'";

wmguk · May 23, 2008

ah ha!

Excellent thank you, now i get the page layout with no errors if i select show all, however if i select an album name i now get this error:

Query Error:6: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cartern''' at line 1

Query: SELECT YEAR(orderdate) AS year, MONTH(orderdate) AS month, '*' FROM orders WHERE 'year = '*'' AND 'month = '*'' AND 'status = '*'' AND 'albumname = 'cartern''

BlueSkyIS · May 23, 2008

you've got single-quotes messed up a little in your query. compare with my example.

wmguk · May 23, 2008

you've got single-quotes messed up a little in your query. compare with my example.

yes sorry, I just noticed that right, I think ive got the single quotes sorted now, however i still get this error

Query Error:6: Unknown column 'year' in 'where clause'

Query: SELECT YEAR(orderdate) AS year, MONTH(orderdate) AS month, '*' FROM orders WHERE year = '*' AND month = '*' AND status = '*' AND albumname = 'cartern'

wmguk · May 23, 2008

Ok, found loads of silly mistakes with this code, so this is the new version

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

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

but i still get :

Query Error:6: Unknown column 'year' in 'where clause'

Query: SELECT YEAR(orderdate) AS year, MONTH(orderdate) AS month, '*' FROM orders WHERE year = '*' AND month = '*' AND status = '*' AND albumname = 'cartern'

however I thought im telling it that YEAR(orderdate) is year etc etc?

BlueSkyIS · May 23, 2008

you shouldn't use function names as aliases. that may be the problem. i'd change YEAR(orderdate) AS year to something more like YEAR(orderdate) AS album_year. ditto for MONTH()

wmguk · May 23, 2008

ah ha, okies

I have now change it to:

if (empty($_POST['albumyear'])) { $where_year = "albumyear = '*'"; } else { $where_year = "albumyear = '".$_POST['albumyear']."'"; }
if (empty($_POST['albummonth'])) { $where_month = "albummonth = '*'"; } else { $where_month = "albummonth = '".$_POST['albummonth']."'"; }
if (empty($_POST['albumstatus'])) { $where_status = "albumstatus = '*'"; } else { $where_status = "albumstatus = '".$_POST['albumstatus']."'"; }
if (empty($_POST['albumname'])) { $where_albumname = "albumname = '*'"; } else { $where_albumname = "albumname = '".$_POST['albumname']."'"; }

$sql6="SELECT YEAR(orderdate) AS albumyear, MONTH(orderdate) AS albummonth, '*' FROM orders WHERE $where_year AND $where_month AND $where_status AND $where_albumname";

but i still get this error:

Query Error:6: Unknown column 'albumyear' in 'where clause'

Query: SELECT YEAR(orderdate) AS albumyear, MONTH(orderdate) AS albummonth, '*' FROM orders WHERE albumyear = '2008' AND albummonth = 'March' AND status = 'Payment Pending' AND albumname = 'cartern'

Barand · May 23, 2008

You cannot use column aliases in a WHERE clause.

BlueSkyIS · May 23, 2008

was just going to post that. news to me!!

http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html

Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This restriction is imposed because when the WHERE code is executed, the column value may not yet be determined. For example, the following query is illegal:

SELECT id, COUNT(*) AS cnt FROM tbl_name WHERE cnt > 0 GROUP BY id;

however,

You can use an alias to refer to a column in GROUP BY, ORDER BY, or HAVING clauses.

wmguk · May 23, 2008

oh no, thats a pain,

how else can i get the year information and month info from my database?

BlueSkyIS · May 23, 2008

just don't use aliases in the where:

$sql6="SELECT YEAR(orderdate) AS albumyear, MONTH(orderdate) AS albummonth, '*' FROM orders WHERE YEAR(orderdate) = 'some_value' AND MONTH(orderdate) = 'some_other_value' AND $where_status AND $where_albumname";

Barand · May 23, 2008

try

<?php
$where = array();
$whereclause = '';

if (!empty($_POST['albumyear'])) {
$albumyear = $_POST['albumyear']; 
$where[] = "YEAR(orderdate) = '$albumyear'";
}

if (!empty($_POST['albummonth'])) {
$albummonth = $_POST['albummonth']; 
$where[] = "MONTH(orderdate) = '$albummonth'";
}

if (!empty($_POST['albumstatus'])) {
$albumstatus = $_POST['albumstatus']; 
$where[] = "albumstatus = '$albumstatus'";
}

if (!empty($_POST['albumname'])) {
$albumname = $_POST['albumname']; 
$where[] = "albumname = '$albumname'";
}

$whereclause =  (count($where)) ? 'WHERE ' . join (' AND ', $where) : '';

$sql6="SELECT YEAR(orderdate) AS albumyear, MONTH(orderdate) AS albummonth, * FROM orders $whereclause";
?>

wmguk · May 24, 2008

try

<?php
$where = array();
$whereclause = '';

if (!empty($_POST['albumyear'])) {
$albumyear = $_POST['albumyear']; 
$where[] = "YEAR(orderdate) = '$albumyear'";
}

if (!empty($_POST['albummonth'])) {
$albummonth = $_POST['albummonth']; 
$where[] = "MONTH(orderdate) = '$albummonth'";
}

if (!empty($_POST['albumstatus'])) {
$albumstatus = $_POST['albumstatus']; 
$where[] = "albumstatus = '$albumstatus'";
}

if (!empty($_POST['albumname'])) {
$albumname = $_POST['albumname']; 
$where[] = "albumname = '$albumname'";
}

$whereclause =  (count($where)) ? 'WHERE ' . join (' AND ', $where) : '';

$sql6="SELECT YEAR(orderdate) AS albumyear, MONTH(orderdate) AS albummonth, * FROM orders $whereclause";
?>

ok, I think its starting to get there, the problem I have is, how do I get it to show all orders, i.e. if no information is selected, then I need to display all the orders in the database, so how can I show all

Barand · May 24, 2008

If all the search fields are empty then there is no WHERE clause therefore all records are selected.

wmguk · May 24, 2008

If all the search fields are empty then there is no WHERE clause therefore all records are selected.

ok that makes sense, now the issue I am having, is i now get this error:

Notice: Undefined variable: albumname in /var/www/vhosts/iwphoto.co.uk/httpsdocs/orders/searchresults.php on line 124

and i get the correct amount of rows, but they are all empty? I dont understand why it isnt showing the info within the database?

<?
while($row = mysql_fetch_array($result6)) 
{ 
$orderdate = $row['orderdate'];
$id = $row['id'];
$name_first = $row['name_first'];
$name_last = $row['name_last'];
$ordered_items = $row['ordered_items'];
$total = $row['total'];

$showdate = date('d M Y H:m', strtotime($row['orderdate']));
?> 
  <td width="80" class='main'><? echo "$showdate" ;?></td>  
  <td width="37" class='main'><? echo $id; ?></td> 
  <td align='left' class='main'><? echo $name_first ;?> <? echo $name_last ;?></td>
  <td width="250" align='left' class='main'><? echo $ordered_items ;?></td>
  <td width="40" align='center' class='main'><? echo $total ?></td>
  <td width="55" align='center' class='main'>  
  <form name='orderdetail' action='order_detail.php' method='POST'>
  <input type='hidden' name='id' value='<? echo $id ;?>'>
  <input type='submit' class='main' name='Submit' value='Details' onclick='return CheckForm()' />
  </form></td>
</tr>
  <? }?>

wmguk · May 24, 2008

current script:

<?
include "../scripts/connection.php"; 

$where = array();
$whereclause = '';

if (!empty($_POST['albumyear'])) {
$albumyear = $_POST['albumyear']; 
$where[] = "YEAR(orderdate) = '$albumyear'";
}

if (!empty($_POST['albummonth'])) {
$albummonth = $_POST['albummonth']; 
$where[] = "MONTH(orderdate) = '$albummonth'";
}

if (!empty($_POST['status'])) {
$status = $_POST['status']; 
$where[] = "status = '$status'";
}

if (!empty($_POST['albumname'])) {
$albumname = $_POST['albumname']; 
$where[] = "loginid = '$albumname'";
}

$whereclause =  (count($where)) ? 'WHERE ' . join (' AND ', $where) : '';

$sql="SELECT DISTINCT (loginid), albumname FROM orders";
$sql2="SELECT DISTINCT DATE_FORMAT(orderdate, '%M') AS albummonth, month(orderdate) AS monthnum FROM orders ORDER BY monthnum asc";
$sql3="SELECT DISTINCT YEAR(orderdate) AS albumyear 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 albumyear, MONTH(orderdate) AS albummonth, '*' FROM orders $whereclause";

$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>" );

//ALBUMS
$options="";
while ($row=mysql_fetch_array($result)) { 
    $id=$row["loginid"]; 
    $thing=$row["albumname"]; 
    $options.="<OPTION VALUE=\"$id\">".$thing.'</option>';
} 

//MONTHS
$options2=""; 
while ($row=mysql_fetch_array($result2)) { 
    $id2=$row["albummonth"]; 
    $thing2=$row["albummonth"]; 
    $options2.="<OPTION VALUE=\"$id2\">".$thing2.'</option>';
}

//YEARS
$options3=""; 
while ($row=mysql_fetch_array($result3)) { 
$id3=$row["albumyear"]; 
    $thing3=$row["albumyear"]; 
    $options3.="<OPTION VALUE=\"$id3\">".$thing3.'</option>';
}
//STATUS
$options4=""; 
while ($row=mysql_fetch_array($result4)) { 
$id4=$row["status"]; 
    $thing4=$row["status"]; 
    $options4.="<OPTION VALUE=\"$id4\">".$thing4.'</option>';
}
//GET ALBUM NAME
$options5=""; 
while ($row=mysql_fetch_array($result5)) { 
$id5=$row["login"]; 
    $thing5=$row["albumname"];
}
?> 
          <br />
          <form action="searchresults.php" class="main" name="searchtype" method="post" >
<table width="100%" border="0" cellspacing="3" cellpadding="0">
  <tr>
    <td colspan="2" class="header"><img src="../images/heading/order_current.gif" alt="" /></td>
  </tr>
  <tr>
    <td colspan="2" class="header">Please use the search options below to view your orders</td>
    </tr>
  <tr>
    <td> </td>
    <td><div align="left"><span class="style1"></span></div></td>
  </tr>
  <tr>
    <td><div align="right">Search for orders from :</div></td>
    <td>
      
        <div align="left" class="style1">
          <select name="albumname" class="main" id="albumname">
            <option value="">All Your Albums
              <?=$options;?>
              </option>
          </select>
        </div></td>
  </tr>
  <tr>
    <td><div align="right">During the Month of :</div></td>
    <td>
      
        <div align="left" class="style1">
          <select name="albummonth" class="main" id="albummonth">
            <option value=''>All Months
              <?=$options2;?>
              </option>
          </select>
        </div></td>
  </tr>
  <tr>
    <td><div align="right">And the Year of :</div></td>
    <td>
        <div align="left" class="style1">
          <select name="albumyear" class="main" id="albumyear">
            <option value=''>All Years
              <?=$options3;?>
              </option>
          </select>
        </div></td>
  </tr>
  <tr>
    <td><div align="right">With Delivery status of :</div></td>
    <td>
       <div align="left" class="style1">
          <select name="status" class="main" id="status">
            <option value=''>All Delivery Status
              <?=$options4;?>
              </option>
          </select>
        </div></td>
  </tr>
  <tr>
    <td> </td>
    <td><div align="right">
      <input name="submit" type="submit" class="main" id="submit" value="View Orders" />
    </div></td>
  </tr>
  <tr>
    <td>Displaying Orders From :</td>
    <td><?  if ($albumname == '' )
		{
		$albumname = 'All Albums' ;
		}
		else 
		{
		$albumname = $thing5 ; 
		}
		echo "$albumname" ;?></td>
  </tr>
  <tr>
    <td>For the Period of :</td>
    <td><?
   //SET MONTH
	    if ($albummonth == '' ){
		$albummonth = 'All Months' ;
		} else {
		$albummonth = $albummonth ; 
		}
	//SET YEAR	
		if ($albumyear == '' ){
		$albumyear = 'All Years' ;
		} else {
		$albumyear = $albumyear ;  }
		echo ("$albummonth in $albumyear") ;?>
            
  </td>
  </tr>
  <tr>
    <td>Using the Delivery Status of :</td>
    <td><? if ($status == '' ){
		$status = 'All Status' ;
		} else {
		$status = $status ; 
		}
		echo "$status" ;?></td>
  </tr>
</table>
          </FORM>

<p><img src='../images/heading/allorders.gif'></p>
      <table width="95%" border='1' align="center" cellpadding='5' cellspacing='0' bordercolor='cccccc'> 
  <tr> 
  <th align='center' bgcolor='#CCCCCC' class='main'><font color = '000000'>Date & Time<br />
  of Order</font></th> 
  <th align='center' bgcolor='#CCCCCC' class='main'><font color = '000000'>Order<br />
    Ref</font></th> 
  <th align='center' bgcolor='#CCCCCC' class='main'><font color = '000000'>Customer<br />
  Name</font></th>
  <th align='center' bgcolor='#CCCCCC' class='main'><font color = '000000'>Order<br />
  Description</font></th>
  <th align='center' bgcolor='#CCCCCC' class='main'><font color = '000000'>Order<br />
  Total</font></th>
  <th align='center' bgcolor='#CCCCCC' class='main'> </th>  
  </tr>
  <tr><?
while($row = mysql_fetch_array($result6)) 
{ 
$orderdate = $row['orderdate'];
$id = $row['id'];
$name_first = $row['name_first'];
$name_last = $row['name_last'];
$ordered_items = $row['ordered_items'];
$total = $row['total'];

$showdate = date('d M Y H:m', strtotime($row['orderdate']));
?> 
  <td width="80" class='main'><? echo "$showdate" ;?></td>  
  <td width="37" class='main'><? echo $id; ?></td> 
  <td align='left' class='main'><? echo $name_first ;?> <? echo $name_last ;?></td>
  <td width="250" align='left' class='main'><? echo $ordered_items ;?></td>
  <td width="40" align='center' class='main'><? echo $total ?></td>
  <td width="55" align='center' class='main'>  
  <form name='orderdetail' action='order_detail.php' method='POST'>
  <input type='hidden' name='id' value='<? echo $id ;?>'>
  <input type='submit' class='main' name='Submit' value='Details' onclick='return CheckForm()' />
  </form></td>
</tr>
  <? }?> 
      </table>

Barand · May 24, 2008

<td><? if ($albumname == '' )

Shouldn't that be

<td><? if ($thing5 == '' )

Also,

$sql6="SELECT YEAR(orderdate) AS albumyear, MONTH(orderdate) AS albummonth, '*' FROM orders $whereclause";

Why have you put the quotes round the * ?

That will just return a column in each row containing an "*"

wmguk · May 24, 2008

<td><? if ($albumname == '' )

Shouldn't that be

<td><? if ($thing5 == '' )

Also,

$sql6="SELECT YEAR(orderdate) AS albumyear, MONTH(orderdate) AS albummonth, '*' FROM orders $whereclause";

Why have you put the quotes round the * ?

That will just return a column in each row containing an "*"

The $thing5 relates to the drop down menu selector, if ive done this right it should only be used to populate the dropdown selector, but this is really testing my skill (or lack of skill) now...

I used the single quote because with out them I get this error:

Query Error:6: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM orders' at line 1

Query: SELECT YEAR(orderdate) AS albumyear, MONTH(orderdate) AS albummonth, * FROM orders

but if i put the single quote that error disappears?

Barand · May 24, 2008

try

$sql6="SELECT YEAR(orderdate) AS albumyear, MONTH(orderdate) AS albummonth, orders.* FROM orders $whereclause";

wmguk · May 24, 2008

try

$sql6="SELECT YEAR(orderdate) AS albumyear, MONTH(orderdate) AS albummonth, orders.* FROM orders $whereclause";

!!!!!!! EXCELLENT

Thank you, thats brilliant, everything except the month search is working now!

If I select all, rather than an actual status, ie, year, or delivery status etc i get this error:

Notice: Undefined variable: albumname in /var/www/vhosts/domain.co.uk/httpsdocs/orders/searchresults.php on line 236

Notice: Undefined variable: albummonth in /var/www/vhosts/domain.co.uk/httpsdocs/orders/searchresults.php on line 250

Notice: Undefined variable: albumyear in /var/www/vhosts/domain.co.uk/httpsdocs/orders/searchresults.php on line 256

Notice: Undefined variable: status in /var/www/vhosts/domain.co.uk/httpsdocs/orders/searchresults.php on line 266

how can i define the variable when it isnt selected?

Barand · May 24, 2008

try

<?
include "../scripts/connection.php"; 

$where = array();
$whereclause = '';
$albumyear = $albummonth = $status = $albumname = '';                    // ADD THIS LINE

...

?>

Barand · May 24, 2008

And

<?php

//MONTHS
$options2=""; 
while ($row=mysql_fetch_array($result2)) { 
    $id2=$row["monthnum"];                                                       // <<<< changed to "monthnum"
    $thing2=$row["albummonth"]; 
    $options2.="<OPTION VALUE=\"$id2\">".$thing2.'</option>';
}

wmguk · May 24, 2008

OMG!!!! you are marvellous!!

That has totally resolved the issues :)

Thank you soooooo much!!!!

Sign In

[SOLVED] AND / OR Query Help

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information