Re: [php] & [mysql] select and subselect

[Nisse Engström <news.NOSPAM.0ixbtqKe@xxxxxxxx>]

On Mon, 16 Nov 2009 14:21:41 -0800, Allen McCabe wrote:

> I have an order table that keeps track of the order_id, the date, the
> status, etc. I also have an order_lineitem table that is the contents of the
> order. This has a one-to-many structure (without foreign keys because it is
> mysql).

<http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html>

   "InnoDB supports foreign key constraints. ..."
 
> I was baffled as to how to filter the orders by the item_id that appears in
> the order_lineitem table.
> 
> I just came up with this, but I'm not sure how the mysql_queries will handle
> an array. Do I have to do some extensive regular expression management here
> to get this to work, or will it accept an array?
> 
> <?php
> 
> if (isset($_POST['showid']))
>    $showid = $_POST['showid'];
>    $subSQL = "SELECT order_id FROM afy_show_lineitem WHERE show_id =
> {$_POST['showid']};";
>    $subResult = mysql_query($subSQL);
>    $where = "WHERE";
>    $extQuery = 'order_id = {$subResult}';
>   }
> 
> $resultOrders = mysql_query("SELECT * FROM afy_order {$where} {$extQuery};")
> or die(mysql_error("Could not query the database!"));

[[ Note: You should probably have posted to the php-db list
   (or the php.db newsgroup) to increase the chance of
   getting responses from people who actually know what
   they're talking about. I'm a MySQL newbie... ]]


$subResult is a resource. You need to fetch the results
before you can use them like this. Maybe something like:

--------------------
  /* UNTESTED */

  /* Don't forget to escape! Some will argue that
     you are better off using prepared statements. */
  $showid = mysql_real_escape_string ($_POST['showid']);

  $q = <<<_
SELECT `order_id` FROM `afy_show_lineitem`
 WHERE `show_id` = '$showid';
_;

  $res = mysql_query ($q);
  if ($res) {
    while ($row = mysql_fetch_row ($res)) {
      $ids[] = $row[0]; /* Assuming numerical ids. */
    }
    $ids_spec = implode (',', $ids);
    mysql_free_result ($res);
  } else {
    /* Handle error */
  }

  if (isset ($ids_spec)) {
    $q = <<<_
SELECT * FROM `afy_order`
 WHERE `order_id` IN ($ids_spec)
_;
    $res = mysql_query ($q);
    if ($res) {
      /* Do stuff */
    } else {
      /* Handle error */
    }
  }
--------------------

But you could also do it in one query (*untested*):

  /* UNTESTED */

  SELECT `ao`.* FROM `afy_order`         AS `ao`,
                     `afy_show_lineitem` AS `asl`
   WHERE  `ao`.`show_id`  =  $show_id
     AND `asl`.`order_id` = `ao`.`show_id`


Or maybe there are better ways, such as using INNER JOIN,
but those sometimes end up using a temporary table...
(Hint: php-db, php.db, php-db, ...)


/Nisse

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php