Insert Into Second Table

justlukeyou · August 6, 2013

Hi,

I paid someone $30 to create the following Cron Job on Freelancer. However when I requested further work to be added he put up the prices to $50 as if he was trying to blackmail me.

All I am trying to do is to insert some of the code into a second table so I can sort it. Is it possible to do it with the following easily enough? Would I just use an else code on the first set of insert?

$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  category_name = '" .safe_string_escape(strpp($arr, '<awCat>', '</awCat>')) ."',  
					 merchant_category = '" .safe_string_escape(strpp($arr, '<mCat>', '</mCat>')) ."', 
					 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>') ."'");
		//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;
}
?>

Edited August 6, 2013 by justlukeyou

requinix · August 6, 2013

I paid someone $30 to create the following Cron Job on Freelancer. However when I requested further work to be added he put up the prices to $50 as if he was trying to blackmail me.

From his point of you, you told him to do some amount of work, agreed to a price, and then tried to get him to do more work than was agreed upon as if you were trying to con him. He's right for increasing the price.

Insert what code where to do what and why?

justlukeyou · August 6, 2013

Hi,

But the additional work is far less than the original work.

I've had a go at it. I can get the second insert into furniture_groups to only insert one line. But the first insert into productdbase inserts everything I need. Cant see why though.

		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(1) ."',  
					 short_name = '" .safe_string_escape(strpp($arr, '<mCat>', '</mCat>')) ."', 
				     id = '" .strpp($arr, '<pId>', '</pId>') ."'");	 
					 
		//echo mysql_error();
	}
}

requinix · August 6, 2013

Kinda vague. Is the first insert working properly while the second is not? What's this "one line" being inserted, and as opposed to what?

Put another way, if you hardcode some example values into the queries, what do the queries look like? And what's the value of $arr?

justlukeyou · August 6, 2013

Thanks requinix,

Getting there with it. I can insert the two columns into the second table. Now I need to filter which room each pirticular peice of furniture goes into. I have added a filter at the end but it is from another cron job which did work.

I think I need to change the $dbh but not sure what to.

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

?>

KevinM1 · August 6, 2013

@justlukeyou - I'll only say this once: whatever arrangement you make/made with a freelancer is none of our concern. We offer the Freelancing section as a courtesy, but there are no guarantees or warranties stated or implied with the service. So, long story short, do not come on here and essentially state that you were being blackmailed. For one, we're not the freelance police. Second, without knowing the nature of the arrangement you had, we're not going to allow you to potentially slander someone else.

If you have a problem with that person, take it up with them privately. We don't want he said/they said shit spreading onto the forum.

---

Regarding your coding problem, you have given us an incomplete look at your code, with only one reference to $dbh and without anything to hint at why you'd want to change it. It's like asking, "Why does my leg hurt?" and then showing us a picture of one of your toenails.

So, you need to show us RELEVANT code. The stuff that actually pertains to your particular problem. We don't operate by throwing a bunch of shit at the wall and hoping it magically works, so you'll need to read through what you have, pick out the part that's supposed to do what you want, and then do the following:

1. Explain IN CLEAR ENGLISH what you're trying to do. We're not in the room with you, nor are we telepathic. We don't keep track of the projects our members work on, so you need to explain everything as though you're describing it for the first time. Because to us, you are.

2. Explain IN CLEAR ENGLISH what's actually happening. Including all errors and warnings.

3. Give us the RELEVANT code so we can compare that with both your goals and reality.

jazzman1 · August 6, 2013

Kevin, I am going to bookmark this reply.

You have a gift to be a teacher in a college

What are you still doing here? I like that!

justlukeyou · August 7, 2013

Thanks Kevin,

I used another site but the guy did try to sting me. Its a like a garage changing 3 tyres for $300 but then offering $150 to do the fourth tyre.

Luckily though I have been able to make some of the additions. The final thing I need to is to read the $long_name and select keywords and insert another keywork into $room

For example "coffee table" from $long_name is inserted into $room as "living room".

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

?>



    Mark Solved
    Quote
    MultiQuote
    Report

justlukeyou · August 8, 2013

Any suggestions please?

The problem I have is this part here:

$dbh->query

KevinM1 · August 8, 2013

Any suggestions please?

The problem I have is this part here:
$dbh->query

I have no idea what the problem is. Is the query not working? Are you not able to pass data to it?

...

Okay, here's what you need to do for us to try to give you a helpful answer:

1. Post the database schema for the tables you're trying to interact with

2. Describe EXACTLY what data you're trying to insert, update, or retrieve

3. Show us ALL of the code that's used to handle that process. That means MORE than just the query

4. Explain what "it's not working" means. Any errors (including SQL errors) or warnings or not expected results

If this sounds familiar, it's because it's what we ALWAYS ask for. You have (right now) 917 posts. You've been here long enough to know how this place works. The quality of the answers we give are directly proportional to the quality of the questions asked. If you want help finding the solution, you need to ask better questions.

justlukeyou · August 8, 2013

Hi,

If $long_name is "coffee table" the above code is supposed to insert "living room" into $room.

There are no error messages. I have had this working before but I have tried to streamline the rest of the cron job and now I cant get this to work.

This is ALL the code I currently have. 95% of it works. Just need to finish the final part off. Like I say I have had it working before, its incredibly frustrating that I cant get it to work now.

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

?>

