SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Aug 2013
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Final Element of Code Not Working

    Hi,

    I have a cron job which installs data from datafeed into my table. however I am trying to add a piece of code which reads one column ($long_name) when the code identifies specific key words it inserts another key work into a second column ($room).

    For example if "coffee" is entered into $long_name "living room" is inserted into $room

    or

    If "mattr" is enterinted into $long_name "bedroom" is inserted into $room

    However I can get the two peices of code to work together. Any suggestions please?


    Code:
    {
    	$fp = $zip->getStream("datafeed_98057.xml"); //file inside archive
    	if(!$fp)
    		die("Error: can't get stream to zipped file");
    	$buf = ""; 
    	ob_start();
    	while (!feof($fp)) 
    		$buf .= fread($fp, 2048);
    	$s = ob_get_contents();
    	ob_end_clean();
    	if(stripos($s, "CRC error") != FALSE)
    		die('CRC32 mismatch');
    	fclose($fp);
    	$zip->close();
    	$ax = strocc($buf, '<prod', '</prod>');
    	
    	echo '<pre>';
    	for($i=0; $i<sizeof($ax); $i++)
    	{ 
    		$arr = $ax[$i];
    		echo strpp($arr, '<pId>', '</pId>') ."<br />";
    		$desc = '';
    		if(strpos($ax[$i], '<desc>')!==FALSE)
    		{
    			$desc = safe_string_escape(strpp($ax[$i], '<desc>', '</desc>'));
    		}
    		$discount = 0;
    		if(floatval(strpp($arr, '<rrp>', '</rrp>'))!=0)
    			$discount = 100 - (floatval(strpp($arr, '<buynow>', '</buynow>')) / floatval(strpp($arr, '<rrp>', '</rrp>')) * 100);
    		mysql_query("insert into productdbase SET  
    					 image_link = '" .safe_string_escape(strpp($arr, '<awImage>', '</awImage>')) ."', 
    				     link = '" .safe_string_escape(strpp($arr, '<awTrack>', '</awTrack>')) ."', 
    					 name = '" .safe_string_escape(strpp($arr, '<name>', '</name>')) ."', 
    					 linkname = '" .str_replace("---", "-", str_replace(" ", "-", safe_string_escape(strpp($arr, '<name>', '</name>')))) ."', 
    					 fulldescription = '$desc', 
    				     price = '" .safe_string_escape(strpp($arr, '<rrp>', '</rrp>')) ."', 
    					 discount = '$discount', 
    					 merchant = '" .safe_string_escape(strpp($arr, 'name="', '"')) ."', 
    					 promotional_text = '$desc', 
    				     id = '" .strpp($arr, '<pId>', '</pId>') ."'");
    					 
    			
    		mysql_query("insert into furniture_groups SET  
    					 long_name = '" .safe_string_escape(strpp($arr, '<mCat>', '</mCat>')) ."', 
    					 short_name = '" .safe_string_escape(strpp($arr, '<awCat>', '</awCat>')) ."', 
    				     id = '" .strpp($arr, '<pId>', '</pId>') ."'");	 
    					 
    		//echo mysql_error();
    	}
    }
    else
    	echo "zip not found";
    unlink($file);
    
    
    
    
    function strocc($str, $pos1, $pos2)
    {
    	$pos=0;
    	$occ=array();
    	while(strpos($str, $pos1, $pos) && strpos($str, $pos2, strpos($str, $pos1, $pos)))
    	{
    		array_push($occ, strpp($str, $pos1, $pos2, $pos));
    		$pos = strpos($str, $pos2, strpos($str, $pos1, $pos));
    	}
    	return ($occ);
    } 
    
    function strpp($str, $pos1, $pos2, $startoffset=0)
    {
    	return substr($str, (strpos($str, $pos1, $startoffset)+strlen($pos1)), strpos($str, $pos2, strpos($str, $pos1, $startoffset)+strlen($pos1))-(strpos($str, $pos1, $startoffset)+strlen($pos1)));
    }
    
    function mysqlinit($user, $pass, $db, $host='localhost')
    {
    	$link = mysql_connect($host, $user, $pass);
    	if (!$link)
    	{
    		echo('Cant connect to MySQL : ' .mysql_error());
    		return 0;
    	}
    	$db_selected = mysql_select_db($db, $link);
    	if (!$db_selected)
    	{
    		echo('Cant use database ' .$db .': ' .mysql_error());
    		return 0;
    	}
    	return 1;
    }
    
    
    function safe_string_escape($str)
    {
    	$len=strlen($str);
    	$escapeCount=0;
    	$targetString='';
    	for($offset=0;$offset<$len;$offset++) {
    		switch($c=$str{$offset}) {
    			case "'":
    				if($escapeCount % 2 == 0) $targetString.="\\";
    				$escapeCount=0;
    				$targetString.=$c;
    				break;
    			case '"':
    				if($escapeCount % 2 == 0) $targetString.="\\";
    				$escapeCount=0;
    				$targetString.=$c;
    				break;
    			case '\\':
    				$escapeCount++;
    				$targetString.=$c; 
    				break;
    			default:
    				$escapeCount=0;
    				$targetString.=$c;
    		}
    	}
    	return $targetString;
    }
    
    
    
    $dbh->query('UPDATE `furniture_groups` SET `room` = CASE
    				 WHEN `long_name` LIKE \'%kitchen%\' THEN \'Kitchen\'
    				 WHEN `long_name` LIKE \'%coffee%\'  THEN \'Living-room\'
    				 WHEN `long_name` LIKE \'%living%\'  THEN \'Living-room\'
    				 WHEN `long_name` LIKE \'%sofa%\' 	 THEN \'Living-room\'
    				 WHEN `long_name` LIKE \'%otto%\' 	 THEN \'Living-room\'
    				 WHEN `long_name` LIKE \'%tv%\' 	 THEN \'Living-room\'
    				 WHEN `long_name` LIKE \'%cd%\' 	 THEN \'Living-room\'
    				 WHEN `long_name` LIKE \'%lounger%\' 	 THEN \'Living-room\'
    				 WHEN `long_name` LIKE \'%mirror%\' 	 THEN \'Living-room\'
    				 WHEN `long_name` LIKE \'%sideboard%\' 	 THEN \'Living-room\'
    				 WHEN `long_name` LIKE \'%lounge%\' 	 THEN \'Living-room\'
    				 WHEN `long_name` LIKE \'%nest of table%\' 	 THEN \'Living-room\'
    				 WHEN `long_name` LIKE \'%entertainment%\' 	 THEN \'Living-room\'
    				 WHEN `long_name` LIKE \'%bed%\' 	 THEN \'Bedroom\'
    				 WHEN `long_name` LIKE \'%mattr%\' 	 THEN \'Bedroom\'
    				 WHEN `long_name` LIKE \'%stool%\' 	 THEN \'Bedroom\'
    				 WHEN `long_name` LIKE \'%wardrobe%\' 	 THEN \'Bedroom\'
    				 WHEN `long_name` LIKE \'%chest%\' 	 THEN \'Bedroom\'
    				 WHEN `long_name` LIKE \'%blanket box%\' 	 THEN \'Bedroom\'
    				 WHEN `long_name` LIKE \'%headboard%\' 	 THEN \'Bedroom\'
    				 WHEN `long_name` LIKE \'%futon%\' 	 THEN \'Bedroom\'
    				 WHEN `long_name` LIKE \'%hall%\' 	 THEN \'Hall\'
    				 WHEN `long_name` LIKE \'%bath%\' 	 THEN \'Bathroom\'
    				 WHEN `long_name` LIKE \'%dressing%\' 	 THEN \'Bedroom\'
    				 WHEN `long_name` LIKE \'%office%\' 	 THEN \'Office\'
    				 WHEN `long_name` LIKE \'%bookcase%\' 	 THEN \'Office\'
    				 WHEN `long_name` LIKE \'%filing%\' 	 THEN \'Office\'
    				 WHEN `long_name` LIKE \'%computer desk%\' 	 THEN \'Office\'
    				 WHEN `long_name` LIKE \'%extending%\' 	 THEN \'Dining-Room\'
    				 WHEN `long_name` LIKE \'%extendable%\'  THEN \'Dining-Room\'
    				 WHEN `long_name` LIKE \'%dining%\' 	 THEN \'Dining-Room\'
    				 WHEN `long_name` LIKE \'%wine rack%\' 	 THEN \'Dining-Room\'
    				 WHEN `long_name` LIKE \'%cushion%\' 	 THEN \'Home-Furnishings\'
    				 WHEN `long_name` LIKE \'%rug%\' 	 THEN \'Home-Furnishings\'
    				 WHEN `long_name` LIKE \'%vase%\' 	 THEN \'Home-Furnishings\'
    				 WHEN `long_name` LIKE \'%lamp%\' 	 THEN \'Home-Furnishings\'
    				 WHEN `long_name` LIKE \'%clock%\' 	 THEN \'Home-Furnishings\'
    				 ELSE `room`
    				 END');
    
    ?>

  2. #2
    SitePoint Addict
    Join Date
    Aug 2006
    Location
    Nantwich, Cheshire
    Posts
    361
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    It's a bit difficult to tell what you mean without more information on the tables involved. It looks as if you're updating two separate tables - productdbase and furniture_groups, and based on the $long_name variable you want to update a third column in furniture_groups. If that's the case, I'd modify the final bit of code so that instead of it being a separate database update, make it a function that returns the contents of $room when you pass in $long_name, call it just before you call the mysql_query to insert into furniture_groups, and just set $room there.

    That said, I'd also want that relationship in a separate table rather than hard-coded like that, and once it's in a table then it would not be normal to store it in furniture_groups, just join it when necessary.

    But I'm no PHP expert, and you might get more replies posting in the PHP section rather than "Web Design".
    http://www.firenza.net - my homage to a car from the 1970s


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
  •