Re: [php] & [mysql] select and subselect
| From: | Nisse Engström | Date: | Tue, 17 Nov 2009 12:34:56 +0000 |
| Subject: | Re: [php] & [mysql] select and subselect | ||
| References: | 1 | Groups: | php.general |
| Request: | Send a blank email to php-general+get-299887@lists.php.net to get a copy of this message | ||
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