Jump to content

Search the Community

Showing results for tags 'sql'.

More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


  • News and Announcements
    • News and Announcements
  • osCommerce Online Merchant v2.x
    • General Support
    • osCommerce Online Merchant Community Bootstrap Edition
    • Add-Ons
  • Development
  • General
    • General Discussions
    • Live Shop Reviews
    • Security
    • Developer Feedback
  • PayPal's Announcements
  • Sage Pay's Announcements
  • Solomono - new level osCommerce templates's Announcements
  • German Community's OSCOM v2.x
  • German Community's Allgemein

Find results in...

Find results that contain...

Date Created

  • Start


Last Updated

  • Start


Filter by number of...


  • Start



Real Name




Found 7 results

  1. Hello, I have a store installation (CE FROZEN, PHP 7.2) with very slow loading admin/categories.php, maybe 20-30 sec. Characteristics for products/chategories: products aprox. 1800 products_to categories aprox. 20000 (products are linked to several categories) categories aprox. 6000 The big number of categories and product links may explain the slow loading. Database tables have been analysed and optimized, they are all using MyISAM and the index settings in the tables are all correct (core). But I observe the following difference between server and local develop installation under XAMPP: Server: always the same loading time XAMPP: slow loading on first time page load rel. fast loading on second and following page loads (2-3 sec.) So my conclusion is that it should be related to a database cache setting. I checked the sqli settings in php.ini of the XAMPP installation and tried to lower mysqli.cache_size=2000 to a very low setting (10) in order to simulate the server problem but no difference. Questions: With the amount of products/categories/links does it sound reasonable this slow loading? Which setting on the server/database can produce this different behaviour Thank you in advance Rainer
  2. Hi Everyone, Hope everyone is keeping safe and well in these scary times. Im looking to run an sql to increase the shipping weight of products if under 1kg to 1.1kg leaving all other weights unchanged. Could do with some help as I dont want to destroy my database Many Thanks
  3. mfleeson

    Products without categories

    Just a silly bit of SQL. I realised that products I had used in previous promotion were not displaying as they were not in any categories, so here's a little bit of SQL to help fix databases if you've been using other tools to administrate your products. 1 - Check for products not in categories. SELECT * FROM products WHERE products_id NOT IN (SELECT DISTINCT products_id FROM products_to_categories); 2 - If none display then you're fine. If they do then choose a category to put them in and get the category number. If need be create a new category and look for the number in the database, i.e. SELECT MAX(categories_id) FROM categories_description; 3 - Move the uncategorised products into your new category... If the previous query resulted in 211 then INSERT INTO products_to_categories SELECT products_id,211 FROM products WHERE products_id NOT IN (SELECT DISTINCT products_id FROM products_to_categories);
  4. As part of my experimentation with Phoenix, I wrote the following database script to move my current live data into Phoenix so I could see what it did. I thought I'd share it for anyone who's needing to move a live store to test or vice-versa. Any questions/comments please let me know. For this script to work, load it into your database tool and search and replace changing old_db to the name of your current live site (or origin data), select the new phoenix database and run the queries there. A couple of things I noticed moving from a 2.3.4 to Phoenix. Check your old sites categories_description table. Some older sites do not have categories_seo_description so this will fail on the select. change to '' in select. Check your manufacturers_info table for 'manufacturers_seo_description',and other seo fields and if it fails change them to '' in the select. If you get an error Data truncated for column then it means your old table has larger text length than in phoenix. Adjust phoenix field length and re-run query. A lot of old products table do not have products_gtin fields, modify field to '' in query Test Move SQL.sql
  5. Mort-lemur

    Products with no Orders

    Hi All, My stores have been running 10+ years now and have 1000's of products. I'm now looking to streamline the stores and remove all products that over the years have had minimul or zero sales. Is anyone aware of a contribution or Sql commend that will help to identify items with a total sales of say <= 2 and then easily delete them? Many Thanks :)
  6. MySQLi Prepared Statement Automator This Add-On comes with the only guarantee of possibly causing you a lot of headaches. It looks like it's working quite well, but further testing is required ... - Support Thread - http://addons.oscommerce.com/info/9076
  7. Hi, As part of the installation of a contribution I have to run the following SQL database query: DROP TABLE IF EXISTS `products_cross_sell`; CREATE TABLE IF NOT EXISTS `products_cross_sell` ( `products_cross_sell_id` int(10) NOT NULL auto_increment, `products_id` int(10) unsigned NOT NULL default '1', `cross_sell_id` int(10) unsigned NOT NULL default '1', `cross_sell_sort_order` int(10) unsigned NOT NULL default '1', PRIMARY KEY (`products_cross_sell_id`) ) TYPE=MyISAM; When I do this I get the following error message: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE=MyISAM' at line 7 Is there an obvious error does anyone know? Thanks David