如何使用PHP在MySQL中的两个表上同时执行两个select语句

What would be the best way to run this query.

I have two tables within the same database, products and shop_orders. I'm trying to run two select statements, using one of the fields from shop_orders to retrieve data.

<td align="center" bgcolor="#F5F5F5" class="column-light"><strong>Chain Men's Shoes</strong></td>
      <td align="center" bgcolor="#F5F5F5" class="column-light"><strong>

<?
$sql="
    SELECT * FROM shop_orders WHERE 
    DATE(CONCAT(shop_order_year,'-',shop_order_month,'-',shop_order_day))
    BETWEEN '".$start."' and '".$end."'
    AND shop_order_action = 'Sale' AND shop_order_field_to_update NOT LIKE '%con%' AND shop_order_location = 'Chain' ORDER BY shop_order_id DESC";
    $result=mysql_query($sql);
    while($rows=mysql_fetch_array($result)){ ?>
          <? $shop_order_product_id = $rows['shop_order_product_id'];?>

           <?
        $sql2="SELECT * FROM product WHERE product_id='".$rows['shop_order_id']."' AND category = 'Mens'";
        $result2=mysql_query($sql2);
        $mens_shoes=mysql_num_rows($result2);
        echo $mens_shoes; ?>
      </strong></td> <?
    }
?>

DB Structure

Product 

Product ID   Category 

1234         Mens
2345         Mens

Shop_orders

shop_order_day     shop_order_month    shop_order_year    shop_order_action    

     1                   03                  2014                 Sale

shop_order_product id     shop_order_field_to_update

        1234                   quantity_c_size_39

The fields that are alike are shop_order_product_id and Product ID.

This query should total up the number of sales within a given date period where the matching criteria is location, action=Sale and the category is Mens. The first part of this query ($sql) already works, however, when I try to combine it with the second embedded SELECT statement, the figure returned is zero. I know that the figure should be greater than zero.

Any help?

* UPDATE *

I seemed to solve the issue with this :

$sql="
                SELECT COUNT(*) FROM shop_orders 
                LEFT JOIN product
                ON shop_orders.shop_order_product_id=product.product_id
                WHERE 
                DATE(CONCAT(shop_order_year,'-',shop_order_month,'-',shop_order_day))
                BETWEEN '".$start."' and '".$end."'
                AND category = 'Mens' AND shop_order_action = 'Sale' AND shop_order_field_to_update NOT LIKE '%con%' AND shop_order_location = 'Chain' GROUP BY shop_order_id";

If your tables are linked by a foregin key, you can join them and count all values with a single sql quethem with:

$sql="SELECT * FROM shop_orders
    LEFT JOIN product ON product.product_id=shop_order_product_id
    WHERE 
    DATE(CONCAT(shop_order_year,'-',shop_order_month,'-',shop_order_day)) BETWEEN '".$start."' and '".$end."'
     AND shop_order_action = 'Sale' AND shop_order_field_to_update NOT LIKE '%con%' AND shop_order_location = 'Chain' ORDER BY shop_order_id DESC
    ";

The left join clause ON should be the link betwen the shop_orders foreigin key with the producs primary key (if you have a 1 to many data logiacl structure in database)

I will sugest you learning more sql.

This statement will help you:

http://www.w3schools.com/sql/sql_join.asp

With this statement you can do things like:

SELECT * FROM t1 JOIN t2 on t1.id WHERE ........;

You may need this one also:

http://www.w3schools.com/sql/sql_groupby.asp