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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
oskare100
Forum Commoner
Posts: 80
Joined: Sun Oct 29, 2006 5:47 am

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

Post by 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):

Code: Select all

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):

Code: Select all

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
Forum Commoner
Posts: 80
Joined: Sun Oct 29, 2006 5:47 am

Re: Have tab file with one product/line and want to update sql

Post by 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.
Post Reply