SitePoint Sponsor

User Tag List

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

    Identifier Why Item Wont Insert? - Very Confusing

    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?


    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 />";
    		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. #2
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,408
    Mentioned
    45 Post(s)
    Tagged
    12 Thread(s)
    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 SimpleXML 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 mysqli or PDO 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. #3
    SitePoint Evangelist
    Join Date
    Aug 2006
    Location
    Nantwich, Cheshire
    Posts
    413
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    http://www.firenza.net - my homage to a car from the 1970s

  4. #4
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    731
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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/1...nique-in-mysql

  5. #5
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    731
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,408
    Mentioned
    45 Post(s)
    Tagged
    12 Thread(s)
    You need to find the name of the index first:
    Code MySQL:
    SHOW INDEX FROM furniture_groups;

    Then you should be able to drop it:
    Code MySQL:
    DROP INDEX index_name ON furniture_groups;

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


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
  •