aussiemum Posted December 19, 2005 Posted December 19, 2005 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.
Guest Posted December 19, 2005 Posted December 19, 2005 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.
aussiemum Posted December 19, 2005 Author Posted December 19, 2005 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?
Guest Posted December 19, 2005 Posted December 19, 2005 Use phpMyAdmin just browse the tables. The table names show on the left panel.
aussiemum Posted December 19, 2005 Author Posted December 19, 2005 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?
Guest Posted December 19, 2005 Posted December 19, 2005 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.
aussiemum Posted December 19, 2005 Author Posted December 19, 2005 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?
Guest Posted December 19, 2005 Posted December 19, 2005 you place it on the server and you run it. Pretty much like when you access a page with your browser.
aussiemum Posted December 20, 2005 Author Posted December 20, 2005 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:
aussiemum Posted December 20, 2005 Author Posted December 20, 2005 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?
Guest Posted December 20, 2005 Posted December 20, 2005 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?
aussiemum Posted December 20, 2005 Author Posted December 20, 2005 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
Guest Posted December 20, 2005 Posted December 20, 2005 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.
aussiemum Posted December 21, 2005 Author Posted December 21, 2005 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?
Guest Posted December 21, 2005 Posted December 21, 2005 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
aussiemum Posted December 21, 2005 Author Posted December 21, 2005 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
Guest Posted December 21, 2005 Posted December 21, 2005 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
aussiemum Posted December 21, 2005 Author Posted December 21, 2005 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:
aussiemum Posted December 21, 2005 Author Posted December 21, 2005 I just called the script from my browser & I got the following: merUrl price cat shortDesc
Guest Posted December 21, 2005 Posted December 21, 2005 ok so it works. I mean no errors but of course I don't know if the script does what you really want.
aussiemum Posted December 21, 2005 Author Posted December 21, 2005 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?
aussiemum Posted December 21, 2005 Author Posted December 21, 2005 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?
Recommended Posts
Archived
This topic is now archived and is closed to further replies.