Have one tab separ file with one product/line and want to update an sql database

[oskare100]

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

[sasa]

<?php
$a = file('insert_filename_here');
// connect db
foreach ($a as $b) {
preg_match('/([0-9]+)\s*http[^0-9]*([0-9]+)/',$b,$c);
$price = $c[1];
$id = $c[2];
// update table
}
?>

[oskare100]

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.