SELECT OR statement returning all results

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
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

SELECT OR statement returning all results

Post 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
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: SELECT OR statement returning all results

Post by Celauran »

(aStar_orderdetails.ProductID = aStar_products.Code OR aStar_orderdetails.ProductID = aStar_fav.Code)
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: SELECT OR statement returning all results

Post 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)); ?>
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: SELECT OR statement returning all results

Post by Celauran »

OrderID is probably ambiguous because you're using SELECT * across five or six tables. Try specifying only the columns you actually need.
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: SELECT OR statement returning all results

Post 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.
Post Reply