mySQL OR returning all results..

[Jonathan_Fortis]

I have a select statement that is checking if a product matches an orderID. I have two tables for products and and orderDetail, at the moment all the records are being return rather that jkust the ones based on the orderID, i think is must be an issue with the OR in the statment, as seperatly (without the or) the correct results are being returned.

I need to say "WHERE orderdetails.Code = products.Code OR products1.Code

$colname_rsOrder = "-1";

if (isset($_SESSION['OrderID'])) {

  $colname_rsCustomer = $_SESSION['OrderID'];

}

mysql_select_db($database_momo, $momo);

$query_rsOrder = sprintf("SELECT * FROM momo_users, momo_orders, momo_orderdetails, momo_products, momo_fav WHERE momo_orders.OrderID = %s AND momo_orders.CustomerID = momo_users.userID AND momo_orderdetails.OrderID = momo_orders.OrderID AND (momo_orderdetails.ProductID = momo_products.Code OR momo_fav.Code)", GetSQLValueString($colname_rsOrder, "text"));

$query_limit_rsOrder = sprintf("%s LIMIT %d, %d", $query_rsOrder, $startRow_rsOrder, $maxRows_rsOrder);

$rsOrder = mysql_query($query_limit_rsOrder, $momo) or die(mysql_error());

$row_rsOrder = mysql_fetch_assoc($rsOrder);

[bregent]

I've answered this one for you before - several times. Please write the answer down this time so you can refer to it at a later date.

All expressions in a SQL WHERE clause evaluate to either true or false. In nearly every imaginable case, an expression consists of two terms separated by an evaulation operator. In your case you have two expression in your WHERE clause

1) orderdetails.Code = products.Code

2) products1.Code

See the problem?

[Jonathan_Fortis]

WHERE orderdetails.Code = products.Code OR products1.Code isnt the SQL, i was just saying i need to say

"WHERE orderdetails.Code = products.Code OR products1.Code"

the actual SQL i am tripping up on is

momo_orderdetails.ProductID = momo_products.Code OR momo_fav.Code

so i expanded this to

AND (momo_orderdetails.ProductID = momo_products.Code OR momo_orderdetails.ProductID = momo_fav.Code)

i think that is what you are saying..

i have tried this but it is still returning all results

thanks in advance

[Jonathan_Fortis]

the only way i have been able to make this work is by having two seperate querys. this however has its problems/restrictions.

$colname_rsOrder = "-1";

if (isset($_SESSION['OrderID'])) {

  $colname_rsOrder = $_SESSION['OrderID'];

}

mysql_select_db($database_momo, $momo);

$query_rsOrder = sprintf("SELECT * FROM momo_users, momo_orders, momo_orderdetails, momo_products WHERE momo_orders.OrderID = %s AND momo_orders.CustomerID = momo_users.userID AND momo_orderdetails.OrderID = momo_orders.OrderID AND momo_orderdetails.ProductID = momo_products.Code", GetSQLValueString($colname_rsOrder, "text"));

$rsOrder = mysql_query($query_rsOrder, $momo) or die(mysql_error());

$row_rsOrder = mysql_fetch_assoc($rsOrder);

$totalRows_rsOrder = mysql_num_rows($rsOrder);

mysql_select_db($database_momo, $momo);

$query_rsFav = sprintf("SELECT * FROM momo_users, momo_orders, momo_orderdetails, momo_fav WHERE momo_orders.OrderID = %s AND momo_orders.CustomerID = momo_users.userID AND momo_orderdetails.OrderID = momo_orders.OrderID AND momo_orderdetails.ProductID = momo_fav.Code", GetSQLValueString($colname_rsOrder, "text"));

$rsFav = mysql_query($query_rsFav, $momo) or die(mysql_error());

$row_rsFav = mysql_fetch_assoc($rsFav);

$totalRows_rsFav = mysql_num_rows($rsFav);

This does work to a certain extent it would be better if i could get the OR statement working

Message was edited by: Jonathan Fortis

[bregent]

Ah, I see. You can't really use an OR there because that's part of a join. There are a few ways to solve this. The simplest would be to try an outer join for those two conditions. I believe you are using MySQL - which does not support outer joins in the WHERE clause - you need to move those into the FROM clause and use ANSI 92 join syntax.

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

Another way would be to use 2 subqueries - but that wont work if you need to display data from the momo_products or momo_fav tables. It all depends on why you are joining these two tables. Is it to filter the data, or to get additional details from them.

It would also be helpful to know what's in those tables - why do you need to join to both of them?