Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

How do I extract a datafeed from mysql database?


aussiemum

Recommended Posts

Posted

Hi, as I said I need to get product data from the mysql to give to price comparison sites. The sites require: product ID, product category, product name, product url, price, availability. I have been in the export section of phpmyadmin & downloaded a couple of test .csv's but it is not what I am looking for. I thnk I need a script or something that will work by pulling all the information together & include the url's & ID's.

 

I was given a php script that works on the older version of osc, I am using 2.2 & this script does not work (so I am told). Maybe someone here can edit the script so it does work? Here it is:

 

#!/usr/local/bin/php
<?php

$imageURL = 'http://www.yoursite.com/x-scripts/oscommerce/images/'; // CHANGEME: to the location of your images directory
$productURL = 'http://www.yoursite.com/cgi-bin/oscommerce/product_info.php?products_id='; // CHANGME: to the location of your products page.

$home="localhost"; // CHANGEME: location of your database (probably localhost)
$user="username"; // CHANGEME: username to log into the database
$pass="password"; // CHANGEME: password to log into the database
$base="database"; // CHANGEME: name of the database to log into.
$addGST=false; //CHANGEME: set to false if the price includes GST.
$gstRate=1.1; // CHANGEME: default is 10% for GST

// End of CHANGEME section.

header("Content-type: text/plain");
$already_sent = array();
if (!($link=mysql_connect($home,$user,$pass))) {
echo "Error: Unable to connecto to database with user/pass";
exit();
}
if (!mysql_select_db( $base , $link )) {
echo "Error: Connected, but database does not exist.";
exit();
}

$sql = "
SELECT concat( '" . $productURL . "' ,osc_products.products_id) AS product_url,
products_model AS prodModel, products_weight,
osc_manufacturers.manufacturers_name AS mfgName,
osc_manufacturers.manufacturers_id,
osc_products.products_id AS id,
osc_products_description.products_name AS name,
osc_products_description.products_description AS description,
osc_products.products_status AS prodStatus,
osc_products.products_price AS price,
osc_specials.specials_new_products_price AS special_price,
CONCAT( '" . $imageURL . "' ,osc_products.products_image) AS image_url,
osc_products_to_categories.categories_id AS prodCatID,
osc_categories.parent_id AS catParentID,
osc_categories_description.categories_name AS catName
FROM osc_categories,
osc_categories_description,
osc_products,
osc_products_description,
osc_products_to_categories

left join osc_manufacturers on ( osc_manufacturers.manufacturers_id = osc_products.manufacturers_id )
left join osc_specials on osc_products.products_id = osc_specials.products_id

WHERE osc_products.products_id=osc_products_description.products_id
AND osc_products.products_id=osc_products_to_categories.products_id
AND osc_products_to_categories.categories_id=osc_categories.categories_id
AND osc_categories.categories_id=osc_categories_description.categories_id
ORDER BY
osc_products.products_id ASC
";


$catInfo = "
SELECT
osc_categories.categories_id AS curCatID,
osc_categories.parent_id AS parentCatID,
osc_categories_description.categories_name AS catName
FROM
osc_categories,
osc_categories_description
WHERE osc_categories.categories_id = osc_categories_description.categories_id
";

function findCat($curID, $catTempPar, $catTempDes, $catIndex)
{
if($catTempPar[$curID] != 0 )
{
	if($catIndex[$catTempPar[$curID]] != null)
	{
		$temp=$catIndex[$catTempPar[$curID]];
	}
	else
	{
		$catIndex = findCat($catTempPar[$curID], $catTempPar, $catTempDes, $catIndex);
		$temp = $catIndex[$catTempPar[$curID]];
	}
}
if($catTempPar[$curID] == 0)
{
	$catIndex[$curID] = $catTempDes[$curID];
}
else
{
	$catIndex[$curID] = $temp . " > " . $catTempDes[$curID];
}
return $catIndex;

}

