Cron Job Returns Message But No Insert

justlukeyou · 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");

?>

justlukeyou · 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?

trq · 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.

justlukeyou · 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.

trq · 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.

justlukeyou · 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.

justlukeyou · 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(?, ?)');

justlukeyou · 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

justlukeyou · 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."')");

justlukeyou · 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");

?>

justlukeyou · 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;

Sign In

Cron Job Returns Message But No Insert

Recommended Posts

justlukeyou

Link to comment

Share on other sites

justlukeyou

Link to comment

Share on other sites

trq

Link to comment

Share on other sites

justlukeyou

Link to comment

Share on other sites

trq

Link to comment

Share on other sites

justlukeyou

Link to comment

Share on other sites

justlukeyou

Link to comment

Share on other sites

justlukeyou

Link to comment

Share on other sites

justlukeyou

Link to comment

Share on other sites

justlukeyou

Link to comment

Share on other sites

justlukeyou

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information