Identifier Why Item Wont Insert? - Very Confusing

PHP
1

Hi,

I have the following code which should insert information into two tables “productdbase” and “furniture_groups”. However the code inserts more lines into productdbase than what it does into furniture_groups. For example products 1-10 will be inserted into productdbase but only 1,3,4,7 and 9 will be inserted into furniture_groups.

However there appears to be nothing different between lines 2 and 3 or 6 and 7.

Is it possible to create a code as to why something wont insert into a table?

	$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 />";
		echo strpp($arr, '<mCat>', '</mCat>') ."<br />";
		echo strpp($arr, '<awCat>', '</awCat>') ."<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',
					 merchant_category = '" .safe_string_escape(strpp($arr, '<mCat>', '</mCat>')) ."',
				     price = '" .safe_string_escape(strpp($arr, '<buynow>', '</buynow>')) ."',
					 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;
}
2

Is it possible that one or more of the values being inserted in the second query aren’t set in the source data for that row and so the query is failing?

I think your code would be simpler and easier to read (and therefore easier to debug) if you used PHP’s [fphp]SimpleXML[/fphp] functions to parse your XML file. The same goes for escaping data before inserting it into the DB. The less code you have to write yourself, the better the program.

You should also think about changing to the [fphp]mysqli[/fphp] or [fphp]PDO[/fphp] extension for your DB access, as the mysql_* functions are depreciated and are going to be removed from PHP. Either of those extensions would also give you the benefit of prepared statements/bound parameters which would help simplify your query building code.

3

Hard to say without seeing the data - for example is it possible that long_name and short_name are required fields in the database configuration but missing in some of the data records, or that id is duplicated and allowed in one table but not in the other? Is it always the same records that are missing from the second table?

This code is very familiar - another poster asked a different question about an indentical (except with a bit extra) section of code last week.

4

Hi,

Can anyone advise how to remove the unique key, I tried the following link but couldn’t get any of them to work:

http://stackoverflow.com/questions/1564924/how-to-drop-unique-in-mysql

5

I thought it would be one of the following but neither works. Any suggestions please?

DROP UNIQUE long_name ON furniture_groups;

alter table furniture_groups drop UNIQUE long_name;

6

You need to find the name of the index first:

SHOW INDEX FROM furniture_groups;

Then you should be able to drop it:

DROP INDEX index_name ON furniture_groups;

Edit: If you have access to PHPMyAdmin, it’s probably easier to use that.