Jump to content
Sign in to follow this  
Designer

Useful SQL queries

Recommended Posts

We collect useful SQL queries, to help novice developers. Everything, that is connected with a database
 

-- Quantity Of Goods In Orders
-- DB_PREFIX
-- $product_id - Product ID
-- $this->config->get('config_complete_status_id') - Order Complete Status

SELECT COUNT(*) AS `total` FROM ' . DB_PREFIX . 'order_product op JOIN ' . DB_PREFIX . 'order o ON op.`order_id` = o.`order_id` WHERE op.product_id = ' . (int)$product_id . ' AND o.`order_status_id` = ' . (int)$this->config->get('config_complete_status_id') . ' GROUP BY o.`order_status_id`

 

Share this post


Link to post
Share on other sites
# Quickly Change The Table Prefix In The Database
# Create A rename_table.php File With The Contents Below The Code
# Start And Wait For The Process To Finish http://domain.ru/rename_table.php
# Rejoice And Delete The File rename_table.php

<?php
$old_prefix = 'oldprefix_';
$new_prefix = 'newprefix_';

header('Content-type: text/plain');

require_once('config.php');

# For versions above 1.5. *
# require_once(DIR_SYSTEM . 'library/db/mpdo.php');
# require_once(DIR_SYSTEM . 'library/db/mssql.php');
# require_once(DIR_SYSTEM . 'library/db/mysql.php');
# require_once(DIR_SYSTEM . 'library/db/mysqli.php');
# require_once(DIR_SYSTEM . 'library/db/postgre.php');

require_once(DIR_SYSTEM . 'library/db.php');

$db = new DB(DB_DRIVER, DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_DATABASE);

$query = $db->query('SHOW TABLES');

if ($query->num_rows) {
	foreach ($query->rows as $row) {
		$table   = current($row);
		
		$new_table = '';

		if($old_prefix){
			if(strpos($table, $old_prefix) === 0){
				$new_table = $new_prefix . substr($table, strlen($old_prefix));
			}
		} else {
			$new_table = $new_prefix . $table;
		}

		if($new_table){
			$db->query("ALTER TABLE `{$table}` RENAME TO `{$new_table}`");
			echo "RENAME TABLE {$new_table}\n";
		}
	}
}

die('The process is completed.');

 

Share this post


Link to post
Share on other sites
-- Bulk Edit Of The Date Of Addition Of Products

-- Update All Products To The Current Date
UPDATE `"' . DB_PREFIX . '"product` SET `date_added` = NOW();

-- Update All Products Before The Specified Date
UPDATE `"' . DB_PREFIX . '"product` SET `date_added` = "2017-07-10";

-- Update With The Specified Product
UPDATE `"' . DB_PREFIX . '"product` SET `date_added` = NOW() WHERE `product_id` = "10";

-- Update From Product List
UPDATE `"' . DB_PREFIX . '"product` SET `date_added` = NOW() WHERE `product_id` IN ("10,11,22,334,23");

 

Share this post


Link to post
Share on other sites
-- Output of categories with goods from the manufacturer
SELECT c.category_id FROM oc_product_category p2c LEFT JOIN oc_product p ON p.product_id = p2c.product_id LEFT JOIN oc_category с ON с.category_id = p2c.category_id LEFT JOIN oc_product_description cd ON cd.category_id = p2c.category_id WHERE p.status_id = 1 AND c.status_id = 1 AND cd.language_id = (int)$this->config->get('config_language_id') AND p.manufacturer_id = (int)$manufacturer_id

-- Output of goods from the category of a certain manufacturer
SELECT p2c.product_id FROM oc_product_category p2c LEFT JOIN oc_product p ON p.product_id = p2c.product_id WHERE p.status_id = 1 AND p2c.category_id = (int)$category_id AND p.manufacturer_id = (int)$manufacturer_id

 

Edited by Designer

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
You are posting as a guest. If you have an account, please sign in.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

×