keat Posted March 3, 2016 Share Posted March 3, 2016 I'm still finding the occasional product that has a missing main image. Sometimes the image file is in the sources folder and sometimes it's not (the latter could have been a mirroring/ftp issue i guess) I tried to view the product inventory and then view all, in the hope that I could quickly scroll through to see at a glance. However, with 211 pages I'm guessing it was too much for MYsql to handle and it crashed the page. Is there an easier way to find any products which may be missing an image ? Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted March 3, 2016 Share Posted March 3, 2016 You could use the Category choice on the right top corner of the Products page and then View All - surely that would not crash. Quote Link to comment Share on other sites More sharing options...
bsmither Posted March 3, 2016 Share Posted March 3, 2016 An easy way? Short answer - no. Long answer - please make these edits: NOPE! Made the same mistake here that I did on a prior similar hack. Ignore the following edits. In the file /admin/sources/products.index.inc.php, find: Near line 1333: $updated_time = $result['updated']; $result['updated'] = $updated_time ? $updated_time : $lang['common']['unknown']; $smarty_data['products'][] = $result; } Change to: $updated_time = $result['updated']; $result['updated'] = $updated_time ? $updated_time : $lang['common']['unknown']; $smarty_data['products'][] = $result; } if(!empty($_GET['sort']['image'])) { $arrImgPaths = array_column($smarty_data['products'], 'image_path_tiny'); switch ($_GET['sort']['image']) { case 'ASC': array_multisort($arrImgPaths, SORT_ASC, SORT_STRING, $smarty_data['products']); break; default: array_multisort($arrImgPaths, SORT_DESC, SORT_STRING, $smarty_data['products']); } } Near line 1322: $result['image_path_tiny'] = $catalogue->imagePath($image[0]['file_id'], 'tiny'); $result['image_path_large'] = $catalogue->imagePath($image[0]['file_id'], 'large'); } Change to: $result['image_path_tiny'] = $catalogue->imagePath($image[0]['file_id'], 'tiny'); $result['image_path_large'] = $catalogue->imagePath($image[0]['file_id'], 'large'); } else { $result['image_path_tiny'] = $result['image_path_large'] = ""; } Near line 1228: 'image' => $lang['catalogue']['title_image'], Change to: 'image' => $GLOBALS['db']->column_sort('image', $lang['catalogue']['title_image'], 'sort', $current_page, $_GET['sort']), // $lang['catalogue']['title_image'], Near line 14: if (!defined('CC_INI_SET')) die('Access Denied'); ABOVE that, add: if (!function_exists('array_column')) { function array_column($input, $column_key, $index_key = null) { $arr = array_map(function($d) use ($column_key, $index_key) { if (!isset($d[$column_key])) { return null; } if ($index_key !== null) { return array($d[$index_key] => $d[$column_key]); } return $d[$column_key]; }, $input); if ($index_key !== null) { $tmp = array(); foreach ($arr as $ar) { $tmp[key($ar)] = current($ar); } $arr = $tmp; } return $arr; } } The above only sorts on the 10 or so records that are actually pulled from the database -- not the entire inventory. So, ignore this response completely. Need to find a different approach. Quote Link to comment Share on other sites More sharing options...
bsmither Posted March 3, 2016 Share Posted March 3, 2016 Let's try these edits: In /admin/sources/products.index.inc.php, find: Near line 1261: if (($where === false || strlen($where) > 0) && ($results = $GLOBALS['db']->select('CubeCart_inventory', false, $where, $_GET['sort'], $per_page, $page)) !== false) { Change to: if (isset($_GET['sort']['image'])) { $query = sprintf(" SELECT CONCAT_WS('', FM.filepath, FM.filename) AS image, II.main_img, I.* FROM %1\$sCubeCart_inventory AS I LEFT JOIN (SELECT file_id, product_id, main_img FROM %1\$sCubeCart_image_index WHERE main_img <> '0') AS II ON I.product_id = II.product_id LEFT JOIN %1\$sCubecart_filemanager AS FM ON II.file_id = FM.file_id". (($where) ? " WHERE ".$where : "")." ORDER BY image ".$_GET['sort']['image'], $GLOBALS['config']->get('config', 'dbprefix')); } else { $order_by_string = ($_GET['sort']) ? " ORDER BY ".key($_GET['sort'])." ".current($_GET['sort'])." " : "" ; $query = sprintf("SELECT * FROM %1\$sCubeCart_inventory".(($where) ? " WHERE ".$where : "").$order_by_string, $GLOBALS['config']->get('config', 'dbprefix')); } if (($where === false || strlen($where) > 0) && ($results = $GLOBALS['db']->query($query, $per_page, $page)) !== false) { //select('CubeCart_inventory', false, $where, $_GET['sort'], $per_page, $page)) !== false) { Near line 1228: 'image' => $lang['catalogue']['title_image'], Change to: 'image' => $GLOBALS['db']->column_sort('image', $lang['catalogue']['title_image'], 'sort', $current_page, $_GET['sort']), // $lang['catalogue']['title_image'],'image' => $lang['catalogue']['title_image'], Â Found another gotcha! The Database class does not load '_found_rows' with the total of rows found when using DB->query(). This means pagination is not calculable. So, trying another query. Quote Link to comment Share on other sites More sharing options...
bsmither Posted March 4, 2016 Share Posted March 4, 2016 The next try: In /admin/sources/products.index.inc.php, find: Near line 1261: if (($where === false || strlen($where) > 0) && ($results = $GLOBALS['db']->select('CubeCart_inventory', false, $where, $_GET['sort'], $per_page, $page)) !== false) { Change to: reset($_GET['sort']); if (!empty($_GET['sort']['image'])) { $columns = "CONCAT_WS('', FM.filepath, FM.filename) AS image, II.main_img, I.*"; $table = sprintf("%1\$sCubeCart_inventory AS I LEFT JOIN ( SELECT file_id, product_id, main_img FROM %1\$sCubeCart_image_index WHERE main_img <> '0' ) AS II ON I.product_id = II.product_id LEFT JOIN %1\$sCubeCart_filemanager AS FM ON II.file_id = FM.file_id", $GLOBALS['config']->get('config', 'dbprefix')); $order_by_string = key($_GET['sort'])." ".current($_GET['sort']); } else { $columns = false; $table = "CubeCart_inventory"; $order_by_string = key($_GET['sort'])." ".current($_GET['sort']); } if (($where === false || strlen($where) > 0) && ($results = $GLOBALS['db']->select($table, $columns, $where, $order_by_string, $per_page, $page)) !== false) { Near line 1228: 'image' => $lang['catalogue']['title_image'], Change to: 'image' => $GLOBALS['db']->column_sort('image', $lang['catalogue']['title_image'], 'sort', $current_page, $_GET['sort']), // $lang['catalogue']['title_image'],'image' => $lang['catalogue']['title_image'], Edit: A correction was made to the name of the database table CubeCart_filemanager (was Cubecart_filemanager). My system is Windows and the MYSQL database engine does not respect capitalization, although I strive to make sure my code is properly capitalized. Â Quote Link to comment Share on other sites More sharing options...
keat Posted March 4, 2016 Author Share Posted March 4, 2016 That sort of half worked in the respect that it gives me a sort button on the images column. But when I try to sort by image, it reports back 'No Images' 13 hours ago, Dirty Butter said: You could use the Category choice on the right top corner of the Products page and then View All - surely that would not crash. There are about a dozen categories, each with about 15 sub categories, so whilst I am slowly trawling my way through it, it's time consuming. I was hoping there might be a simple solution like search and arrange by image name in the database. However, i'll no doubt have gone through all these by the time you guys come on line. Â Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted March 4, 2016 Share Posted March 4, 2016 I'm sorry this has been such a tedious process, thanks to the number of listings you have. With over 4k listings, I would hate to have to do it this way, too. I hope, as you said, that you have gone through all of them by now. Quote Link to comment Share on other sites More sharing options...
bsmither Posted March 4, 2016 Share Posted March 4, 2016 "But when I try to sort by image, it reports back 'No Images' " Maybe --No Products --? Then the query isn't working right. In CubeCart's admin, Store Settings, Advanced tab, enable Debug mode and enter you IP address in the next field (www.whatismyip.com). Ask for the list of products and sort on the image column. Scroll the web browser window so that you can see the list of SQL queries. At the bottom of the list, there may be a query in red. What does that query say? Â Quote Link to comment Share on other sites More sharing options...
keat Posted March 4, 2016 Author Share Posted March 4, 2016 Lots of select count. Â [330] SELECT COUNT(cat_id) AS Count FROM `CubeCart_category` WHERE CubeCart_category.cat_parent_id = '100024'; -- (0.000441074371338 sec) [NOT CACHED] [331] SELECT COUNT(cat_id) AS Count FROM `CubeCart_category` WHERE CubeCart_category.cat_parent_id = '100233'; -- (0.000442028045654 sec) [NOT CACHED] [332] SELECT SQL_CALC_FOUND_ROWS CONCAT_WS('', FM.filepath, FM.filename) AS image, II.main_img, I.* FROM CubeCart_inventory AS I LEFT JOIN ( SELECT file_id, product_id, main_img FROM CubeCart_image_index WHERE main_img '0' ) AS II ON I.product_id = II.product_id LEFT JOIN Cubecart_filemanager AS FM ON II.file_id = FM.file_id ORDER BY image ASC LIMIT 20 OFFSET 0; -- (0.00045108795166 sec) [NOT CACHED]Errors:[1] 1: Table 'xxxx_ccv6.Cubecart_filemanager' doesn't exist [ERROR - NOT CACHED] Memory: Peak Usage / Max (%): 10.35MB / 128M (8.09%) Cache (File): Always Disabled in ACP Cache Used: 0.00 KB of 36.00 KB (0.00%) [Clear Cache] Page Load Time: 0.277278 seconds I notice the error Cubecart_filemanager does not exists. The table is called CubeCart_filemanager. Â I changed the typo in products.index and now it seems to sort. I had checked and corrected them all manually earlier in the day, however, i'm about to do the live update tomorrow, so lets see what happens. Â Â Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted March 4, 2016 Share Posted March 4, 2016 GREAT!!! I'm sure Bsmither will edit the typo in his last code suggestion,. It's a shame we've lost the ability to refer easily to a specific comment in this thread. If anyone reads this before Bsmither fixes his code, it's the one where he says "the next try" about half way down the code. Quote Link to comment Share on other sites More sharing options...
bobdonkey Posted March 7, 2016 Share Posted March 7, 2016 I made a simple reporting web page that lists all the products with no images set using a sql query. I am now half way through writing another script that then automatically goes and fetches the image from whatever web page I have set for that manufacturer, and downloads and sets the images. Let me know if that sounds of any use and I will share. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.