justlukeyou · August 9, 2013

Any suggestions please.

I cant really see how I can explain it. The concept seems quite simple. Im just looking to read the keyword from one column and insert a different keyword into a different column.

boompa · August 9, 2013

Perhaps you should try checking your query's CASE syntax against the documentation

justlukeyou · August 10, 2013

Perhaps you should try checking your query's CASE syntax against the documentation

Thanks,

I didn't write the code and I dont understand the code enough. Is there in pirticular wrong with it?

I have had the code which inserts a key working before but I cant get it working again now.

justlukeyou · August 11, 2013

Hi,

Any suggestions please. As far I can see it is this part which I need to fix as the code up until works fine.

$dbh->query

I have tried using something like this but I am not using a string as a query. Can anyone suggest how I tie the code which inserts the which inserts the data into the table with the code which identifies the keyword and inserts a different keyword into $room.

$mysql_query

justlukeyou · August 12, 2013

Hi,

I placed another advert at people are quoting me $80 but I paid $50 to have 98% of the code done. Im now trying to reuse code I had working before. This is ridiculous.

I tried changing it to the following but couldn't get that to work either. This sort of thing kills me:

$fp->query('UPDATE `furniture_groups` SET `room` = CASE
WHEN `long_name` LIKE \'%kitchen%\' THEN \'Kitchen\'
WHEN `long_name` LIKE \'%coffee%\'  THEN \'Living-room\'

Edited August 12, 2013 by justlukeyou

justlukeyou · August 12, 2013

Hi,

Does no one understand what I'm trying to do?

I think its pretty simple!

KevinM1 · August 13, 2013

Look at what I wrote in post #10. Instead of saying, "Thanks," and then NOT doing any of the things I told you to do, you should actually do them.

justlukeyou · August 13, 2013

Hi,

Unfortunately it is not creating any error messages.

This is what I'm trying to aichieve. Lets say I have a spreadsheet and in column K I insert "coffee table". I have a piece of code which reads the insert, identifies the keyword "Coffee Table" and inserts "Living Room" into into column L. Because a coffee table is based in a living room. Altermatively if "mattress" is entered into column K, "bedroom" is entered into column L.

Its very frustrating as I have had this element of the working before.

Here is ALL the code I have:

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

?>

KevinM1 · August 13, 2013

1. Are you sure that 'coffee table' is a part of the .xml file you're reading from?

2. Are you sure that all of the data in the INSERT that's working is being inserted correctly? In the right format, in the right columns?

3. Have you attempted to run your UPDATE query in phpMyAdmin? If not, do so.

justlukeyou · August 13, 2013

1. Are you sure that 'coffee table' is a part of the .xml file you're reading from?

2. Are you sure that all of the data in the INSERT that's working is being inserted correctly? In the right format, in the right columns?

3. Have you attempted to run your UPDATE query in phpMyAdmin? If not, do so.

Hi,

1 and 2 are fine. How do I do Number 3.

I am also trying run the code on its own by inserting everything into furniture groups and then running the update.

$dbh->query('UPDATE `furniture_groups` SET `room` = CASE
				 WHEN `long_name` LIKE \'%kitchen%\' THEN \'Kitchen\'
				 WHEN `long_name` LIKE \'%coffee%\'  THEN \'Living-room\'

KevinM1 · August 13, 2013

For #3, how do you normally test that your queries are working?

Here's the thing: you never, ever, ever want to just throw stuff up on your production environment. If you're doing anything non-trivial, you need to have a development environment. If you're on Windows, get WampServer (http://www.wampserver.com/en/), if you're on Mac or *nix, you can get what you need through a package manager.

Regardless of what platform you're on (Windows, Mac, *nix), you need to have a local environment that you can do work on. It's more efficient, it allows you to tinker without worrying about killing the actual site, etc.

phpMyAdmin is a web-based application that allows you to create/edit/drop tables, run queries, import/export data, etc. If you've ever had to do anything with a database on shared hosting, chances are you did it through phpMyAdmin.

Since it's common for queries to simply fail without PHP echoing out the error, it's always a good idea to run them through phpMyAdmin to see if you have a syntax error. Beyond that, it's a good way to check that your queries are actually executing correctly (my questions 1 and 2). Just because something is being inserted, that doesn't mean the correct thing is being inserted, or that it's being inserted the right way.

justlukeyou · August 13, 2013

How will I know the wrong thing is being inserted in the wrong way as I will just be echoing what is in my database regardless of whether I run a query in PHPMyAdmin or echo it to a page?

Im confused as to how a query run in MyAdmin is different to a query run on the page.

justlukeyou · August 13, 2013

Hi,

I have to tried to isolate the code I am trying to add on it keeps coming back with the same problem.

It is now creating an error saying it is a non-object.

I need away of declaring this part:


	$dbh->commit();

	$dbh->query('UPDATE `furniture_groups` SET `room` = CASE

mysql_connect($host, $username, $password);
mysql_select_db($database);


$query = mysql_query("SELECT * FROM furniture_groups ");
while($row = mysql_fetch_array($query)) {


echo $row['long_name']; 

	$dbh->commit();

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

}
?>

KevinM1 · August 13, 2013

It's different because, like I said, it will actually give you the errors and it will allow you to simply write queries without having to modify code in a page. It's more efficient and more immediate.

Sign In

Insert Into Second Table

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information