Exporting Products Catalog from Prestashop 1.7 to csv file to use on Facebook Shop

user_10135998 0
Viewed 1255

that is working but i have on the product id´s not all in numeric order, for example, i have product id like (1,2,3,4,5,7,10,12,13,14, etc...)

When the script generates de file it creates like, product id (1,2,3,4,5,6,7,8,9,10,11,12,13,14, etc..) and on the id´s that there is not product information it creates a blank id line.

Can some help me to fix this ?

Regard´s

<?php
   // Connecting to database
   
   $con = mysqli_connect('127.0.0.1', 'root', '', 'prestahop');
   
   // check connection
   
   if(mysqli_connect_errno())
   {
       echo "Failed to connect to Mysql : " .mysqli_connect_errno();
           
   }
   
   
   $domain = "127.0.0.1/prestashop";
   
    $names = array();
    $descriptions = array();
    $limit = array();
    $skus = array();
    $prices = array();
     
   
   $stmt = $con->prepare("SELECT id_product,name,description FROM ps_product_lang where id_lang = 1");
   
   $stmt->execute();
   $stmt->store_result();
   //$stmt->bind_result($active,$id,$name,$description);
   $stmt->bind_result($id,$name,$description);
   
   while($stmt->fetch())
   
   {
       
       //saving names and descriptions in associative array with id as key
       
       $names[$id] = $name;
       $descriptions[$id] = $description;
       
   }
   
   $stmt->close();
   
   $stmt = $con->prepare("SELECT id_product,reference,price FROM ps_product");
   $stmt->execute();
   $stmt->store_result();
   $stmt->bind_result($id,$reference,$price);
   
   while($stmt->fetch())
   
   {
       $limit[] = $id;
           
       //saving id, skus and price in associative array with id as key
       
       $ids[$id] = $id;
       $skus[$id] = $reference;
       $prices[$id] = $price;
   }
   
   $stmt->close();
   
   $fp = fopen("fbshop.csv","w");
   
   // setting column headers
   
   $fields = array(
   
                'id',
                'title',
                'description',
                'availability',
                'condition',
                'price',
                'link',
                'image_link',
                'brand',
                'additional_image_link',
                'age_group'
   
                );
     fputcsv($fp,$fields); 
     $count = 1;
   
     while($count < count($limit))
     {
       $lineData = array(
           
           $ids[$count],
           $names[$count],
           //$descriptions[$count],
           strip_tags($descriptions[$count]),
           'in stock',
           'New',
           number_format((float)$prices[$count],2,'.',''),
           $domain . 'index.php?id_product=' . $ids[$count] . '&controller=product',
           $domain . $ids[$count] . '-large_default/' . str_replace(" "," ",strtolower($names[$count])) . '.jpg',
           'BrandName',
           $domain . $ids[$count] . '-large_default/' . str_replace(" ","-",strtolower($names[$count])) . '.jpg',
           'adult'
        );  
         
         fputcsv($fp,$lineData);
         $count++;
         
         
     }
   
   fclose($fp);
   
   ?>
prestashop-1.7 sql php
2 Answers

This script assumes that there are no "holes" between the product ids...

To avoid that a non-existent id is written in the CSV fule you can insert a check immediately after :

while($count < count($limit))
{

Like :

if (!isset($ids[$count])) {
    continue;
}
user_32568430

maybe you could do the sql query this way:

<?php

error_reporting(E_ALL & ~E_NOTICE);
ini_set('display_errors', '1');

$db = mysqli_connect('127.0.0.1', 'root', 'password', 'my_presta_bd');

if (mysqli_connect_errno()) {
    echo "Failed to connect to Mysql : " . mysqli_connect_errno();

}

$sql = "SELECT pp.id_product, pp.reference, pp.price, ppl.name, ppl.description FROM ps_product pp LEFT JOIN ps_product_lang ppl ON (ppl.id_product = pp.id_product) WHERE ppl.id_lang = 1;";
$query = mysqli_query($db, $sql);

Then you can save the data in your csv file, into the while:

while($product_list = mysqli_fetch_assoc($query)){

   
    $id_product     = $product_list['id_product'];
    $reference      = $product_list['reference'];
    $price          = $product_list['price'];
    $name           = $product_list['name'];
    $description    = $product_list['description'];
    
    echo $id_product.' - '.$reference.' '.$price.' '.$name.' '.$description.' <br>';
    
}

for example to product id 14:

$sql = "SELECT pp.id_product, pp.reference, pp.price, ppl.name, ppl.description FROM ps_product pp LEFT JOIN ps_product_lang ppl ON (ppl.id_product = pp.id_product) WHERE ppl.id_lang = 1 AND pp.id_product <= 14;";

Do you also have to export the combination of products?

PD: To get only active products, sql query:

SELECT pp.id_product, pp.reference, pp.price, ppl.name, pp.active, ppl.description FROM ps_product pp LEFT JOIN ps_product_lang ppl ON (ppl.id_product = pp.id_product) WHERE ppl.id_lang = 1 AND pp.id_product <= 14 AND pp.active = 1
user_94465150
Related