Jump to content
Sign in to follow this  
yesudo

DB Manipulation from Admin

Recommended Posts

Hi Emmet

Just a thought, if the different action queries were stored in a database table, this script would become more generic and extensible as it could then be possible to dynamically fetch the query to execute, perhaps preview number of records affected (possibly rollback in future etc) and people could add additional queries or tweak queries more easily :)


KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Share this post


Link to post
Share on other sites
Hi Emmet

Just a thought, if the different action queries were stored in a database table, this script would become more generic and extensible as it could then be possible to dynamically fetch the query to execute, perhaps preview number of records affected (possibly rollback in future etc) and people could add additional queries or tweak queries more easily :)

interesting thots Carine.


Your online success is Paramount.

Share this post


Link to post
Share on other sites

This has been rewritten and is now DB driven following a suggestion from bruyndoncx.

 

Which makes it easier for you to build your own queries and add them to the DB and they will then show as an option in admin.


Your online success is Paramount.

Share this post


Link to post
Share on other sites

This contrib is easy to install and here is an example of the contrib output:

 

db.gif

 

The queries shown above are included as part of the contrib.


Your online success is Paramount.

Share this post


Link to post
Share on other sites

Amend Database from Admin - Version 2.0

Modified by Emmett Brosnan

------------------------------------------------

The update to this Contribution is to:

 

1) Allow query running from the database or from a script.

2) Add further consistency with osCommerce standards in coding. Also thank you to Aaron Hiat in this respect also.

------------------------------------------------

Edited by yesudo

Your online success is Paramount.

Share this post


Link to post
Share on other sites

Emmet, i have added this contribution , but when i try to use the meta tag function it comes up with this error :

 

1054 - Unknown column 'products_head_title_tag' in 'field list'

 

update products_description set products_head_title_tag = 'a product - The product name' where products_id = '28'

 

[TEP STOP]

 

Any ideads what might be causing this ?

Share this post


Link to post
Share on other sites
Emmet, i have added this contribution , but when i try to use the meta tag function it comes up with this error :

 

1054 - Unknown column 'products_head_title_tag' in 'field list'

 

update products_description set products_head_title_tag = 'a product - The product name' where products_id = '28'

 

[TEP STOP]

 

Any ideads what might be causing this ?

Hi Patrick.

 

That particular script/query/function is set to work with this contrib:

 

http://www.oscommerce.com/community/contri...l/search,header

 

Sorry to ask an obvious question - I assume you have that installed ?

 

Let me know.

 

Ta,


Your online success is Paramount.

Share this post


Link to post
Share on other sites

Aha, so that is the reason.

i don't have it installed so therefore i am getting the error ?

 

I didn't quite understand what that contribution was for [still don't completely get it]

Should i install it and what is the main reason i should [in case you say yeah,if you are going to install 1 contribution in your life that is THE contribution to pick] B)

Share this post


Link to post
Share on other sites
Aha, so that is the reason.

i don't have it installed so therefore i am getting the error ?

 

I didn't quite understand what that contribution was for [still don't completely get it]

Should i install it and what is the main reason i should [in case you say yeah,if you are going to install 1 contribution in your life that is THE contribution to pick] B)

it is to allow for dynamic meta tags on each page - good for SEO.


Your online success is Paramount.

Share this post


Link to post
Share on other sites

reset counter

 

and

 

reset sold items

 

queries added to contrib.


Your online success is Paramount.

Share this post


Link to post
Share on other sites

Current actions included with contrib:

 

Counter(Reset)

Customers(Delete ALL)

Fill Meta-Tags(Used with header tags controller contrib)

Optimise Store Database

Orders(Delete ALL)

Products(Set ALL to inactive)
Products(Set ALL to active)
Products(Delete ALL)
Products(Reset viewed stats)
Products(Reset sold stats)
Products(Set ALL to taxable)
Products(Set ALL to NOT taxable)

Reviews(Delete ALL)

Sessions(Delete ALL)

Tables (Empty ALL except required structural ones)

Visitors(Delete ALL)


Your online success is Paramount.

Share this post


Link to post
Share on other sites

Version 3 released:

 

More user friendly version.

 

Install instructions are the same for upgrades or new installs.


Your online success is Paramount.

Share this post


Link to post
Share on other sites

List now:

 

Banners (Reset Stats)

Categories (Delete ALL)

Counter (Reset)

Customers (Delete ALL)

Featured (Delete ALL)

Fill Meta-Tags (Used with header tags controller contrib)

Optimise Store Database

Orders (Delete ALL)

Products (Delete ALL)

Products (Reset sold stats)

Products (Reset viewed stats)

Products (Set ALL to active)

Products (Set ALL to inactive)

Products (Set ALL to NOT taxable)

Products (Set ALL to taxable)

Reviews (Delete ALL)

Sessions (Delete ALL)

Specials (Delete ALL)

Tables (Empty ALL except required structural ones)

Visitors (Delete ALL)


Your online success is Paramount.

Share this post


Link to post
Share on other sites

Contrib updated - Correction to delete all orders query.

 

New Installs - Follow all instructions.

 

Upgrades from V2 - Follow all instructions.

 

Existing V3 users - just run the sql file.


Your online success is Paramount.

Share this post


Link to post
Share on other sites

Released: Version 4 - which will allow user input to queries(if applic).


Your online success is Paramount.

Share this post


Link to post
Share on other sites

Before I install this contribution...

 

Would I be able to add data into the Database i.e. can it insert data into the orders and orders_products tables?

Share this post


Link to post
Share on other sites

Could this contrib help me move data from one database to another?


Rik Rasmussen

Share this post


Link to post
Share on other sites

By : Products - Delete ALL

 

This error :

 

1146 - Table 'shop.additional_images' doesn't exist

 

delete from additional_images

 

[TEP STOP]

 

why ?

 

v4.0

Share this post


Link to post
Share on other sites
By : Products - Delete ALL

 

This error :

 

1146 - Table 'shop.additional_images' doesn't exist

 

delete from additional_images

 

[TEP STOP]

 

why ?

 

v4.0

 

It means that table does not exist in your db.

 

As this contrib allows user configuration of query builds just remove the following from the table 'db_queries_desc' in your db:

 

INSERT INTO `db_queries_desc` VALUES (82, 28, 'delete from additional_images where products_id = ');

INSERT INTO `db_queries_desc` VALUES (91, 3, 'delete from additional_images');

 

Then run query from admin again.


Your online success is Paramount.

Share this post


Link to post
Share on other sites

Hey,

 

I'm wanting to add a query to the Amend Database 4.0 contribution I have installed, but need some help. I want to delete products where I enter the product model. It looks like all tables don't have the products_model, but they do have the product_id. So can a select be added at the beginning of the "Delete Product ID" query that is already contained in the contribution and what would that look like? I'm assuming it would get the product id where the product model = user input model number.

 

The current delete product ID query looks like this when it is inserted into the new table on install:

INSERT INTO `db_queries` VALUES (28, 'Products - Delete product ID ', 'Y', 'nonscript');

INSERT INTO `db_queries_desc` VALUES (74, 28, 'delete from products where products_id = ');

INSERT INTO `db_queries_desc` VALUES (75, 28, 'delete from products_description where products_id = ');

INSERT INTO `db_queries_desc` VALUES (76, 28, 'delete from specials where products_id = ');

INSERT INTO `db_queries_desc` VALUES (77, 28, 'delete from reviews where products_id = ');

INSERT INTO `db_queries_desc` VALUES (78, 28, 'delete from products_attributes where products_id = ');

INSERT INTO `db_queries_desc` VALUES (79, 28, 'delete from products_to_categories where products_id = ');

INSERT INTO `db_queries_desc` VALUES (80, 28, 'delete from customers_basket where products_id = ');

INSERT INTO `db_queries_desc` VALUES (81, 28, 'delete from customers_basket_attributes where products_id = ');

INSERT INTO `db_queries_desc` VALUES (82, 28, 'delete from additional_images where products_id = ');

 

 

Mike

Share this post


Link to post
Share on other sites
Hey,

 

I'm wanting to add a query to the Amend Database 4.0 contribution I have installed, but need some help. I want to delete products where I enter the product model. It looks like all tables don't have the products_model, but they do have the product_id. So can a select be added at the beginning of the "Delete Product ID" query that is already contained in the contribution and what would that look like? I'm assuming it would get the product id where the product model = user input model number.

 

The current delete product ID query looks like this when it is inserted into the new table on install:

INSERT INTO `db_queries` VALUES (28, 'Products - Delete product ID ', 'Y', 'nonscript');

INSERT INTO `db_queries_desc` VALUES (74, 28, 'delete from products where products_id = ');

INSERT INTO `db_queries_desc` VALUES (75, 28, 'delete from products_description where products_id = ');

INSERT INTO `db_queries_desc` VALUES (76, 28, 'delete from specials where products_id = ');

INSERT INTO `db_queries_desc` VALUES (77, 28, 'delete from reviews where products_id = ');

INSERT INTO `db_queries_desc` VALUES (78, 28, 'delete from products_attributes where products_id = ');

INSERT INTO `db_queries_desc` VALUES (79, 28, 'delete from products_to_categories where products_id = ');

INSERT INTO `db_queries_desc` VALUES (80, 28, 'delete from customers_basket where products_id = ');

INSERT INTO `db_queries_desc` VALUES (81, 28, 'delete from customers_basket_attributes where products_id = ');

INSERT INTO `db_queries_desc` VALUES (82, 28, 'delete from additional_images where products_id = ');

Mike

 

You would need to build it in script form then add it to the database e.g.

 

Select products_id from products where products_model = input model number.

 

Then run the Delete queries, as intimated, on the result of the above query.

 

Because the delete queries are dependant on the result of the first query you can only achieve this via the script method for this contribution.

Edited by yesudo

Your online success is Paramount.

Share this post


Link to post
Share on other sites

I really like this cotrib

Im just trying to add another function, Delete all Products that are inactive.

I have a field called "products_status" in my products table and I want to be able to delete all the products that have a status of "0"

This works of course

"delete from products where products_status = 0"

but

"delete from products_to_categories where products_status = 0"

doesn't cause "products_status" is not in the"products_to_categories"

table.

 

any thought on how to wright the script properly for this?

thanks for any help

Share this post


Link to post
Share on other sites

Backup and then try:

 

  $product_deletion_query = tep_db_query("select products_id from from " . TABLE_PRODUCTS . " where products_status = 0");
 while ($product_deletion = tep_db_fetch_array($product_deletion_query)) {
tep_db_query("delete from " . TABLE_PRODUCTS . " where products_id = '" . $product_deletion['products_id'] . "'");	  
tep_db_query("delete from " . TABLE_PRODUCTS_TO_CATEGORIES . " where products_id = '" . $product_deletion['products_id'] . "'");	  
 }

 

You will need to add any other relevant product tables within the while loop e.g. products_attributes, products_description etc...


Your online success is Paramount.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×