...

View Full Version : Have one tab separated file with one product/line and want to update an sql database



oskare100
03-13-2008, 05:31 PM
Hello,
I really need help with this ASAP. The prices for 2000 products of 6000 products in my new webshop has dissapeared and the only thing I have is a tab separated price file.

Here is how the tab separated price file looks like (a total of 6000 lines):

3713 mobilesp,Reservdelar, battery to Samsung SGH-N400 10 http://www.xxxxxxxxx.com/product_info.php?products_id=3711
3714 testproducts, Test product two 11 http://www.xxxxxxxxx.com/product_info.php?products_id=3712 http://www.xxxxxxxxx.com/images/TAS000335.jpg
3715 mobilesp,Reservdelar,keypad to Siemens S55 10 http://www.xxxxxxxxx.com/product_info.php?products_id=3713
3716 Kameraprodukter, Mobile phone test 12 http://www.xxxxxxxxx.com/product_info.php?products_id=3714 http://www.xxxxxxxxx.com/images/TAS000323.jpg
3717 memoryproducts,Memory card reader 66 http://www.xxxxxxxxx.com/product_info.php?products_id=3715 http://www.xxxxxxxxx.com/images/KAR000234.jpg
3718 mobilesp,Reservdelar, Card for keypad 1 för sony K300, K300i, K310i 10 http://www.xxxxxxxxx.com/product_info.php?products_id=3716
3719 memoryproducts, Memory reader 23 i 1 49 http://www.xxxxxxxxx.com/product_info.php?products_id=3717 http://www.xxxxxxxxx.com/images/KAR000231.jpg

The database has the following structure (a total of 6000 rows):

INSERT INTO `products` (`products_id`, `products_quantity`, `products_on_order`, `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`, `suppliers_id`, `products_ordered`, `products_bundle`, `products_thickness`, `products_ean`, `products_comments`) VALUES
(1845, 0, 0, '', NULL, 13.1640, '2008-01-29 17:14:40', NULL, NULL, 100.00, 1, 1, 14, 'cable USB-SIE DCA540', 0, '', 10, '', ''),
(1846, 0, 0, '', NULL, 61.6680, '2008-01-29 18:37:52', NULL, NULL, 100.00, 1, 1, 14, 'Cable USB LG C3310', 0, '', 10, '', ''),
(1847, 0, 0, '', NULL, 5.0000, '2008-01-29 18:43:41', NULL, NULL, 100.00, 1, 1, 14, 'Data cable MOT UC200 ', 0, '', 10, '', ''),
(1848, 0, 0, '', 'testimage.jpg', 50.7200, '2008-01-29 18:50:20', NULL, NULL, 100.00, 1, 1, 14, 'Data LG G2030/G2120 7130', 0, '', 10, '', ''),
(1849, 0, 0, '', NULL, 53.3120, '2008-01-29 18:56:22', NULL, NULL, 100.00, 1, 1, 14, 'Data BEN/SIE S68, 0, '', 10, '', ''),
(1850, 0, 0, '', 'KAGG553.jpg', 46.8760, '2008-01-29 19:02:36', NULL, NULL, 100.00, 1, 1, 14, 'Data L5100', 0, '', 10, '', ''),

In the end of each URL on each line of the price file there are an ID ("....o.php?products_id=3716"). There is also a price on each line in the price file, as for example "12" on the line whith the product ID 3716 in the sample price file above.

1) First the script needs to find the product ID and price on each line of the price file.

2) Then the script needs to select the product with that product ID in the database and update the price to the price from the price file.

In other words, with for example product 3716 the script should select the row with the products_id 3716 and then set the products_price to 12.

I'm not good at PHP but I've figured out that it needs to be a loop that goes through all lines of the tab separated price file. What I don't know is how to do that, I've never worked with tab separated text files before in PHP.

Then it should probably be a much easier way to do this since it's a tab separated price file but what I thought is that when the script has selected the line it should search for "....product_info.php?products_id=" and assing a variable to the following letters. Then the price is just before the "http://ww..." so that should be to just find and assign a variable to the price that comes before that. But then the script wouldn't take any advantage of that it's a tab separated file....

I really appreciate all help I can get. I've worked with adding all these products for three a months.

Thanks in advance,
Oskar R

oskare100
03-13-2008, 08:09 PM
Solved the problem, used SQL command: "UPDATE products SET products_price=(SELECT test4 FROM dummy WHERE products_id = test1) WHERE products_price = '0.0000' and added the tab separated file to a database.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum