justlukeyou Posted June 30, 2013 Share Posted June 30, 2013 Hi, I have a cron job file. When I run it returns a message that the items have been inserted however nothing is inserting into the database. Does anyone have any suggestions why it would return this message but not insert into the database and how to fix it please? mysql_connect($host, $username, $password); mysql_select_db($database); // Make it PHP4 compatible. if(!function_exists("file_get_contents")) { function file_get_contents($filename) { $c = curl_init(); curl_setopt($c, CURLOPT_RETURNTRANSFER, 1); curl_setopt($c, CURLOPT_URL, $filename); $contents = curl_exec($c); curl_close($c); if($contents) { return $contents; } else { return curl_error($c); } return false; } } if(!function_exists("file_put_contents")) { function file_put_contents($filename, $data) { $f = @fopen($filename, "w"); if(!$f) { return false; } else { $bytes = fwrite($f, $data); fclose($f); return $bytes; } } } // Unzip the files. //require_once("dunzip2.class.php"); //$url = "http://datafeed.api.productserve.com/datafeed/download/apikey/ca79e5c05777a469065377372c824696/cid/424,451,448,453,449,452,450/mid/495,1829/columns/merchant_id,merchant_name,aw_product_id,merchant_product_id,product_name,description,category_id,category_name,merchant_category,aw_deep_link,aw_image_url,search_price,delivery_cost,merchant_deep_link,merchant_image_url,rrp_price/format/xml/compression/zip/"; $url = "http://datafeed.api.productserve.com/datafeed/download/apikey/ca79e5c05777a469065377372c824696/cid/424,451,448,453,449,452,450/mid/495,1829/columns/merchant_id,merchant_name,aw_product_id,merchant_product_id,product_name,description,category_id,category_name,merchant_category,aw_deep_link,aw_image_url,search_price,delivery_cost,merchant_deep_link,merchant_image_url,rrp_price/format/xml/compression/zip/"; $contents = file_get_contents($url); file_put_contents("datafeed_98057.xml.zip", $contents); //$zip = new dUnzip2("datafeed_98057.xml.zip"); //$zip->getList(); //$zip->unzipAll(); system("unzip -qq datafeed_98057.xml.zip"); // Fetch the feed and fix the broken end-tags. $filename = "datafeed_98057.xml"; $feed = file_get_contents($filename); // Start up the XML Reader. $xmlReader = new XMLReader(); $xmlReader->open($filename); // Loop through the file, and do all the extraction. while($xmlReader->read()) { if($xmlReader->nodeType == XmlReader::END_ELEMENT) { } else { switch($xmlReader->name) { case "merchant": echo "New merchant: " . $xmlReader->getAttribute("name") . "<br />"; $merchant = mysql_real_escape_string($xmlReader->getAttribute("name")); break; case "prod": $dom = new DOMDocument(); $domNode = $xmlReader->expand(); $element = $dom->appendChild($domNode); $domString = utf8_encode($dom->saveXML($element)); if(trim($domString) != "") { $prod = new SimpleXMLElement($domString); $id = $prod->attributes(); $id = $id['id']; $link = $prod->uri->mLink; $description = mysql_real_escape_string($prod->text->name); $fulldescription = mysql_real_escape_string($prod->text->desc); $category_name = $prod->uri->category_name; $merchant_category = $prod->uri->merchant_category; $image = $prod->uri->awImage; $retailprice = $prod->price->rrp; $sellprice = $prod->price->buynow; if($id) { if($sellprice > 0 && $retailprice > 0) { $discount = 100 - ($sellprice / $retailprice * 100); } else { $discount = 0; } echo "New product: #" . $id . " - " . $category_name . " . $merchant_category ." . $description . " - Discount: " . $discount . "%" . (($discount > 90.01 || $discount < 0) ? " (Not inserted)" : " (Inserted)") . "<br />" . $fulldescription . "<br /><br />"; if($discount > 0 && $discount < 90.01) { $query = mysql_query("SELECT * FROM productdbasetest WHERE id = '".$id."'"); if(mysql_num_rows($query) > 0) { $query = mysql_query("UPDATE productdbasetest SET category_name = '".$category_name."', merchant_category = '".$merchant_category."', awImage = '".$image."', link = '".$link."', description = '".$description."', fulldescription = '".$fulldescription."', price = '".$sellprice."', discount = '".round($discount)."', merchant = '".$merchant."' WHERE id = '".$id."'"); } else { $query = mysql_query("INSERT INTO productdbasetest (id, category_name, merchant_category, awImage, link, description, fulldescription, price, discount, merchant) VALUES ('".$id."', '".$category_name."', '".$merchant_category."', '".$image."', '".$link."', '".$description."', '".$fulldescription."', '".$sellprice."', '".round($discount)."', '".$merchant."')"); } if($query) { echo $id . " has been inserted.<br />"; } else { echo $id . " could not be inserted because: " . mysql_error() . ".<br />"; } } } else { echo "Could not retrieve the product information.<br />"; } } break; } } } // Close the XML Reader and free memory. $xmlReader->close(); unset($feed); unlink("datafeed_98057.xml"); unlink("datafeed_98057.xml.zip"); ?> Quote Link to comment https://forums.phpfreaks.com/topic/279729-cron-job-returns-message-but-no-insert/ Share on other sites More sharing options...
justlukeyou Posted July 1, 2013 Author Share Posted July 1, 2013 Hi, Can anyone advise how I can progress with this please? I have tried advertising the job on PeoplePerHourbut I get very basic questions through like "what code is it written in". I cant seem to find someone to pay to fix it! How difficult are Cron jobs to set up and use? Quote Link to comment https://forums.phpfreaks.com/topic/279729-cron-job-returns-message-but-no-insert/#findComment-1438875 Share on other sites More sharing options...
trq Posted July 1, 2013 Share Posted July 1, 2013 How difficult are Cron jobs to set up and use? Not at all. Your issue is not likely even cron related. What steps have you taken to debug your code? Any? Have you tried breaking the code down into parts to try and locate where the issue occurs? These are all basic programming skills. Learn them, or find a new hobby. Quote Link to comment https://forums.phpfreaks.com/topic/279729-cron-job-returns-message-but-no-insert/#findComment-1438880 Share on other sites More sharing options...
justlukeyou Posted July 1, 2013 Author Share Posted July 1, 2013 Thanks, programming isn't the hobby I want lol its the website that comes from it!! I did have a bash at it and I can now insert some of columns I want to insert which is a great step. Very pleased with that. However, I still cant actually understand how the code works. For example, the percentage of the discount inserts so it must be reading the RRP and the sell price. However the RRP and sell price are not entering into the table. Quote Link to comment https://forums.phpfreaks.com/topic/279729-cron-job-returns-message-but-no-insert/#findComment-1438884 Share on other sites More sharing options...
trq Posted July 2, 2013 Share Posted July 2, 2013 Thanks, programming isn't the hobby I want lol its the website that comes from it!! If you want the site to be any good, you probably need a programmer then. Anyway.. my questions above still stand. Quote Link to comment https://forums.phpfreaks.com/topic/279729-cron-job-returns-message-but-no-insert/#findComment-1438925 Share on other sites More sharing options...
justlukeyou Posted July 2, 2013 Author Share Posted July 2, 2013 Thanks once again. I am almost there with it. About half the data I need inserting. Is there a process I can use to see why some data is inserting and other data isn't? I'm confused as to why the discount calculation works but the values that enter into the calculation don't enter into the table. Quote Link to comment https://forums.phpfreaks.com/topic/279729-cron-job-returns-message-but-no-insert/#findComment-1438987 Share on other sites More sharing options...
justlukeyou Posted July 2, 2013 Author Share Posted July 2, 2013 Hi, I have spent another 4 hours on this non-stop but still cant get anywhere. Can anyone advise how this part works please. Whenever I try to add to it, it wont insert. I have 12 columns and 12 questions marks but I dont understand how to add another column. $sth = $dbh->prepare('INSERT INTO `productdbase` (`product_id`, `link`, `name`, `linkname`, `fulldescription`, `image_link`, `rrp`, `sell_price`, `discount`, `merchant`, `furniture_group`) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE `link` = ?, `fulldescription` = ?, `rrp` = ?, `sell_price` = ?, `discount` = ?, `furniture_group` = ?'); $sth_fg = $dbh->prepare('INSERT INTO `furniture_groups` (`long_name`, short_name) VALUES(?, ?)'); Quote Link to comment https://forums.phpfreaks.com/topic/279729-cron-job-returns-message-but-no-insert/#findComment-1439130 Share on other sites More sharing options...
justlukeyou Posted July 3, 2013 Author Share Posted July 3, 2013 Hi, Should I be giving the column names aliases? If so do I need to declare these somehow? fulldescription` = :fulldescription1 , `rrp` = :rrp1 , `sell_price` = :sell_price , `discount` = :discount1 , `furniture_group` = :furniture_group1 Quote Link to comment https://forums.phpfreaks.com/topic/279729-cron-job-returns-message-but-no-insert/#findComment-1439206 Share on other sites More sharing options...
justlukeyou Posted July 4, 2013 Author Share Posted July 4, 2013 Hi, I need to declare the values but whenever I try it creates a syntaz error. Is there any way to fix it it please? (`product_id`, `link`, `name`, `linkname`, `fulldescription`, `image_link`, `rrp`, `sell_price`,`discount`, `merchant`, `furniture_group`) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE `link` = ?, `fulldescription` = ?, `rrp` = ?, `sell_price` = ?, `discount` = ?, `furniture_group` = ?'); VALUES ('".$id."', '".$category_name."', '".$merchant_category."', '".$image."', '".$link."', '".$description."', '".$fulldescription."', '".$sellprice."', '".round($discount)."', '".$merchant."')"); Quote Link to comment https://forums.phpfreaks.com/topic/279729-cron-job-returns-message-but-no-insert/#findComment-1439455 Share on other sites More sharing options...
justlukeyou Posted July 8, 2013 Author Share Posted July 8, 2013 Hi, I have made alot of progress. I am now declaring the values but I am still struggling to understand how each areas correlates. Can anyone advise please? // Unzip the files. //require_once("dunzip2.class.php"); //$url = "http://datafeed.api.productserve.com/datafeed/download/apikey/ca79e5c05777a469065377372c824696/cid/424,451,448,453,449,452,450/mid/495,1829/columns/merchant_id,merchant_name,aw_product_id,merchant_product_id,product_name,description,category_id,category_name,merchant_category,aw_deep_link,aw_image_url,search_price,delivery_cost,merchant_deep_link,merchant_image_url,rrp_price/format/xml/compression/zip/"; $url = "http://datafeed.api.productserve.com/datafeed/download/apikey/ca79e5c05777a469065377372c824696/cid/424,451,448,453,449,452,450/mid/495,1829/columns/merchant_id,merchant_name,aw_product_id,merchant_product_id,product_name,description,category_id,category_name,merchant_category,aw_deep_link,aw_image_url,search_price,delivery_cost,merchant_deep_link,merchant_image_url,rrp_price/format/xml/compression/zip/"; $contents = file_get_contents($url); file_put_contents("datafeed_98057.xml.zip", $contents); //$zip = new dUnzip2("datafeed_98057.xml.zip"); //$zip->getList(); //$zip->unzipAll(); system("unzip -qq datafeed_98057.xml.zip"); // Fetch the feed and fix the broken end-tags. $filename = "datafeed_98057.xml"; $feed = file_get_contents($filename); // Start up the XML Reader. $xmlReader = new XMLReader(); $xmlReader->open($filename); // Loop through the file, and do all the extraction. while($xmlReader->read()) { if($xmlReader->nodeType == XmlReader::END_ELEMENT) { } else { switch($xmlReader->name) { case "merchant": echo "New merchant: " . $xmlReader->getAttribute("name") . "<br />"; $merchant = mysql_real_escape_string($xmlReader->getAttribute("name")); break; case "prod": $dom = new DOMDocument(); $domNode = $xmlReader->expand(); $element = $dom->appendChild($domNode); $domString = utf8_encode($dom->saveXML($element)); if(trim($domString) != "") { $prod = new SimpleXMLElement($domString); $id = $prod->attributes(); $id = $id['id']; $link = $prod->uri->awTrack; $producttitle = mysql_real_escape_string($prod->text->name); $fulldescription = mysql_real_escape_string($prod->text->desc); $category_name = $prod->uri->category_name; $merchant_category = $prod->uri->merchant_category; $image = $prod->uri->awImage; $productlink = (string)$product->uri->awTrack; $retailprice = $prod->price->rrp; $sellprice = $prod->price->buynow; if($id) { if($sellprice > 0 && $retailprice > 0) { $discount = 100 - ($sellprice / $retailprice * 100); } else { $discount = 0; } echo "New product: #" . $id . " - " . $category_name . " . $merchant_category ." . $producttitle . " - Discount: " . $discount . "%" . (($discount > 90.01 || $discount < 0) ? " (Not inserted)" : " (Inserted)") . "<br />" . $fulldescription . "<br /><br />"; if($discount > 0 && $discount < 90.01) { $query = mysql_query("SELECT * FROM productdbase WHERE id = '".$id."'"); if(mysql_num_rows($query) > 0) { $query = mysql_query("UPDATE productdbase SET category_name = '".$category_name."', merchant_category = '".$productlink."', image_link = '".$image."', link = '".$link."', name = '".$producttitle."', fulldescription = '".$fulldescription."', sell_price = '".$sellprice."', discount = '".round($discount)."', merchant = '".$merchant."' WHERE id = '".$id."'"); } else { $query = mysql_query("INSERT INTO productdbase (id, category_name, merchant_category, image_link, link, name, fulldescription, price, discount, merchant) VALUES ('".$id."', '".$category_name."', '".$merchant_category."', '".$image."', '".$link."', '".$producttitle."', '".$fulldescription."', '".$sellprice."', '".round($discount)."', '".$merchant."')"); } if($query) { echo $id . " has been inserted.<br />"; } else { echo $id . " could not be inserted because: " . mysql_error() . ".<br />"; } } } else { echo "Could not retrieve the product information.<br />"; } } break; } } } // Close the XML Reader and free memory. $xmlReader->close(); unset($feed); unlink("datafeed_98057.xml"); unlink("datafeed_98057.xml.zip"); ?> Quote Link to comment https://forums.phpfreaks.com/topic/279729-cron-job-returns-message-but-no-insert/#findComment-1439953 Share on other sites More sharing options...
justlukeyou Posted July 12, 2013 Author Share Posted July 12, 2013 Sorted it! Does anyone have any views on this code? Are there any improvements I can make? $prod = new SimpleXMLElement($domString); $id = $prod->attributes(); $id = $id['id']; $link = $prod->uri->awTrack; $producttitle = mysql_real_escape_string($prod->text->name); $fulldescription = mysql_real_escape_string($prod->text->desc); $category_name = $prod->uri->category_name; $product_name = mysql_real_escape_string($prod->text->name); $link_name = strtolower(str_replace(array(' ', '_'), '-', preg_replace('#[^A-Za-z0-9 \-_]#', '', $product_name))); $merchant_category = $prod->uri->merchant_category; $image = $prod->uri->awImage; $productlink = (string)$product->uri->awTrack; $retailprice = $prod->price->rrp; $sellprice = $prod->price->buynow; if($id) { if($sellprice > 0 && $retailprice > 0) { $discount = 100 - ($sellprice / $retailprice * 100); } else { $discount = 0; } echo "New product: #" . $id . " - " . $category_name . " . $merchant_category ." . $producttitle . " - Discount: " . $discount . "%" . (($discount > 90.01 || $discount < 0) ? " (Not inserted)" : " (Inserted)") . "<br />" . $link_name . "<br />" . $fulldescription . "<br /><br />"; if($discount > 0 && $discount < 90.01) { $query = mysql_query("SELECT * FROM productdbase WHERE id = '".$id."'"); if(mysql_num_rows($query) > 0) { $query = mysql_query("UPDATE productdbase SET category_name = '".$category_name."', merchant_category = '".$productlink."', image_link = '".$image."', link = '".$link."', name = '".$producttitle."', fulldescription = '".$fulldescription."', sell_price = '".$sellprice."', discount = '".round($discount)."', linkname = '".$link_name."', merchant = '".$merchant."' WHERE id = '".$id."'"); } else { $query = mysql_query("INSERT INTO productdbase (id, category_name, merchant_category, linkname, image_link, link, name, fulldescription, price, discount, merchant) VALUES ('".$id."', '".$category_name."', '".$merchant_category."', '".$link_name."', '".$image."', '".$link."', '". $producttitle."', '".$fulldescription."', '".$sellprice."', '".round($discount)."', '".$merchant."')"); } if($query) { echo $id . " has been inserted.<br />"; } else { echo $id . " could not be inserted because: " . mysql_error() . ".<br />"; } } } else { echo "Could not retrieve the product information.<br />"; } } break; Quote Link to comment https://forums.phpfreaks.com/topic/279729-cron-job-returns-message-but-no-insert/#findComment-1440471 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.