$catIndex = array();
$catTempDes = array();
$catTempPar = array();
$processCat = mysql_query( $catInfo )or die( $FunctionName . ": SQL error " . mysql_error() . "| catInfo = " . htmlentities($catInfo) );
while ( $catRow = mysql_fetch_object( $processCat ) )
{
$catKey = $catRow->curCatID;
$catName = $catRow->catName;
$catParID = $catRow->parentCatID;
if($catName != "")
{
	$catTempDes[$catKey]=$catName;
	$catTempPar[$catKey]=$catParID;
}
}

foreach($catTempDes as $curID=>$des)  //don't need the $des
{
$catIndex = findCat($curID, $catTempPar, $catTempDes, $catIndex);
}

$_strip_search = array(
"![\t ]+$|^[\t ]+!m", // remove leading/trailing space chars
'%[\r\n]+%m'); // remove CRs and newlines
$_strip_replace = array(
'',
'');

$output = "merUrl\tprice\tcat\tshortDesc\n";

$result=mysql_query( $sql )or die( $FunctionName . ": SQL error " . mysql_error() . "| sql = " . htmlentities($sql) );

while( $row = mysql_fetch_object( $result ) ) {
if ($already_sent[$row->id] == 1) continue; // if we've sent this one, skip the rest of the while loop

if( $row->prodStatus == 1 )	{
	// reset the products price to our special price if there is one for this product
	if ($row->special_price > 0) {
		$row->price = $row->special_price;
		}
	if ($addGST) {
		$row->price = $row->price *  $gstRate;
		}
	$row->price = number_format($row->price, 2, '.', '');

	$output .=
		$row->product_url .
		"\t" .
		$row->price .
		"\t" .
		$catIndex[$row->prodCatID] .
		"\t" .
		preg_replace($_strip_search, $_strip_replace, strip_tags( str_replace(">", "> ", $row->name) ) ) .
		"\n";
	}
$already_sent[$row->id] = 1;
}

echo $output;
?>

 

Any help will be nice. I searched google for a long time today but have not found any help there.

thanks.

Posted

Is the osc_ prefix present in your current sql tables in the dbase? If not you could remove them from the script and try it again.

Posted
Is the osc_ prefix present in your current sql tables in the dbase? If not you could remove them from the script and try it again.
How do I find that out?
Posted

Use phpMyAdmin just browse the tables. The table names show on the left panel.

Posted
Use phpMyAdmin just browse the tables. The table names show on the left panel.

OK I see what you mean. It would be easyer for me to just pull the information from the database (mysql) without the use of that script. I need to export (csv) with: (product ID, product category, product name, product url, price, availability) Is that possible?

Posted

There is no option with the phpMyAdmin to export selected columns (at least on the version I have) but of course you could copy the dbase to a test one and then delete the non-needed columns. Then you could export the tables.

Posted
There is no option with the phpMyAdmin to export selected columns (at least on the version I have) but of course you could copy the dbase to a test one and then delete the non-needed columns. Then you could export the tables.

Yeah the problem is the formating is all over the place & I can't seem to get the categories to come up. :blush:

I guess it is back to the script, can you tell me how to execute the above script so I can test it to see if it works?

Posted

you place it on the server and you run it. Pretty much like when you access a page with your browser.

Posted
you place it on the server and you run it. Pretty much like when you access a page with your browser.

OK thanks I will give it a go :thumbsup:

Posted

OK just ran the script & I got this error:

: SQL error Unknown column 'products.manufacturers_id' in 'on clause'| sql =

SELECT concat( 'http://www.MY LOVELY SITE.com/cgi-bin/oscommerce/product_info.php?products_id=' ,products.products_id) AS product_url,

products_model AS prodModel, products_weight,

manufacturers.manufacturers_name AS mfgName,

manufacturers.manufacturers_id,

products.products_id AS id,

products_description.products_name AS name,

products_description.products_description AS description,

products.products_status AS prodStatus,

products.products_price AS price,

specials.specials_new_products_price AS special_price,

CONCAT( 'http://www.MY LOVELY SITE.com/x-scripts/oscommerce/images/' ,products.products_image) AS image_url,

products_to_categories.categories_id AS prodCatID,

categories.parent_id AS catParentID,

categories_description.categories_name AS catName

FROM categories,

