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?


{
	$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');

?>

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”.