[SOLVED] Opencart to Google merchant

Code: Select all

<?php
// Replace the following variables with your own values

	$servername = "";
	$username = "";
	$password = "!!";
	$dbname = "";
	$file_nameG = "/path/to/Google_Merchant_Export";
	$file_nameFB = "/path/to/Facebook_Merchant_Export.csv";


	// Create connection
	$conn = mysqli_connect($servername, $username, $password, $dbname);


	// Check connection
	if (!$conn) {
		die("Connection failed: " . mysqli_connect_error());
	} else {
	//	print_r( "Connected successfully<br>");
	}


	$sql = "SELECT
		DISTINCT
		p.sku as id,
		p.model,
		p.image AS image_link,
		i.image AS additional_image_link,
		p.status AS availability,
		p.date_available AS availability_date,
		Round(p.weight, 4) AS shipping_weight,
		p.quantity,
		Round(p.price, 2) as price,
		Round(s.price, 2) as sale_price,
		d.name as title,
		d.description,
		m.name AS brand,
		u.keyword as link,
		max_cat.category_id AS highest_category_id,  -- Selecting the highest category_id
		SUBSTRING_INDEX(group_concat(CASE a.attribute_id WHEN '34' THEN a.text END), ',', 1) `condition`,
		SUBSTRING_INDEX(group_concat(CASE a.attribute_id WHEN '35' THEN a.text END), ',', 1) `gender`,
		SUBSTRING_INDEX(group_concat(CASE a.attribute_id WHEN '36' THEN a.text END), ',', 1) `size`,
		SUBSTRING_INDEX(group_concat(CASE a.attribute_id WHEN '37' THEN a.text END), ',', 1) `color1`,
		SUBSTRING_INDEX(group_concat(CASE a.attribute_id WHEN '38' THEN a.text END), ',', 1) `color2`,
		SUBSTRING_INDEX(group_concat(CASE a.attribute_id WHEN '41' THEN a.text END), ',', 1) `material`
	FROM
		db_product_attribute a
	LEFT JOIN
		db_product p ON p.product_Id = a.product_id
	LEFT JOIN
		db_product_special s ON p.product_id = s.product_id
	LEFT JOIN
		db_product_description d ON p.product_id = d.product_id
	LEFT JOIN
		db_manufacturer m ON p.manufacturer_id = m.manufacturer_id
	LEFT JOIN
		db_product_image i ON p.product_id = i.product_id
	LEFT JOIN
		db_seo_url u ON CONCAT('product_id=',p.product_Id) = u.query
	LEFT JOIN (
		SELECT c.product_id, c.category_id
		FROM db_product_to_category c
		INNER JOIN (
			SELECT product_id, MAX(category_id) AS max_category
			FROM db_product_to_category
			GROUP BY product_id
		) AS subq ON c.product_id = subq.product_id AND c.category_id = subq.max_category
	) AS max_cat ON p.product_id = max_cat.product_id  -- Left join the subquery for max category
	WHERE
		AND p.quantity > 0
		AND p.status = 1
	GROUP BY
		a.product_id;";


	// Execute the query
	$result = mysqli_query($conn, $sql);
	//print_r($result->rows);

	// Set the headers for the Excel file
	// Set the headers for the tab-delimited file
	header('Content-Type: text/plain');


	// Generate a downloadable file
	// header('Content-Disposition: attachment; filename="' . $file_name . '.txt"');


	// Create the headers for the file
	$googlel = "id \t title \t google_product_category \t description \t price \t condition \t link \t image_link \t additional_image_link \t availability \t availability_date \t sale_price \t brand \t adult \t age_group \t color \t gender \t size \t material \t shipping_weight\n";
	$facebookl = "id \t title \t fb_product_category \t google_product_category \t description \t availability \t condition \t custom_label_0 \t price \t quantity_to_sell_on_facebook \t link \t image_link \t additional_image_link \t brand \t sale_price \t is_adult_product \t age_group \t color \t gender \t size \t material \t is_outfit_set \t shipping_weight \t is_costume\n";


	getProductIdOnCondition($result, $googlel, $facebookl);


	mysqli_close($conn);


	function getProductIdOnCondition($result, $googlel, $facebookl) {

		global $file_nameG;
		global $file_nameFB;


		$AVAILABILITY_G = "in_stock";
		$AVAILABILITY_F = "in stock";
		$ADULT = "no";

		// Open the file for writing
		$facebook_handle = fopen($file_nameFB, 'w');


		$facebook_array = explode("\t", $facebookl);
		fputcsv($facebook_handle, $facebook_array);


		// Loop through the data and output it to the Excel file
		while ($row = mysqli_fetch_assoc($result)) {

			if () {

				//a bunch of conditions where the variables and data is set or you can skip this and set the info directly from the array

			}

			$googlel .= $row['id'] . "\t" . $row['title'] . "\t" . $goog_category . "\t" . $row['description'] . "\t" . $row['price'] . " USD \t" . $CONDITION . "\t https://yourwebsite.com/" . $row['link'] . "\t https://yourwebsite.com/image/" . $row['image_link'] . "\t https://yourwebsite.com/image/" . $row['additional_image_link'] . "\t" . $AVAILABILITY_F . "\t" . $row['availability_date'] . "\t" . $SALE_PRICE . "\t" . $row['brand'] . "\t" . $ADULT . "\t" . $AGE_GROUP . "\t" . $COLOR . "\t" . $GENDER . "\t" . $SIZE . "\t" . $row['material'] . "\t" . number_format($row['shipping_weight'], 4, ".", "") . "\n";

			$facebook_string = $row['id'] . "\t" . $row['title'] . "\t" . $fb_category . "\t" . $goog_category . "\t" . $row['description'] . "\t" . $AVAILABILITY_F . "\t" . $CONDITION . "\t" . $row['custom_label_0'] . "\t" . $row['price'] . " USD \t" . $row['quantity'] . "\t https://yourwebsite.com/" . $row['link'] . "\t https://yourwebsite.com/image/" . $row['image_link'] . "\t https://yourwebsite.com/image/" . $row['additional_image_link'] . "\t" . $row['brand'] . "\t" . $SALE_PRICE . "\t" . $ADULT . "\t" . $AGE_GROUP . "\t" . $COLOR . "\t" . $GENDER . "\t" . $SIZE . "\t" . $row['material'] . "\t" . $SET . "\t" . number_format($row['shipping_weight'], 4, ".", "") . " lb\t" . $COSTUME . "\n";

			$facebook_array = explode("\t", $facebook_string);

			fputcsv($facebook_handle, $facebook_array);

		}

		file_put_contents($file_nameG . '.txt', $googlel);

		fclose($facebook_handle);

	}
?>