SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    730
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Bug Fixing Cron Job

    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.


    Code:
    $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('&nbsp;', 10), $product_id, '<br>';
    			// echo str_repeat('&nbsp;', 17), $product_name, '<br>';
    			// echo str_repeat('&nbsp;', 17), $product_link, '<br>';
    			// echo str_repeat('&nbsp;', 17), str_replace('    ', '<br>' . str_repeat('&nbsp;', 17), $product_description), '<br>';
    			// echo str_repeat('&nbsp;', 17), $furniture_type, '<br>';
    			// echo str_repeat('&nbsp;', 17), $product_category, '<br>';
    			// echo str_repeat('&nbsp;', 17), $image_link, '<br>';
    			// echo str_repeat('&nbsp;', 17), $recommended_retail_price, '<br>';
    			// echo str_repeat('&nbsp;', 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. #2
    SitePoint Addict bronze trophy vectorialpx's Avatar
    Join Date
    Dec 2012
    Location
    Bucharest
    Posts
    219
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    I hate the "?" method. You have 17 marks in the first SQL - I don't know how you can see just 13
    However, use aliases so it's all way clear:

    PHP Code:
    <?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. #3
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    730
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Brilliant thanks,

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

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

  4. #4
    SitePoint Addict bronze trophy vectorialpx's Avatar
    Join Date
    Dec 2012
    Location
    Bucharest
    Posts
    219
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Normally, it's not a rule.
    I do it, just to be safe.

  5. #5
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    730
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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?

    Code:
    (`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` = ?');


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

  6. #6
    SitePoint Addict bronze trophy vectorialpx's Avatar
    Join Date
    Dec 2012
    Location
    Bucharest
    Posts
    219
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    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. #7
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    730
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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?


    Code:
    $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('&nbsp;', 10), $product_id, '<br>';
    			// echo str_repeat('&nbsp;', 17), $product_name, '<br>';
    			// echo str_repeat('&nbsp;', 17), $product_link, '<br>';
    			// echo str_repeat('&nbsp;', 17), str_replace('    ', '<br>' . str_repeat('&nbsp;', 17), $product_description), '<br>';
    			// echo str_repeat('&nbsp;', 17), $furniture_type, '<br>';
    			// echo str_repeat('&nbsp;', 17), $product_category, '<br>';
    			// echo str_repeat('&nbsp;', 17), $image_link, '<br>';
    			// echo str_repeat('&nbsp;', 17), $recommended_retail_price, '<br>';
    			// echo str_repeat('&nbsp;', 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. #8
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    730
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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. #9
    SitePoint Addict bronze trophy vectorialpx's Avatar
    Join Date
    Dec 2012
    Location
    Bucharest
    Posts
    219
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    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 use a better editor and you'll see everything way clear.

    Quote Originally Posted by justlukeyou View Post
    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?

    Code:
    $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

  10. #10
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    730
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code:
    $link_name 			  = strtolower(str_replace(array(' ', '_'), '-', preg_replace('#[^A-Za-z0-9 \-_]#', '', $product_name)));

    Code:
     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;
            }
        }
    }


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •