[php] & [mysql] select and subselect

--00504502ae021bf0f10478846f78
Content-Type: text/plain; charset=ISO-8859-1

I have a page on my site where I can optionaly filter by certain fields
(order by filesize or file category), but I am implementing a shopping cart
type of idea where users can submit an order.

As administrators, my coworkers and I need to be able to filter orders by
their contents. For example:

View all orders for "Jack and the Beanstalk," where an order may have Jack
and the Beanstalk and other items.

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).

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?


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!"));

?>

--00504502ae021bf0f10478846f78--

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

Allen McCabe wrote:

> I have a page on my site where I can optionaly filter by certain fields
> (order by filesize or file category), but I am implementing a shopping
> cart type of idea where users can submit an order.
>
> As administrators, my coworkers and I need to be able to filter orders by
> their contents. For example:
>
> View all orders for "Jack and the Beanstalk," where an order may have Jack
> and the Beanstalk and other items.
>
> 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).
>
> 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?
>
> >
> 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!"));
>
> ?>
If $_POST['showid'] is likely to be an array, use implode to create a comma
separated string of values, then use IN to build the query.

$ids = implode(',', $_POST['showid'];
$subSQL = "SELECT order_id FROM afy_show_lineitem WHERE show_id IN ($ids)"

But you need also to check your logic - your query above will have two WHERE
in it and will fail miserably :-) And your call to mysql_error will
probably not help you either, as the (optional) argument should be a link
identifier, not a string of your choosing.



Cheers
--
David Robley

MS-DOS: celebrating ten years of obsolescence
Today is Sweetmorn, the 29th day of The Aftermath in the YOLD 3175.


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

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

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).



"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?
>
> >
> 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