Update column from another table

ikenD

Hi there,

I have 2 tables - Product and LineItems

I am POSTing 3 arrays ( $postID / $postQty / $postQty )

I create a string of ID's with

$qID = implode(",", $postID);

I then perform a select using

$qryProd = "SELECT * FROM onlineProds WHERE id IN (".$qID.") ORDER by '$qID'";

If I echo the query - it's correct and holds the order (the POSTed order)-

SELECT * FROM onlineProds WHERE id IN (6,1) ORDER by '6,1'

BUT - it LOSES the order in the query result, so when I echo the resulting id's - it would show 1,6 instead of 6,1

How can I either

a) Apply the sort order from $qID to the mysql result,
OR
b) keep the arrays somehow GLUED in the correct order - meaning, I need to keep the result ID together with the POSTed QTY array

Please help

Kindest Regards

ikenD

bradgrafelman

I would say forget the ordering, just retrieve the results and store them into an array using the id as the index.

Then, loop through your $postID array and output the results in order.

EDIT: I just read the thread's title again - "Update column from another table" - and now I'm curious. What is the end result here? If you're trying to use data from one table to update a column in another, it might be that a) there's an easier way to do this without using a SELECT query at all, or b) your DB design might need tweaking to be more optimized/normalized.

ikenD

bradgrafelman;10942955 wrote:
EDIT: I just read the thread's title again - "Update column from another table" - and now I'm curious. What is the end result here? If you're trying to use data from one table to update a column in another, it might be that a) there's an easier way to do this without using a SELECT query at all, or b) your DB design might need tweaking to be more optimized/normalized.

Thanks for answering so quickly -

The title = I should have changed it perhaps - but what I wanted to do originally was simply insert the product ID along with the quantity into the lineitems table - and have other columns automatically looked up from the products table(cost, image path) - perhaps that would be the best way to go ? If you could provide me with and example for that, it would be much better (sorry, I am very new to this stuff)

Many thanks in advance,

ikenD

johanafm

You can peform selects during inserts.

INSERT INTO lineitem(product_id, cost, image, qty)
SELECT product.id, product.cost, product.image, 4 FROM product WHERE product.id = 6
UNION SELECT product.id, product.cost, product.image, 10 FROM product WHERE product.id = 1

Where 4 and 10 are quantities as supplied by your user. The above will insert two items into your lineitems table, provided that product.id is unique, which it should be.

You can of course also perform one select to get all relevant products from the product table as you started out doing, and then use that data for an insert on the form INSERT INTO table VALUES ...
But, if you do it this way, there is no reason to have a specific order in the result set. Wether you insert lineitem for product 1 first then 6, or first 6 then 1 doesn't matter. And if you have your user supplied data organized in a reasonable way, then there is no extra work to deal with any kind of ordering.

// product id as array key, element holds the quantity
$product[6] = 4;
$product[1] = 10;

$query = 'SELECT id, cost, image FROM product WHERE id IN (' . implode(',', $product) . ')';
//...
if ($result) {
	$values = array();
	while($row = ...) {
		// Reference the product quantity by its id.
		$values[] = '(' . implode(',', $row) . ', ' . $product[$row['id']] . ')';
	}
	$update = 'INSERT INTO lineitem(product_id, cost, image, qty) VALUES ' . implode(', ', $values);
}

But to get some kind of ordering of a result set, you need to tell your dbms what to order by. '6,1' is nonsensical for this purpose. Perhaps what you were looking for would be ORDER BY id DESC.