categories_description,

products,

products_description,

products_to_categories

 

left join manufacturers on ( manufacturers.manufacturers_id = products.manufacturers_id )

left join specials on products.products_id = specials.products_id

 

WHERE products.products_id=products_description.products_id

AND products.products_id=products_to_categories.products_id

AND products_to_categories.categories_id=categories.categories_id

AND categories.categories_id=categories_description.categories_id

ORDER BY

products.products_id ASC

 

Strange because manufacturers_id is in the products section of phpmyadmin.

 

Any ideas?

Posted

I don't see a problem here. The script runs ok. Did you run it in phpMyAdmin and got the error? What's the mysql version you're using?

Posted
Did you run it in phpMyAdmin and got the error?

How do I do that?

 

What's the mysql version you're using?

Welcome to phpMyAdmin 2.6.4-pl2

MySQL 5.0.15-log running on localhost

Posted

you click the sql tab and you paste the code in the "Run SQL query" box Then you hit go.

 

Yea mysql 5.x may explain the problem you're having. Try removing the left joins, set the conditions after the where clause, or expand the sql query with left joins on all tables and try it again.

Posted
you click the sql tab and you paste the code in the "Run SQL query" box Then you hit go.

 

OK did that & got this error:

#!/usr/local/bin/php

 

 

SQL query:

 

#!/usr/local/bin/php

& lt ; ? php$imageURL = ''http://www.MY LOVELY SITE/shop/images/''

 

MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<?php

 

$imageURL = ''http://www.MY LOVELY SITE/shop/images/''' at line 2

 

Yea mysql 5.x may explain the problem you're having. Try removing the left joins, set the conditions after the where clause, or expand the sql query with left joins on all tables and try it again.

 

mmmm will need your help here, can you step me through it?

Posted

you probably posted the entire file contents and you got the error. You only place the sql queries there. Its the code you posted above minus the first line which is the error itself.

 

One case would be to replace these lines:

products_to_categories

left join manufacturers on ( manufacturers.manufacturers_id = products.manufacturers_id )
left join specials on products.products_id = specials.products_id

 

with these

products_to_categories,
manufacturers,
specials,

 

 

Then further down when you have the:

AND products.products_id=products_to_categories.products_id

 

add after it a couple of lines:

AND manufacturers.manufacturers_id = products.manufacturers_id 
AND products.products_id = specials.products_id

Posted

OK did everything you just said. This is the exact code I posted in the sql:

<?php

$imageURL = 'http://www.MY LOVELY SITE.com/shop/images/'; // CHANGEME: to the location of your images directory
$productURL = 'http://www.MY LOVELY SITE.com/shop/all_products.php'; // CHANGME: to the location of your products page.

$home="MY LOVELY SITE"; // CHANGEME: location of your database (probably localhost)
$user="MY LOVELY SITE"; // CHANGEME: username to log into the database
$pass="MY LOVELY SITE"; // CHANGEME: password to log into the database
$base="MY LOVELY SITE"; // CHANGEME: name of the database to log into.
$addGST=false; //CHANGEME: set to false if the price includes GST.
$gstRate=1.1; // CHANGEME: default is 10% for GST

// End of CHANGEME section.

header("Content-type: text/plain");
$already_sent = array();
if (!($link=mysql_connect($home,$user,$pass))) {
echo "Error: Unable to connecto to database with user/pass";
exit();
}
if (!mysql_select_db( $base , $link )) {
echo "Error: Connected, but database does not exist.";
exit();
}

$sql = "
SELECT concat( '" . $productURL . "' ,products.products_id) AS product_url,
products_model AS prodModel, products_weight,
manufacturers.manufacturers_name AS mfgName,
manufacturers.manufacturers_id,
products.products_id AS id,
products_description.products_name AS name,
products_description.products_description AS description,
products.products_status AS prodStatus,
products.products_price AS price,
specials.specials_new_products_price AS special_price,
CONCAT( '" . $imageURL . "' ,products.products_image) AS image_url,
products_to_categories.categories_id AS prodCatID,
categories.parent_id AS catParentID,
categories_description.categories_name AS catName
FROM categories,
categories_description,
products,
products_description,
products_to_categories,
manufacturers,
specials,

