php - 如何创建 sql_query

Question

我在 php.3 中创建了一些过滤器，其中针对 1 个表。第四个目标是另一张 table 。第一个表是:

id_of_orders :
id_order(int) time(NOW) username(varchar) price(decimal)

第二个是:

order :
 order_id(int)  product(varchar) price (decimal)

order.order_id 引用 id_of_orders.id_order

表 id_of_orders 就像订单表的映射器(id_of_orders.id_order 具有唯一的编号)。表订单包含许多订单，其中一些订单具有相同的 order_id

我想返回包含 order.product=='proion' 的 id_of_orders.id_order

我使用的查询是这样的:

$query = "SELECT * FROM id_of_orders WHERE 1=1";
if(!empty($_SESSION['employees']))
  $query .= " AND id_of_orders.username='$_SESSION[employees]'";
if(!empty($_SESSION['timis']))
  $query .= " AND id_of_orders.price='$_SESSION[timis]'";
if(!empty($_SESSION['dates']))
  $query .= " AND DATE(time)='$_SESSION[dates]'";
//if(!empty($_SESSION['proions']))
 // $query .= " AND (orders.product='$_SESSION[proions]' && id_of_orders.id_order==orders.order_id)";

$result = mysql_query($query);

Answer 1

您可以尝试这样的查询

SELECT o.* 
  FROM id_of_orders i JOIN
(
  SELECT order_id
    FROM orders
   WHERE product = 'proion'
   GROUP BY order_id
) q ON i.id_order = q.order_id
 WHERE o.username = ?
   AND o.price = ?
   AND DATE(time) = ?

或

SELECT i.id_order, i.time, i.username, i.price 
  FROM id_of_orders i JOIN orders o
    ON i.id_order = o.order_id
 WHERE 1 = 1
   AND o.product = 'proion'
   AND o.username = ?
   AND o.price = ?
   AND DATE(time) = ?
 GROUP BY i.id_order, i.time, i.username, i.price