So I have this select statement that I am trying to make more responsive.
I'm selecting products (items
table) that got hit on a website that were listed as unavailable.
I then take the name of the product hit and see if there is any similar product in the database, and subselect the info for any similarly named products, to use as suggested alternates.
My problem is that now I want to display a url for any valid alternates, by concating a standard url with the alternate product id at the end. Unfortunately, I can't concat said url because alt_id
is a field created by a subselect and doesn't exactly exist in the table itself, so I get the error:
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'alt_id' in 'field list'' in /blah/ on line 34
Here is the starting query, which works:
$data = query_array(
"select unavailable_item_hits.itemid, items.Name as unavail_name,
(select max(items.ItemId) from items join products on items.itemid = products.itemid join brands on items.brandcode = brands.code where items.name = unavail_name and items.flagstatus != 'U' and products.flagstatus != 'U' and brands.flagstatus != 'I' and items.ItemId !=unavailable_item_hits.ItemId) as alt_id,
unavailable_item_hits.url as url_hit, unavailable_item_hits.marketer_tag, unavailable_item_hits.count as hit_count, unavailable_item_hits.datestatus as last_date_hit from unavailable_item_hits join items on unavailable_item_hits.ItemId = items.itemid order by alt_id is not null, hit_count desc;");
Which returns this data:
itemid unavail_name alt_id url_hit marketer_tag hit_count last_date_hit
7615 3602 Oxford sb.local/product.php?ItemId=7615&ProductId=55555555555555555&ref=lsf lsf 1 2012-02-02 18:53:37
9621 McTavish 10259 sb.local/product.php?ItemId=9621&ProductId=17246&ref=lsf lsf 2 2012-02-02 18:53:59
Now, the part that I'd like to add is something like this:
select concat('http://example.com/products.php?ItemId=', alt_id), null) as alt_url, ...
But that doesn't work because alt_id
isn't defined yet. I don't know what to do from here, how to be able to use the data from that subselect in the final result. Maybe the only way is to do two different selects or something, but that will mean pulling the data out into an array in memory and then performing the subselect, which I'd like to avoid. Or maybe I have to perform the subselect again on the field that I want to manipulate like a string?