Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Mega huge problem


neurotoxic

Recommended Posts

To make a very, very long story short, someone dropped the "products" table from an osCommerce website, using PHPMyAdmin. Can't go back, and the hosting company wasn't able to restore backups.

 

So, here we are with an almost complete website, ready to go online, but missing a huge chunk of it. The website contained some 2,000 movies for sale.

 

My question is: using the other tables, is it possible to somehow manage to restore or recreate the "products" table in the database? Or do we have to drop everything and do it from scratch?

Link to comment
Share on other sites

Well, if someone dropped the Products table, then you also lost the 13 (default) fields within it, and all the info they contained.

 

Did you take any 'backups' within the osCommerce Control Panel? If so, you could restore from there.

 

If you have no backups then I'm very sorry for you.

 

Vger

Link to comment
Share on other sites

If no backup - one option - although still labour intensive is to run this sql for each product id you find in the products description table:

 

The fields below may not match the structure of your products table - so edit below accordingly.

 

INSERT INTO `products` ( `products_id` , `products_quantity` , `products_model` , `products_image` , `products_price` , `products_date_added` , `products_last_modified` , `products_date_available` , `products_weight` , `products_status` , `products_tax_class_id` , `manufacturers_id` , `products_ordered` )

VALUES ('***', '0', NULL , NULL , '0.0000', '0000-00-00 00:00:00', NULL , NULL , '0.00', '0', '0', NULL , '0'

);

 

*** id from products description table

 

You can then go into admin - where all the products should then exist and fill in any blank fields for them.

 

You could fill the blanks in, in each insert statement but this may get confusing........ :huh:

Your online success is Paramount.

Link to comment
Share on other sites

Yesudo, I have no idea what "easy populate" might be. But I'm pretty sure we'll have to do everything manually...

 

Vger, what info did the 13 default fields included? Can't this be recovered from a fresh osCommerce install, imported in our current databse and then we work from there?

 

We unfortunately did not make any backup by ourselves. For some stupid reason, the hosting comapny can only go back 3 days, did not restore them in time when we asked, then found another older backup, messed with it enough so ti can't be recovered either and, basically, screwed everything and acted like amateur (and I take the blame for not making any backup myself, and my client, who the website belongs to, stupidly pressed "Drop" in PHPMyAdmin without knowing what he was doing).

 

So there is no backup anywhere. :(

 

The reason why I still wonder if something can be hacked to somehow rebuild this particular table is that the "products_description" table seems to contain a lot of information to link each product name to its respective product ID and description. So I thought, from this, we might save some time and work manually on the "products" table only.

Link to comment
Share on other sites

think we cross posted - see my post above yours neurotoxic.

 

Yes indeed, we cross posted :)

 

Thank you very much for your second reply. I think this is exactly what I was looking for, but I did not know if it was possible. I'll go play with this a little and get back with the results.

 

Again, thanks a lot! :thumbsup:

Link to comment
Share on other sites

backup your db - then enter code below into a php page called temp.php - up it to your server then point your browser at it - and it should do the first element i.e. recreating all the products - you can then go into admin and add the missing fields.

 

I have set the products status to 0 - so the products don't show - this can then be changed on the admin side as you edit each product - so you could still go live - and do the products gradually.

 

Let me know if the script works.

 

<?php
 $product_query = tep_db_query("select products_id from products_description");
 while($product = tep_db_fetch_array($product_query))
 {
   $sql_data_array = array('products_id' => $product['products_id'],
                           'products_status' => '0');
   tep_db_perform('products', $sql_data_array);
 }
?>

Your online success is Paramount.

Link to comment
Share on other sites

For the future, I take daily off-site backups of whole websites, including the DB, for critical e-commerce websites.

 

Basically I maintain another domain, on a different server in a different location, and I automatically schedule daily backups via FTP from the active domain's hosting control panel to a folder at the other domain.

 

It's either that or taking backups via FTP to your own computer, because although backups of osCommerce db's in your osCommerce Control Panel would have helped in this situation it wouldn't have helped if the server you're on had got messed up.

 

Vger

Link to comment
Share on other sites

I forgot application top in above script:

 

<?php

require('includes/application_top.php');

$product_query = tep_db_query("select products_id from products_description");
while($product = tep_db_fetch_array($product_query))
{
  $sql_data_array = array('products_id' => $product['products_id'],
                          'products_status' => '0');
  tep_db_perform('products', $sql_data_array);
}
?>

Your online success is Paramount.

Link to comment
Share on other sites

Ok, so first, I created the "products" table, using code from found in /install:

 

DROP TABLE IF EXISTS products;
CREATE TABLE products (
 products_id int NOT NULL auto_increment,
 products_quantity int(4) NOT NULL,
 products_model varchar(12),
 products_image varchar(64),
 products_price decimal(15,4) NOT NULL,
 products_date_added datetime NOT NULL,
 products_last_modified datetime,
 products_date_available datetime,
 products_weight decimal(5,2) NOT NULL,
 products_status tinyint(1) NOT NULL,
 products_tax_class_id int NOT NULL,
 manufacturers_id int NULL,
 products_ordered int NOT NULL default '0',
 PRIMARY KEY (products_id),
 KEY idx_products_date_added (products_date_added)
);

 

And then created a file named db_recover.php containing the small script you posted, uploaded it in /catalog/ and pointed my browser (Firefox, free plug) at it. But I got this as a result:

 

1062 - Duplicate entry '5' for key 1

 

insert into products (products_id, products_status) values ('5', '0')

 

[TEP STOP]

 

So I used PHPMyAdmin to make sure the table was empty, I deleted the "5" entry, went back to execute db_recover.php and got the same thing. Did I do something wrong?

Link to comment
Share on other sites

Try:

 

<?php

require('includes/application_top.php');

$product_query = tep_db_query("select products_id from products_description");
while($product = tep_db_fetch_array($product_query))
{
  $sql_data_array = array(); 
  $sql_data_array = array('products_id' => $product['products_id'],
?             ? ? ? ? ? ? ? ? ? ? ? ? 'products_status' => '0');
?  tep_db_perform('products', $sql_data_array);
}

?>

Your online success is Paramount.

Link to comment
Share on other sites

I'm not so sure that someone did this with phpMyAdmin or that the host got messed up. This exact thing has happened to me at least 2 times before with the products table. It seems like I lost the customers or orders table as well - - don't remember but it was not just the products table.

 

The 2 times (at least) that this happened were on different servers, with different hosts. Same website, I'd just moved hosts. Neither problem happened as part of the move, or very soon after the move was completed. The site had been up and running for at least a month in each case.

 

For this exact reason, I automatically via FTP a db backup off to another server very morning and a full site backup every week. In both cases, I had a backup, so my only problem was a little downtime while I restored those tables.

 

I think that this is a bit more than a conincidence, though - - both of us have lost at least the products table, thorough no apparent action of our own.

 

-jared

Link to comment
Share on other sites

I'm not so sure that someone did this with phpMyAdmin or that the host got messed up. 

 

It really was a stupid "accident" actualy. I gave acces to PHPMyAdmin so the owner could take a look at what the database actualy looks like. He tried to modify some products weights and thought that pressing "Drop" would save the modifications. Oh well.

 

Yesudo, I've tried your new script, but I'm still getting the same error message. I wish I could debug it myself, but I'm far from competent enough to do so. PHP and MySQL are kinda new to me and I'm slowly getting used to them, reading through the documentation, trying to find out what to do.

 

So again, your help and everyone else's is very appreciated.

Link to comment
Share on other sites

Tried and tested:

 

<?php
require('includes/application_top.php');

$product_query = tep_db_query("select products_id from products_description");
while($product = tep_db_fetch_array($product_query))
{
 tep_db_query("insert into products (products_id, products_status) values ('" . $product['products_id'] . "', '0')");
}
?>

Your online success is Paramount.

Link to comment
Share on other sites

Oops, I guess I'm a bit late. Last night, I spent some time on this problem with a friend of mine who know a lot more than I do about MySQL, and we came up with this code, which is apperently working fine too

 

<?php

require('includes/application_top.php');

$product_query = tep_db_query("select products_id from products_description group by products_id");
while($product = tep_db_fetch_array($product_query))
{
 $sql_data_array = array();
 $sql_data_array = array('products_id' => $product['products_id'],
                                     'products_status' => '0');
  tep_db_perform('products', $sql_data_array);
}

?>

 

We simply modified the "$product_query" line at the end, emptied the Products table and executed the script. The problem seemed to be that the product_id was being used as the primary key, but it contained duplicates in the products_attributes table, or something like that.

 

Again, thank you very, very much for your help!

Link to comment
Share on other sites

Now that you have your products records as well as the products description to faciliate updating all products you are probably best of with using quick updates to add model numbers.

If you don't have tax classes, you can easily enter prices too.

If you have prices with tax in your shop, I'd use easy populate to create excel files, and enter tax class, price, weigth, image path, manufacturers and categories data.

Note: for easy populate you must have the model field filled out as it acts as the key

 

HTH - Good luck

Carine

PS The duplicate products_id is because the description is stored for each language

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...