sql manager a hidden treasure of prestashop.

[amodkarmarkar]

Hi to everybody,

 

Recently I discovered the power of sql manager and thought to share to those people who dont know what exactly sql manager is. I agree that i dont have the entire knowledge of sql, But if started now even I can gain more knowledge. Please rectify me if i have made any mistake in the entire post. SO

 

What is sql manager.

 

This tool helps in collecting data and their combinations as per your requirement. eg: total pending products to be delivered when payment is accepted or product wise pending orders when payment is accepted. Or store wise total pending orders to be delivered when payment is accepted etc. Not only when the status is "payment accepted" but you can have results on your desired order statuses. This can be used as a mini back office tool for small or emerging stores.

 

Where is sql manager located

 

Currently with the version 1.5.5.0 it can be found in the admin under Advance parameters>>sql manager.

 

Will this manipulate or delete any data.

 

No, since the sql manager is restricted to only using the "SELECT" statement, you can fearlessly use this tool. If you make any mistake the error message system of PS is very strong. This means that you cannot use "INSERT" "DELETE" "UPDATE" etc statements in sql manager.(I dont know about the bugs if exists) If the expert users somehow reply please consider reading them.

 

Beauty of sql manager.

 

1: Not only you can have results from only one table, ahead you can connect two tables or more for your desired results by the JOIN statement of sql.

2: You can export the results to spreadsheet as well.(Instead of view icon click the export icon on the right side of the desired query.)

3: Unlimited number of queries.

 

EXAMPLES

 

Suppose you have few orders where all the payments are accepted and you want the product wise total quantity. here is the statement

SELECT ps_order_detail.product_name, sum(ps_order_detail.product_quantity) AS producttotal FROM ps_order_detail JOIN ps_orders ON ps_orders.id_order = ps_order_detail.id_order WHERE ps_orders.current_state = 2  GROUP BY ps_order_detail.product_name

The result will show you product name wise total quantity of all stores.

Here if you observe I have connected two tables one ps_order_detail and two ps_orders.

If you have not selected the prefix as ps  while installation you can remove the ps or replace your desired prefix and save the query.

 

If you need the same result for a single store use

SELECT ps_order_detail.product_name, sum(ps_order_detail.product_quantity) AS producttotal FROM ps_order_detail JOIN ps_orders ON ps_orders.id_order = ps_order_detail.id_order WHERE ps_orders.current_state = 2 && ps_order_detail.id_shop = 2 GROUP BY ps_order_detail.product_name

Replace the ps_orders.current_state = 2 to your desired store id and save.

 

The above two examples are with the database structure of version 1.5.5.0. I dont know about past or future versions. Its great if it works.

 

Hopes

 

I hope that prestashop will keep this function or module free and udated permenantly.

This tool can be considered as a good example of open source function or module.

 

I hope the Prestashop will add a functionality to sql manager as below.

Grouping sql manager by stores. if so the selected store manager can view and be updated with only his queries and not others.(Hope i have stated it correctly.)

 

Hope that every expert will add one statement to help others(Truely not to make this post famous but to make all capable)

 

 

FINALLY BUT NOT TOTALLY

 

I know that this post is not completed yet atleast by my side, nor was it possible me to give full knowledge, But it alteast started.

 

Again if I have make any mistakes please rectify.

 

Regards

 

Amod

 

 

[pmaojo]

Hello, Do you know a way to add data from DB manager to database? Im looking for a way of inserting data to a custom table in the database from the backoffice.

[pmaojo]

Hello, Do you know a way to add data from DB manager to database? Im looking for a way of inserting data to a custom table in the database from the backoffice.

[tuk66]

Use phpMyAdmin which is a full-featured database manager.

[pmaojo]

thankyou but I need my client to have a backoffice feature to add data....

[vekia]

this feature is not possible in back office due to the possible problems with inconsistency of database.

not everyone are tech savvy, and even little mistake in the query which uses UPDATE,INSERT,DELETE commands can break up whole database.

 

so, in my opinion it's very good that these commands are blocked.

[pmaojo]

I found this: 

$insertData = array(
'app_id' => 1, 
'app_name' => 'google plus', 
'status' => 0, 
'title' => 'google title'
);

Db::getInstance()->insert("your table name", $insertData);

So this would be possible only in the frontoffice? Should I make a page only for admin for that?