[SOLVED] export to csv / alternative to AND operator / advices with the logic

[johnwayne77]

could somebody tell me a logic to resolve this issue:

 

i need to export all products from a store (in .csv format) and then import it into another store.

 

1. all product details are located in one table

2. images currently uploaded (only paths) are located in another table

3. they are linked via a product_id field

4 !!! -> the problem is that a product sometimes has more than 1 picture so I have 3 values for the same product_id in the images table

 

Now, i managed to export the product details via the following script:

 

$table = 'cp_products';
$csv = NULL;
/* link identifier from db connection */
$conn_id = dbconn($host, $db_name, $user, $pass);
$r = mysql_query("SHOW COLUMNS FROM cp_products");
while ($row = mysql_fetch_assoc($r)) {
$csv .= $row['Field'].',';
}
$csv = substr($csv, 0, -1)."\n";
$r = mysql_query("SELECT cp_products.*
FROM cp_products, cp_categories
WHERE cp_products.cat_id = cp_categories.cat_id AND cp_categories.cat_parent = '101' ORDER BY cp_products.cat_id LIMIT 0, 200");
while ($row = mysql_fetch_assoc($r)) {
$csv .= '"'.join('","', str_replace('"', '""', $row))."\"\n";
}

header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv; filename=" . date("Y-m-d") .
"_".$table.".csv; size=".strlen($csv));
echo $csv;
exit;

 

this would be the select code for adding images too:

 

$r = mysql_query("SHOW COLUMNS FROM cp_products");
while ($row = mysql_fetch_assoc($r)) {
$csv .= $row['Field'].',';
}
$csv .= 'img_src500,';
$csv = substr($csv, 0, -1)."\n";
$r = mysql_query("SELECT cp_products.*,cp_prod_img.img_src500
FROM cp_products, cp_categories, cp_prod_img
WHERE cp_products.cat_id = cp_categories.cat_id AND cp_categories.cat_parent = '101' AND cp_prod_img.prod_id = cp_products.product_id ORDER BY cp_products.product_id LIMIT 0, 200");
while ($row = mysql_fetch_assoc($r)) {
$csv .= '"'.join('","', str_replace('"', '""', $row))."\"\n";
}

 

my problem is in this condition:

 

cp_prod_img.prod_id = cp_products.product_id

 

+ that i need only 1 pic extracted for each product

 

because it extracts only the products that have pictures..

 

i was thinking if there is something else than "AND" operator in SQL.. something more auxiliar so it won't dump the no-pic products..

 

i hope you understood the situation.. i am still thinking of a solution.. maybe u have some advices...

 

thanks

 

[ignace]

why?

 

<?php
$csv .= 'img_src500,';
$csv = substr($csv, 0, -1)."\n";
?>

 

as this is the same as:

 

<?php
$csv .= 'img_src500';
?>

 

can you also provide a sample of what it gives you?

[sasa]

look LEFT JOIN MySQL command

[johnwayne77]

this is how i fixed the issue:

 

$r = mysql_query("SELECT cp_products.*
FROM cp_products, cp_categories
WHERE cp_products.cat_id = cp_categories.cat_id AND cp_categories.cat_parent = '101' ORDER BY cp_products.product_id LIMIT 0, 200");
while ($row = mysql_fetch_assoc($r)) {
$csv .= '"'.join('","', str_replace('"', '""', $row));

$pid = $row['product_id'];
//echo "hey man " . $pid;
$i = mysql_query("SELECT cp_prod_img.img_src500 from cp_products, cp_prod_img WHERE cp_prod_img.prod_id = $pid ORDER BY cp_products.product_id LIMIT 0, 1");
$num_rows = mysql_num_rows($i);
if ($num_rows > 0)
{
	$i2 = mysql_result($i, 0);
}
else
{
	$i2 = "nopic.jpg";
}
$csv .= "\",\"" . $i2 . "\"\n";	

}