Page 1 of 1

SELECT OR statement returning all results

Posted: Tue Nov 19, 2013 5:04 am
by jonnyfortis
Hello, I am having an issue returning results based on an orderID. I have two tables containing products and and orderDetails Table. I need to say "WHERE orderdetails.Code = products.Code OR products1.Code

i have done the following but it is returning all the products from the DB

Code: Select all

$colname_rsCustomer1 = "-1";
if (isset($_SESSION['OrderID'])) {
  $colname_rsCustomer1 = $_SESSION['OrderID'];
}
mysql_select_db($database_astar, $astar);
$query_rsCustomer1 = sprintf("SELECT * FROM aStar_users, aStar_orders, aStar_orderdetails, aStar_products, aStar_fav WHERE aStar_orders.OrderID = %s AND aStar_orders.CustomerID = aStar_users.userID AND aStar_orderdetails.OrderID = aStar_orders.OrderID AND (aStar_orderdetails.ProductID = aStar_products.Code OR aStar_fav.Code)", GetSQLValueString($colname_rsCustomer1, "text"));
$query_limit_rsCustomer1 = sprintf("%s LIMIT %d, %d", $query_rsCustomer1, $startRow_rsCustomer1, $maxRows_rsCustomer1);
$rsCustomer1 = mysql_query($query_limit_rsCustomer1, $astar) or die(mysql_error());
$row_rsCustomer1 = mysql_fetch_assoc($rsCustomer1);
i think this is causing me the issue

Code: Select all

(aStar_orderdetails.ProductID = aStar_products.Code OR aStar_fav.Code)
i have tested with one or the other but both products isnt working

any ideas?

thanks

Re: SELECT OR statement returning all results

Posted: Tue Nov 19, 2013 5:20 am
by Celauran
(aStar_orderdetails.ProductID = aStar_products.Code OR aStar_orderdetails.ProductID = aStar_fav.Code)

Re: SELECT OR statement returning all results

Posted: Tue Nov 19, 2013 5:31 am
by jonnyfortis
whats happening now is it seems to be echoing out all the products in the DB but giving them all the same orderID

Code: Select all

    <?php do { ?><tr>
        <td><?php echo $row_rsCustomer1['OrderID']; ?></td>
        <td><?php echo $row_rsCustomer1['Short_Decription']; ?></td>
        <td>&nbsp;</td>
        <td>&nbsp;</td> 
    </tr><?php } while ($row_rsCustomer1 = mysql_fetch_assoc($rsCustomer1)); ?>

Re: SELECT OR statement returning all results

Posted: Tue Nov 19, 2013 5:37 am
by Celauran
OrderID is probably ambiguous because you're using SELECT * across five or six tables. Try specifying only the columns you actually need.

Re: SELECT OR statement returning all results

Posted: Tue Nov 19, 2013 5:56 am
by jonnyfortis
i have tried this

[text]$colname_rsCustomer1 = "-1";
if (isset($_SESSION['OrderID'])) {
$colname_rsCustomer1 = $_SESSION['OrderID'];
}
mysql_select_db($database_astar, $astar);
$query_rsCustomer1 = sprintf("SELECT aStar_orders.OrderID, aStar_orders.CustomerID, aStar_users.userID, aStar_orderdetails.OrderID, aStar_products.Code, aStar_orderdetails.ProductID, aStar_fav.Code FROM aStar_users, aStar_orders, aStar_orderdetails, aStar_products, aStar_fav WHERE aStar_orders.OrderID = %s AND aStar_orders.CustomerID = aStar_users.userID AND aStar_orderdetails.OrderID = aStar_orders.OrderID AND (aStar_orderdetails.ProductID = aStar_products.Code OR aStar_orderdetails.ProductID = aStar_fav.Code)", GetSQLValueString($colname_rsCustomer1, "text"));
$query_limit_rsCustomer1 = sprintf("%s LIMIT %d, %d", $query_rsCustomer1, $startRow_rsCustomer1, $maxRows_rsCustomer1);
$rsCustomer1 = mysql_query($query_limit_rsCustomer1, $astar) or die(mysql_error());
$row_rsCustomer1 = mysql_fetch_assoc($rsCustomer1);[/text]

but now this is returning lots of the same OrderID but no other records.