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 Code (markup): 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, '', ''), Code (markup): 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
No need for script i think. Create dummy table and import text file with phpMyAdmin. Then simple SQL command (JOIN) to update price in original table
Hello, That might work but the problem is that the products in the tab spearated price file is including vat and the prices in the database is excluding vat. So how would it be possible to take all prices multiplied with 0.8 to get the correct prices? Oskar
I have all the prices in a table called dummy in the row test 4. Then I have the prices I want to replace in the table products and in a row with the name products_prices. Both are in the same format (decimal). How would the JOIN command look to update the prices? I can't find any good guide on how to use it...
sry my error JOIN not even need. use sth like UPDATE products, dummy SET products.price=(SELECT dummy.price FROM dummy WHERE products.prodtext LIKE CONCAT('%prod=', dummy.pid)) Code (markup): in CONCAT() you need replace '%prod=' with '%product_info.php?products_id=' Has worked for me in test tables. but you do backup your products table, before you try!
OK, I wrote in that command about 30 minutes in phpmyadmin ago and it's still working/loading.... I've about 6000 rows as mentioned, but should it really take this long?
open second myadmin and check if already data in .price. If root access use cat /proc/loadavg to check server load
[root@localhost /]# cat /proc/loadavg 1.06 1.08 1.00 2/146 5255 Can't get access to any page that is powered by MySQL...
It didn't work, I had to restart mysqld now since everything stopped to respond... By the way, only the products with products_price = 0 in the products table needs to be updated. Maybe I did something wrong?
Oh, and I just noticed that there actually is a row in the dummy table with the products_ids... I didn't think that the row was correct updated but it was so therefore I wount need to LIKE CONCAT('%prod=', dummy.pid)) part?
Solved the problem, used: "UPDATE products SET products_price=(SELECT test4 FROM dummy WHERE products_id = test1) WHERE products_price = '0.0000'" Really thanks mythbuster08 for your help!
if both products_id in both table is the same, you not need conact(). thought you have products_id only in the link. must misunderstood. might be then this: UPDATE products SET products.price = (SELECT dummy.price FROM dummy WHERE dummy.prodid = products.products_id) WHERE products.price = 0