Jump to content

Time to put my $ to work..... :)


rseigel
 Share

Recommended Posts

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 by rseigel (see edit history)

Share this post


Link to post
Share on other sites

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 by bellini13 (see edit history)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Nevermind....I'm an idiot (that can't read apparently). :blink:

 

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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. :P

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More