WHERE products.products_id=products_description.products_id
AND products.products_id=products_to_categories.products_id
AND manufacturers.manufacturers_id = products.manufacturers_id 
AND products.products_id = specials.products_id
AND products_to_categories.categories_id=categories.categories_id
AND categories.categories_id=categories_description.categories_id
ORDER BY
products.products_id ASC
";


$catInfo = "
SELECT
categories.categories_id AS curCatID,
categories.parent_id AS parentCatID,
categories_description.categories_name AS catName
FROM
categories,
categories_description
WHERE categories.categories_id = categories_description.categories_id
";

function findCat($curID, $catTempPar, $catTempDes, $catIndex)
{
if($catTempPar[$curID] != 0 )
{
	if($catIndex[$catTempPar[$curID]] != null)
	{
		$temp=$catIndex[$catTempPar[$curID]];
	}
	else
	{
		$catIndex = findCat($catTempPar[$curID], $catTempPar, $catTempDes, $catIndex);
		$temp = $catIndex[$catTempPar[$curID]];
	}
}
if($catTempPar[$curID] == 0)
{
	$catIndex[$curID] = $catTempDes[$curID];
}
else
{
	$catIndex[$curID] = $temp . " > " . $catTempDes[$curID];
}
return $catIndex;

}

$catIndex = array();
$catTempDes = array();
$catTempPar = array();
$processCat = mysql_query( $catInfo )or die( $FunctionName . ": SQL error " . mysql_error() . "| catInfo = " . htmlentities($catInfo) );
while ( $catRow = mysql_fetch_object( $processCat ) )
{
$catKey = $catRow->curCatID;
$catName = $catRow->catName;
$catParID = $catRow->parentCatID;
if($catName != "")
{
	$catTempDes[$catKey]=$catName;
	$catTempPar[$catKey]=$catParID;
}
}

foreach($catTempDes as $curID=>$des)  //don't need the $des
{
$catIndex = findCat($curID, $catTempPar, $catTempDes, $catIndex);
}

$_strip_search = array(
"![\t ]+$|^[\t ]+!m", // remove leading/trailing space chars
'%[\r\n]+%m'); // remove CRs and newlines
$_strip_replace = array(
'',
'');

$output = "merUrl\tprice\tcat\tshortDesc\n";

$result=mysql_query( $sql )or die( $FunctionName . ": SQL error " . mysql_error() . "| sql = " . htmlentities($sql) );

while( $row = mysql_fetch_object( $result ) ) {
if ($already_sent[$row->id] == 1) continue; // if we've sent this one, skip the rest of the while loop

if( $row->prodStatus == 1 )	{
	// reset the products price to our special price if there is one for this product
	if ($row->special_price > 0) {
		$row->price = $row->special_price;
		}
	if ($addGST) {
		$row->price = $row->price *  $gstRate;
		}
	$row->price = number_format($row->price, 2, '.', '');

	$output .=
		$row->product_url .
		"\t" .
		$row->price .
		"\t" .
		$catIndex[$row->prodCatID] .
		"\t" .
		preg_replace($_strip_search, $_strip_replace, strip_tags( str_replace(">", "> ", $row->name) ) ) .
		"\n";
	}
$already_sent[$row->id] = 1;
}

echo $output;
?>

 

After I ran that query I got the same error:

SQL query:

 

& lt ; ? php$imageURL = 'http://www.MY LOVELY SITE.com/shop/images/'

 

MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<?php

 

$imageURL = 'http://www.MY LOVELY SITE.com/shop/images/'' at line 1

Posted
The code you need to place in the phpmyadmin box is here: (minus the error line)

http://www.oscommerce.com/forums/index.php?s=&...ndpost&p=761888

 

Then you will see the original error. Apply the changes I mentioned earlier.

 

You keep entering the entire file.

 

Ah yes and remove the coma from

 

specials,

 

to

 

specials

Sorry about that, I was a bit slow there. Well it worked this time. see here:

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0011 sec)

 

SQL query:

SELECT concat( '" . $productURL . "', products.products_id ) AS product_url, products_model AS prodModel, products_weight, manufacturers.manufacturers_name AS mfgName, manufacturers.manufacturers_id, products.products_id AS id, products_description.products_name AS name, products_description.products_description AS description, products.products_status AS prodStatus, products.products_price AS price, specials.specials_new_products_price AS special_price, CONCAT( '" . $imageURL . "', products.products_image ) AS image_url, products_to_categories.categories_id AS prodCatID, categories.parent_id AS catParentID, categories_description.categories_name AS catName

FROM categories, categories_description, products, products_description, products_to_categories, manufacturers, specials

WHERE products.products_id = products_description.products_id

AND products.products_id = products_to_categories.products_id

AND manufacturers.manufacturers_id = products.manufacturers_id

AND products.products_id = specials.products_id

AND products_to_categories.categories_id = categories.categories_id

AND categories.categories_id = categories_description.categories_id

ORDER BY products.products_id ASC

LIMIT 0 , 30

 

Sorry for the stupid question but now what should I do :huh:

Posted

ok so it works. I mean no errors but of course I don't know if the script does what you really want.

Posted
ok so it works. I mean no errors but of course I don't know if the script does what you really want.

Yeah it works but it does not list anything. The script is supposed to list all my products etc under those headings (merUrl, price, cat, shortDesc).

Any idea why they are not showing up?

Posted

I have just been given another feed that should work for me, it too needs changing from the old version of osc to the new 2.2 version. Here is the code:

<?php

require('includes/application_top.php');

// SCRIPT CONFIGURATION
$AllowedIps = array("", "", "");
// END SCRIPT CONFIGURATION


// ONLY ALLOW ACCESS FOR CERTAIN IPS
if (!in_array($_SERVER['REMOTE_ADDR'], $AllowedIps)) {
print "Invalid IP address";
exit;
}

$OutFile = session_save_path()."/temp_file_".rand()."_sfdata.txt";
$destination_file = "sfdata.txt";
$source_file = $OutFile;
$already_sent = array();
// LANGUAGE ID - DEFAULT VALUE IS 1 FOR ENGLISH
$language_id = 1;

