rseigel Posted October 2, 2013 Share Posted October 2, 2013 (edited) I have been struggling trying to get the base image URL and the product URL so I could stuff them into a csv file. Here's the php file I'm using: mysql_connect("localhost", "xxxxxxxxx", "xxxxxxxx") or die(mysql_error()); mysql_select_db("xxxxxxxx") or die(mysql_error()); mysql_query("DROP TABLE IF EXISTS tmp_price_compare") or die(mysql_error()); mysql_query("CREATE TABLE IF NOT EXISTS tmp_price_compare ( supplier_reference varchar(32) DEFAULT NULL, quantity int(10) NOT NULL DEFAULT '0', price decimal(20,6) NOT NULL DEFAULT '0.000000', wholesale_price decimal(20,6) NOT NULL DEFAULT '0.000000' ) ENGINE=InnoDB DEFAULT CHARSET=utf8") or die(mysql_error()); mysql_query("CREATE INDEX indx_tmp ON tmp_price_compare (supplier_reference)") or die(mysql_error()); echo " - - - SHOPPING CREATION STARTED - - - </br>\n"; $result = mysql_query("SELECT p.upc AS 'Product-id', '3' AS 'Product-id-type', '11' AS 'Item-condition', IFNULL( p.price - sp.reduction, p.price ) AS 'Price', p.supplier_reference as 'SKU', sa.quantity as 'Quantity', '' as 'Item-Note', '2' as 'Shipping-time', 'N' as 'Will-ship-internationally', 'N' as 'Expedited-shipping', '7.95' as 'Standard-shipping-charge', '' as 'Expedited-shipping-charge', '' as 'International-shipping-charge' FROM product p LEFT JOIN specific_price sp USING ( id_product ) JOIN stock_available sa USING ( id_product ) WHERE p.upc > ''"); $num_fields = mysql_num_fields($result); $headers = array(); for ($i = 0; $i < $num_fields; $i++) { $headers[] = mysql_field_name($result , $i); } $csv_filename = "a1Outlet_PURGE.txt"; $fp = fopen($csv_filename, 'w+'); if ($fp && $result) { fputcsv($fp, $headers, $delimiter = ' '); while ($row = mysql_fetch_row($result)) { fputcsv($fp, array_values($row), $delimiter = ' '); } } echo " - - - SHOPPING FILE CREATION ENDED - - - </br>\n"; Sooooo....I simply need to know what code I need to put to get those two things for each product and where to put it. I'm willing to pay a quick $30US to the first person that can solve this little problem for me. I'd love if it was built into the SELECT somehow but in the end I just want it to work. Feel free to send it to me via PM if you don't want to post it here. You'll have to trust me on the $30 but I can assure you that as soon as I confirm it works I'll send you the money via paypal ASAP. This should be simple for the caliber of PS developers that are here. BTW....this is the entire script so any calls that need to be made to PS need to be made here. It's a standalone PHP script that, at this point, only connects to PS via the database. Edited October 2, 2013 by rseigel (see edit history) Link to comment Share on other sites More sharing options...
rseigel Posted October 2, 2013 Author Share Posted October 2, 2013 I should also mention that I really need this ASAP. So if any awesome dev out there is willing to help...... Link to comment Share on other sites More sharing options...
bellini13 Posted October 2, 2013 Share Posted October 2, 2013 (edited) Update: this should do it. You might need to update the database table prefix, since I added ps_ to them. Also not sure what you wanted for the headers of the new columns, so I just guessed. <?php //assumes this script is going to be executed from the root folder of Prestashop store. require(dirname(__FILE__).'/config/config.inc.php'); //create table command $sql1="CREATE TABLE IF NOT EXISTS tmp_price_compare ( supplier_reference varchar(32) DEFAULT NULL, quantity int(10) NOT NULL DEFAULT '0', price decimal(20,6) NOT NULL DEFAULT '0.000000', wholesale_price decimal(20,6) NOT NULL DEFAULT '0.000000' ) ENGINE=InnoDB DEFAULT CHARSET=utf8"; Db::getInstance()->execute('DROP TABLE IF EXISTS tmp_price_compare'); Db::getInstance()->execute($sql1); Db::getInstance()->execute('CREATE INDEX indx_tmp ON tmp_price_compare (supplier_reference)'); echo " - - - SHOPPING CREATION STARTED - - - </br>\n"; $sql2 = "SELECT p.upc AS 'Product-id', '3' AS 'Product-id-type', '11' AS 'Item-condition', IFNULL( p.price - sp.reduction, p.price ) AS 'Price', p.supplier_reference as 'SKU', sa.quantity as 'Quantity', '' as 'Item-Note', '2' as 'Shipping-time', 'N' as 'Will-ship-internationally', 'N' as 'Expedited-shipping', '7.95' as 'Standard-shipping-charge', '' as 'Expedited-shipping-charge', '' as 'International-shipping-charge', id_product FROM ps_product p LEFT JOIN ps_specific_price sp USING ( id_product ) JOIN ps_stock_available sa USING ( id_product ) WHERE p.upc > ''"; $result = Db::getInstance()->executeS($sql2); $link=new Link(); foreach ($result as &$product) { $productName=Product::getProductName($product['id_product']); $cover=Product::getCover($product['id_product']); $product['URL']=$link->getProductLink($product['id_product']); $product['IMAGE_URL']=$link->getImageLink($productName, $cover['id_image']); } var_dump ($result); $headers = array('Product-id', 'Product-id-type', 'Item-condition', 'Price', 'SKU', 'Quantity', 'Item-Note', 'Shipping-time', 'Will-ship-internationally', 'Expedited-shipping', 'Standard-shipping-charge', 'International-shipping-charge', 'URL', 'IMAGE_URL'); $num_fields = 14; $csv_filename = dirname(__FILE__)."/a1Outlet_PURGE.txt"; $fp = fopen($csv_filename, 'w+'); if ($fp && $result) { fputcsv($fp, $headers, $delimiter = ' '); foreach ($result as $product) { unset($product['id_product']); fputcsv($fp, array_values($product), $delimiter = ' '); } } echo " - - - SHOPPING FILE CREATION ENDED - - - </br>\n"; ?> Edited October 2, 2013 by bellini13 (see edit history) Link to comment Share on other sites More sharing options...
rseigel Posted October 2, 2013 Author Share Posted October 2, 2013 Wow! Couldn't resist fixing my bad habits could ya? Much appreciated. I'm getting the following error: - - - SHOPPING CREATION STARTED - - - bool(false) - - - SHOPPING FILE CREATION ENDED - - - When I narrowed it down it appears that this line, var_dump ($result); is causing the problem. It stops executing there and doesn't generate the CSV. Can you help me fix that? Also, PM me your paypal email so I can keep up my end of the bargain once it's up and running. Link to comment Share on other sites More sharing options...
rseigel Posted October 2, 2013 Author Share Posted October 2, 2013 Nevermind....I'm an idiot (that can't read apparently). I missed the "You might need to update the database table prefix, since I added ps_ to them." As soon as I removed the ps_ everything worked fine. Now I just need that email addy. Link to comment Share on other sites More sharing options...
rseigel Posted October 2, 2013 Author Share Posted October 2, 2013 Grrrr.....I'm trying to get rid of the id_product column in the CSV file and just can't get it to work. Any ideas? Link to comment Share on other sites More sharing options...
bellini13 Posted October 3, 2013 Share Posted October 3, 2013 Did you remove the unset from the code? This should be removing it before it gets written to the csv file. It does not get output to my csv file. I'll PM you with the email address. unset($product['id_product']); Link to comment Share on other sites More sharing options...
rseigel Posted October 3, 2013 Author Share Posted October 3, 2013 Ok..... $headers = array('Product-id', 'Product-id-type', 'Item-condition', 'Price', 'SKU', 'Quantity', 'Item-Note', 'Shipping-time', 'Will-ship-internationally', 'Expedited-shipping', 'Standard-shipping-charge', 'International-shipping-charge', 'URL', 'IMAGE_URL'); $num_fields = 14; $csv_filename = dirname(__FILE__)."/a1Outlet_PURGE.txt"; $fp = fopen($csv_filename, 'w+'); if ($fp && $result) { fputcsv($fp, $headers, $delimiter = ' '); foreach ($result as $product) { unset($product['id_product']); fputcsv($fp, array_values($product), $delimiter = ' '); } } This is exactly as I have it. It takes product_id out of the header just fine. When it writes the csv (below the header) though it shifts everything over so the URL column is empty, the URL values are in the IMAGE_URL column, and the IMAGE_URL values are past the headers in the next column. Does that make sense? Link to comment Share on other sites More sharing options...
rseigel Posted October 3, 2013 Author Share Posted October 3, 2013 Whew....I got it! If you compare these two lines: $sql2 = "SELECT p.upc AS 'Product-id', '3' AS 'Product-id-type', '11' AS 'Item-condition', IFNULL( p.price - sp.reduction, p.price ) AS 'Price', p.supplier_reference as 'SKU', sa.quantity as 'Quantity', '' as 'Item-Note', '2' as 'Shipping-time', 'N' as 'Will-ship-internationally', 'N' as 'Expedited-shipping', '7.95' as 'Standard-shipping-charge', '' as 'Expedited-shipping-charge', '' as 'International-shipping-charge', id_product and $headers = array('Product-id', 'Product-id-type', 'Item-condition', 'Price', 'SKU', 'Quantity', 'Item-Note', 'Shipping-time', 'Will-ship-internationally', 'Expedited-shipping', 'Standard-shipping-charge', 'International-shipping-charge', 'URL', 'IMAGE_URL'); you can see that 'Expedited-shipping-charge' was left out of the second one, causing the shift. Whew.....thanks a bazillion. Money was sent a while ago. I'm kinda stoked I was able to work that last little bug out myself. Link to comment Share on other sites More sharing options...
bellini13 Posted October 3, 2013 Share Posted October 3, 2013 headers array was missing the 'Expedited-shipping-charge' column Link to comment Share on other sites More sharing options...
bellini13 Posted October 3, 2013 Share Posted October 3, 2013 lol seems we both found the problem at the same time. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now