Skip to main content
J
Jonathan_Fortis
Inspiring
November 19, 2013
Question

mySQL OR returning all results..

  • November 19, 2013
  • 1 reply
  • 1080 views

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


This topic has been closed for replies.

1 reply

B
bregent
Participating Frequently
November 19, 2013

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?

J
Jonathan_FortisAuthor
Inspiring
November 19, 2013

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

J
Jonathan_FortisAuthor
Inspiring
November 21, 2013

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

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices