Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

DB Manipulation from Admin


yesudo

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

Link to comment
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.

Link to comment
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.

Link to comment
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.

Link to comment
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.

Link to comment
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 ?

Link to comment
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.

Link to comment
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)

Link to comment
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.

Link to comment
Share on other sites

  • 2 weeks later...

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.

Link to comment
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.

Link to comment
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.

Link to comment
Share on other sites

  • 3 weeks later...
  • 3 months later...
  • 3 weeks later...
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.

Link to comment
Share on other sites

  • 11 months later...

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

Link to comment
Share on other sites

  • 2 weeks later...
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.

Link to comment
Share on other sites

  • 9 months later...

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

Link to comment
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.

Link to comment
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
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
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.

×
×
  • Create New...