Bug Fixing Cron Job

PHP
1

Hi,

I have a Cron Job which partially works. However whatever method I try to add or change columns stops the code from working.

Can anyone advise how I bug fix or make changes to the code. Whather I try doesn’t work.

I have 13 question marks but whenever I add a remove a field and quetion mark it stops working.

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

	

	// -----------------------------------------------------------
	// Loop through the XML structure
	// ----
	
	$dbh->beginTransaction();
	
	$m = 1;
	foreach($xml->merchant as $merchant) {
		$attributes = $merchant->attributes();
	
		$merchant_id   = (string)$attributes->id;
		$merchant_name = (string)$attributes->merchant;
		$merchant_name = (string)$attributes->linkname;
		
		//echo ($m > 1 ? '<br>' : ''), 'New merchant: ', $merchant_name, ' (ID: ', $merchant_id, ')<br>';
		
		foreach($merchant->prod as $product) {
			$product_id 			  = (string)$product->attributes()->id;
			$product_name 			  = (string)$product->text->name;
			$link_name 			  = strtolower(str_replace(array(' ', '_'), '-', preg_replace('#[^A-Za-z0-9 \\-_]#', '', $product_name)));
			$product_link 		 	  = (string)$product->uri->awTrack;
			$product_description 	 	  = (string)$product->text->desc;
			//$furniture_type 		  = (string)$product->cat->awCat;
			$furniture_group 		  = (string)$product->cat->mCat;
			$image_link 		 	  = (string)$product->uri->awImage;
			$merchant 		  	 	  = (string)$product->uri->merchant_name;
			$recommended_retail_price = (string)$product->price->buynow;
			$sell_price 			  = (string)$product->price->buynow;
			$price 			  = (string)$product->price->rrp;
            $retailprice 			  = (string)$product->price->rrp;
		
			$discount = ($recommended_retail_price * $sell_price > 0 ? round((1 - $sell_price / $recommended_retail_price) * 100) : 0);
		


		
			// echo str_repeat(' ', 10), $product_id, '<br>';
			// echo str_repeat(' ', 17), $product_name, '<br>';
			// echo str_repeat(' ', 17), $product_link, '<br>';
			// echo str_repeat(' ', 17), str_replace('    ', '<br>' . str_repeat(' ', 17), $product_description), '<br>';
			// echo str_repeat(' ', 17), $furniture_type, '<br>';
			// echo str_repeat(' ', 17), $product_category, '<br>';
			// echo str_repeat(' ', 17), $image_link, '<br>';
			// echo str_repeat(' ', 17), $recommended_retail_price, '<br>';
			// echo str_repeat(' ', 17), $sell_price, '<br>';
			
			try {
				$sth->execute(array(
									$product_id,
									$product_link,
									$product_name,
									$link_name,
									$product_description,
									$image_link,
									$recommended_retail_price,
									$sell_price,
									$discount,
									$merchant_name,
									$furniture_group,
									
									$product_link,
									$product_description,
									$recommended_retail_price,
									$sell_price,
									$discount,
									$furniture_group
2

I hate the “?” method. You have 17 marks in the first SQL - I don’t know how you can see just 13 :slight_smile:
However, use aliases so it’s all way clear:

<?php

$sth = $dbh->prepare('INSERT INTO `productdbase`
( `product_id`, `link`, `name`,
`linkname`, `fulldescription`, `image_link`,
`rrp`, `sell_price`, `discount`,
`merchant`, `furniture_group`)
VALUES
( :product_id, :link, :name,
.... , :furniture_group )
ON DUPLICATE KEY UPDATE
`link` = :link1 ,
`fulldescription` = :fulldescription1 ,
`rrp` = :rrp1 ,
`sell_price` = :sell_price ,
`discount` = :discount1 ,
`furniture_group` = :furniture_group1
');

// ...

try {
    $sth->execute(array(
        ':product_id' => 12,
        ':link' => 'some link',
        // ...
3

Brilliant thanks,

Do I need to delate the values on the right somehow?

fulldescription` = :fulldescription1 ,
`rrp` = :rrp1 ,
`sell_price` = :sell_price ,
`discount` = :discount1 ,
`furniture_group` = :furniture_group1
4

Normally, it’s not a rule.
I do it, just to be safe.

5

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

First: What’s the error?
Second, you have: VALUES ( ... ) but without ON DUPLICATE KEY UPDATE ...
Paste the entire code so maybe you omit a simple syntax error.

The prepared statement idea it’s way better because it protects you by injections.
So, it’s better to use the first code, with all :alias changes.

7

Many thanks,

I need to remove the top set of values with the question marks and replace them with the declared statements. However whather set of syntax I use it creates an error. I’ve got a bit lost to it to be honest.

Is there a rule to it that I should be following?

$sth = $dbh->prepare('INSERT INTO `productdbase`
						(`product_id`, `link`, `name`, `linkname`, `fulldescription`, `image_link`, `rrp`, `sell_price`, `discount`, `merchant`, `furniture_group`)
						  VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)



VALUES ('".$id."', '".$category_name."', '".$merchant_category."', '".$image."', '".$link."', '".$description."', '".$fulldescription."', '".$sellprice."', '".round($discount)."', '".$merchant."')");






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

	

	// -----------------------------------------------------------
	// Loop through the XML structure
	// ----
	
	$dbh->beginTransaction();
	
	$m = 1;
	foreach($xml->merchant as $merchant) {
		$attributes = $merchant->attributes();
	
		$merchant_id   = (string)$attributes->id;
		$merchant_name = (string)$attributes->merchant;
		$merchant_name = (string)$attributes->linkname;
		
		//echo ($m > 1 ? '<br>' : ''), 'New merchant: ', $merchant_name, ' (ID: ', $merchant_id, ')<br>';
		
		foreach($merchant->prod as $product) {
			$product_id 			  = (string)$product->attributes()->id;
			$product_name 			  = (string)$product->text->name;
			$link_name 			  = strtolower(str_replace(array(' ', '_'), '-', preg_replace('#[^A-Za-z0-9 \\-_]#', '', $product_name)));
			$product_link 		 	  = (string)$product->uri->awTrack;
			$product_description 	 	  = (string)$product->text->desc;
			//$furniture_type 		  = (string)$product->cat->awCat;
			$furniture_group 		  = (string)$product->cat->mCat;
			$image_link 		 	  = (string)$product->uri->awImage;
			$merchant 		  	 	  = (string)$product->uri->merchant_name;
			$recommended_retail_price = (string)$product->price->buynow;
			$sell_price 			  = (string)$product->price->buynow;
			$price 			  = (string)$product->price->rrp;
            $retailprice 			  = (string)$product->price->rrp;
		
			$discount = ($recommended_retail_price * $sell_price > 0 ? round((1 - $sell_price / $recommended_retail_price) * 100) : 0);
		


		
			// echo str_repeat(' ', 10), $product_id, '<br>';
			// echo str_repeat(' ', 17), $product_name, '<br>';
			// echo str_repeat(' ', 17), $product_link, '<br>';
			// echo str_repeat(' ', 17), str_replace('    ', '<br>' . str_repeat(' ', 17), $product_description), '<br>';
			// echo str_repeat(' ', 17), $furniture_type, '<br>';
			// echo str_repeat(' ', 17), $product_category, '<br>';
			// echo str_repeat(' ', 17), $image_link, '<br>';
			// echo str_repeat(' ', 17), $recommended_retail_price, '<br>';
			// echo str_repeat(' ', 17), $sell_price, '<br>';
			
			try {
				$sth->execute(array(
									$product_id,
									$product_link,
									$product_name,
									$link_name,
									$product_description,
									$image_link,
									$recommended_retail_price,
									$sell_price,
									$discount,
									$merchant_name,
									$furniture_group,
									
									$product_link,
									$product_description,
									$recommended_retail_price,
									$sell_price,
									$discount,
									$furniture_group
8

Hi,

Does anyone have any suggestions please.

I am stuck on declaring the statements. Which ever syntax I use it creates an error based on the syntax.

9

If this is the code, you have a big syntax error. Use a smart editor, like netbeans and you’ll see that your quotes are without logic.
First of all, this is not valid for MySql.

  1. You have VALUES(?, ?, ?,... and again VALUES ('".$id."', '"....
  2. After the second VALUES ... $merchant."')"); you closed your quotes and what’s next should be a string. But it’s not. ON DUPLICATE KEY UPDATE .... ?');
    Long story short, your code is a big mess :slight_smile: use a better editor and you’ll see everything way clear.
10

Many thanks,

I have followed your advise and cleared it up alot. However I am stuck on adding new variables.

For example I am trying to add this so I can use it within links. However when I try to at it to the insert and update as $linkname it says I have in balance of variables.


$link_name 			  = strtolower(str_replace(array(' ', '_'), '-', preg_replace('#[^A-Za-z0-9 \\-_]#', '', $product_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;
        }
    }
}