// Grab the products
$products_query = tep_db_query("select
manuf.manufacturers_name as manufacturer,
prd.products_id AS id,
'' AS stock,
'' AS mpn,
prd.products_id AS mpc,
prdsc.products_name AS name,
'' AS description,
FORMAT(prd.products_price,2) AS price,
prd.products_image,

if(catdescparent2.categories_name!='',
concat_ws( ' > ' , catdescparent2.categories_name , concat_ws( ' > ' , catdescparent.categories_name , catdesccurrent.categories_name )),
concat_ws( ' > ' , catdescparent.categories_name , catdesccurrent.categories_name ) 
)
AS category

FROM " . TABLE_CATEGORIES . " ,
" . TABLE_PRODUCTS . " prd,
" . TABLE_PRODUCTS_DESCRIPTION . " as prdsc,
" . TABLE_CATEGORIES_DESCRIPTION . " as catdesccurrent,
" . TABLE_PRODUCTS_TO_CATEGORIES . " as prdtocat
left join " . TABLE_CATEGORIES_DESCRIPTION . " as catdescparent on ( catdescparent.categories_id = categories.parent_id AND catdescparent.language_id = '$language_id' )
left join " . TABLE_CATEGORIES . " as cat2 on ( cat2.categories_id = categories.parent_id )
left join " . TABLE_CATEGORIES_DESCRIPTION . " as catdescparent2 on ( catdescparent2.categories_id = cat2.parent_id AND catdescparent2.language_id = '$language_id' )
left join " . TABLE_MANUFACTURERS . " as manuf on ( manuf.manufacturers_id = prd.manufacturers_id )
WHERE 
( prd.products_id=prdsc.products_id AND prdsc.language_id = '$language_id' )
AND prd.products_id=prdtocat.products_id
AND prdtocat.categories_id=categories.categories_id
AND ( catdesccurrent.categories_id = categories.categories_id AND catdesccurrent.language_id = '$language_id' )
AND prd.products_status != 0");

// Check for any applicable specials for the corresponding products_id
$specials_query = tep_db_query("select
specials.products_id as idS,
format(specials.specials_new_products_price,2) as priceS
from
specials,
products
where
specials.products_id=products.products_id
and specials.status != 0
and products.products_status != 0");
while( $row_s = tep_db_fetch_array( $specials_query ) )
{
foreach ($row_s as $i=>$v) {
	$SPECIALS[$row_s['idS']][$i] = $v;
}
}


$_strip_search = array(
"![\t ]+$|^[\t ]+!m", // Remove leading/trailing space chars
'%[\r\n]+%m',
); // Remove CRs and newlines
$_strip_replace = array(
'',
'',
);

$output = "Category\tISBN\tGTIN\tManufacturer\tModel\tMerchant Product Code\tProduct Name\tProduct Description\tProduct URL\tProduct Image URL\tStock\tPrice\n";

// PRINT THE PRODUCTS
while( $row = tep_db_fetch_array( $products_query ) )
{
// If we've sent this one, skip the rest of the while loop
if ($already_sent[$row['product_url']] == 1) continue;

$row['product_url'] = tep_href_link(FILENAME_PRODUCT_INFO, 'products_id='.$row['id'], 'NONSSL', false);
if($row['products_image'])
{
	$row['image_url'] = HTTP_SERVER . DIR_WS_CATALOG . DIR_WS_IMAGES . $row['products_image'];
}	

// Reset the products price to our special price if there is one for this product
if( $SPECIALS[$row['id']]['idS'] ){
	$row['price'] = $SPECIALS[$row['id']]['priceS'];
}

// $QTY IS THE QUANTITY VARIABLE	
if($row['stock']>0){
	$row['stock'] = "";
}
elseif($row['stock']==0){
	$row['stock'] = "";
}
else{
	$row['stock'] = "";
}

$output .=
$row['category'] . "\t\t\t" .
$row['manufacturer'] . "\t" .
$row['mpn'] . "\t" .
$row['mpc'] . "\t" .
preg_replace($_strip_search, $_strip_replace, $row['name']) . "\t" .
preg_replace($_strip_search, $_strip_replace, $row['description']) . "\t" .
$row['product_url'] . "\t" .
$row['image_url'] . "\t" .
$row['stock'] . "\t" .
$row['price'] . "\n";

$already_sent[$row['product_url']] = 1;
}

print $output;
exit;

?>

Runnning that code from the browser in it's current state provides this error:

1054 - Unknown column 'categories.parent_id' in 'on clause'

 

select manuf.manufacturers_name as manufacturer, prd.products_id AS id, '' AS stock, '' AS mpn, prd.products_id AS mpc, prdsc.products_name AS name, '' AS description, FORMAT(prd.products_price,2) AS price, prd.products_image, if(catdescparent2.categories_name!='', concat_ws( ' > ' , catdescparent2.categories_name , concat_ws( ' > ' , catdescparent.categories_name , catdesccurrent.categories_name )), concat_ws( ' > ' , catdescparent.categories_name , catdesccurrent.categories_name ) ) AS category FROM categories , products prd, products_description as prdsc, categories_description as catdesccurrent, products_to_categories as prdtocat left join categories_description as catdescparent on ( catdescparent.categories_id = categories.parent_id AND catdescparent.language_id = '1' ) left join categories as cat2 on ( cat2.categories_id = categories.parent_id ) left join categories_description as catdescparent2 on ( catdescparent2.categories_id = cat2.parent_id AND catdescparent2.language_id = '1' ) left join manufacturers as manuf on ( manuf.manufacturers_id = prd.manufacturers_id ) WHERE ( prd.products_id=prdsc.products_id AND prdsc.language_id = '1' ) AND prd.products_id=prdtocat.products_id AND prdtocat.categories_id=categories.categories_id AND ( catdesccurrent.categories_id = categories.categories_id AND catdesccurrent.language_id = '1' ) AND prd.products_status != 0

 

[TEP STOP]

Any ideas?

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...