Hi again David,
I'd like to implement some global filters. Normally I'd just edit admin.php but isn't there now a proper global filters mod? If so, could you please tell me how to add this to my existing installations?
On another note, which I hope you might be able to help with, is this....
If I have several instances of a single product in my DB, i.e. all from different merchants, but one has some extra fields populated in the DB, what's the best way of selecting that instance with a query? I guess I really need it to look recursively if possible.
e.g. ID -> Name -> Category -> Subcategory1 -> Subcategory2 -> Subcategory3
If I have 4 products, then I want to pick the one with as many of the category fields containing data as possible. e.g., if there's an instance with data going all the way up to subcat3, I want that one. If not, then if there's one going to subcat2, then that one etc...Make sense?
Do you know of a neat way of doing this in an SQL query or will I need a generic query and then do some conditional looping in PHP?
Cheers.
Keeop
Thanks a lot David, will give that a go.
But, while I've got you.......;-)
Is there an easy way to bulk copy records from one table to another? I'm going to create a products_cache table which will contain all the most accessed products and those used for all the popular,best selling etc. queries - to speed these all up. So, I need to extract records from the products table and put them in the new one but is ther another way of doing it rather than a SELECT and then INSERT record,value etc., etc., in to the new table? Almost want to do a copy of the selected data? On a similar MySQL theme, whilst trying to sort this myself, I have come across 'prepared statements' and MySQLi, which is recommended for installs of MySQL 4.6 or greater. Would it be beneficial to upgrade all the API cals to use MySQLi rather than MySQL? Have you played with this at all?
Cheers.
Keeop
Hi Keeop,
Sounds like quite a complex mod - not least because of the cached table also having to be updated whenever the main table is updated as a result of a re-import (otherwise queries directed towards the cache table would show "old" results)...
If you're considering this because of performance issue I'd be inclined to look elsewhere first - such as the MySQL tuning that can be achieved as discussed in this thread...
Cheers,
David.
--
PriceTapestry.com
Hi David,
The plan is to update the cache table at import time so what I need to do is run three Select queries to get the data I want and then dump the results in to the cache table. I was hoping there was simpler way that running three separate Insert queries!
As for the tuning, I've looked at thread a few times. I was just curious why the PHP site recommends using MySQLi and have also seen a lot of people recommending using 'prepared statements'. I was just wondering if you had any thoughts or experience with either?
Cheers.
Keeop
Hi David,
Got the global filters working nicely and am now playing with the quality score. Not sure if this is possible but I really need the product with the highest quality score to be the one returned by any 'GROUP BY name' search results. Trying to avoid a new query. When a single row is returned, when doing a GROUP BY, do you know which row is chosen by MySQL to be the one displayed? If so, can this be altered? Almost like having a separate ORDER BY within the grouping clause.
Cheers.
Keeop
Hi Keeop,
That's the catch i'm afraid - it's not possible to specify which product record the rows are returned from in a summary (GROUP BY) query, so what I'd suggest in this instance would be to requery with the quality parameter after the intial GROUPed query, for example:
$theProducts = array("Product 1","Product 2","Product 3");
asort($theProducts);
$in = implode(",","'".database_safe($theProducts)."'");
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE name IN ('Product 1,'Product 2','Product 3') GROUP BY name ORDER BY name";
database_querySelect($sql,$products);
$row = 0;
foreach($theProducts as $theProduct)
{
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE name='".database_safe($theProduct)."' ORDER BY quality DESC limit 1";
database_querySelect($sql,$result);
$products[$row]["category"] = $result[0]["category"];
$products[$row]["subcategory1"] = $result[0]["subcategory1"];
$products[$row]["subcategory2"] = $result[0]["subcategory2"];
$products[$row]["subcategory1"] = $result[0]["subcategory3"];
$row++;
}Hope this helps!
Cheers,
David.
--
PriceTapestry.com
Hi David,
Thanks for your help - it is a bit of a mare! I managed to almost get something working using inner joins in a nested select, but the performance was pretty woeful and I didn't really know what I was doing!
What I think I'll do is update at import time, again. What would be the most efficient way of doing this? I guess after the import routine. It would be a case of finding all instances of a product matched on name and then updating all the records' category information based on that stored in the record with the highest quality score.
Cheers.
Keeop
Hi Keeop,
Really straight forward to add Global Filters to an existing (11/09A) installation. It's implemented in exactly the same database table, but with the filename field being empty to indicate a global filter - that's basically it.
First, extract each of the Global Filter admin files from the 12/19A distribution -
admin/global_filters.php
admin/global_filters_configure.php
admin/global_filters_delete.php
If you like, you can create a menu item in admin/admin_menu.php of course, otherwise just browse to admin/global_filters.php.
Next, in includes/admin.php, look for the SQL where filters are selected by the following code at around line 421:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."filters` WHERE filename='".database_safe($admin_importFeed["filename"])."' ORDER BY created";...and REPLACE that with:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."filters` WHERE filename='".database_safe($admin_importFeed["filename"])."' OR filename='' ORDER BY filename,created";...and that's it!
Regarding your SQL query; I think for efficiency what I would do is populate a "quality" field - INT(11) - in the products table to indicate the depth of meta data available for a record. This can be derived once during import, something like:
$quality = 0;if ($importRecord["category"]) $quality++;
if ($importRecord["subcategory1"]) $quality++;
if ($importRecord["subcategory2"]) $quality++;
if ($importRecord["subcategory3"]) $quality++;
...add $quality to the INSERT SQL of course, and then when you come to want to SELECT the product row with the most details populated, simply ORDER BY quality DESC and that will return the record with the most information first.
That's for a single product SELECT, if you wanted to select multiple products there has to be a loop somewhere - either multiple SELECTs, or a single SELECT and then a PHP loop to extract the highest quality record for each product. I would be inclined to use the former, so you would have something like this:
$products = array();$theProducts = array("Product 1","Product 2","Product 3");
foreach($theProducts as $theProduct)
{
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE name='".database_safe($theProduct)."' ORDER BY quality DESC limit 1";
database_querySelect($sql,$result);
$products[] = $result[0];
}
...which will return your $products array just as if it had been returned from a single SELECT query...
Cheers,
David.
--
PriceTapestry.com