Page 1 of 1

Newlines breaking up database text

Posted: Thu Aug 10, 2006 3:56 pm
by jlevinson
OK, this is a bit of a long one, so bear with me.

I'm developing this e-commerce site, and it interacts with a Microsoft RMS Web Integrator. Essentially, the Web Integrator (WI) shoots out a SOAP header and I take the XML, parse it for products, and dump them in the database. Easy enough, right?

On my development laptop (WinXP, PHP5, MySQL 4.1), it works great. Perfect.

On our testing server, however, it doesn't. The problem is a field called "longdescription," and in the MySQL db, it's a text field. It includes an extended description. The problem is, anytime I grab the data out of that field, only the very last line after a newline makes it into the database. So if I hada field which said...

"Now comes in five crazy colors!

No batteries included!"

Only the 2nd line makes it in there, unlike my own machine, where it works fine. I tried to use stripslashes($data) before I put it in... but for the sake of argument, let me know you how it works...

Code: Select all

function tag_contents($parser, $data) {
     global $current, $xmlresponse, $description, $itemcode, $quantity, $price, $weight, $smallimage, $longdescription, $taxtype, $category;
     switch ($current)
     {
	 	case "ITEMCODE":
			$fp = fopen('/tmp/rmsresponse.xml', "a", 0);
	        fputs($fp, $data);
	        fputs($fp, '"><Error Code="');
	        $xmlresponse .= $data.'"><Error Code="';
	        fclose($fp);
	        $itemcode = addslashes($data);
			break;
		case "DESCRIPTION":
			$description = addslashes($data);
			break;
		case "PRICE":
			$price = addslashes($data);
			$break;
		case "QUANTITY":
			$quantity = addslashes($data);
			break;
		case "WEIGHT":
			$weight = addslashes($data);
			break;
		/* case "SMALLIMAGE":
			if (isset($smallimage)) {
				$smallimage = addslashes($data); }
			else {
				$smallimage = addslashes("nopic.gif"); }
			break; */
		case "LONGDESCRIPTION":
			$longdescription = addslashes($data);
			break;
		case "TAXTYPE":
			if ($data == "Sales Tax")
				$taxtype = 1;
			else
				$taxtype = 0;
			break;	   
	 }	 
   }
It's the "LONGDESCRIPTION" that we need to concern ourselves with. I did, at one put, put an "echo" statement of $data here, just to make SURE that I had the full description, still, which I did. So that's not the issue.

After that, it gets dumped into the DB:

Code: Select all

if ($numrows > 0)
	       	 {
				
				$query = sprintf("UPDATE products SET description = '%s', quantity = '%s', price = '%s', weight = '%s', longdescription = '%s', taxable = '%s', category_id = '%s' WHERE itemcode = '%s'",
				$description,
				$quantity,
				$price,
				$weight,
				$longdescription,
				$taxtype,
				$category_id,
				$itemcode);
				@mysql_query($query);
				fputs($fp, '0">Product Updated');
	         $xmlresponse .= '0">Product Updated"';	  
			}
			else
			{
			  $query = "INSERT INTO products (description, quantity, price, weight, longdescription, taxable, category_id, itemcode) VALUES ('".$description."', '".$quantity."', '".$price."', '".$weight."', '".$longdescription."', '".$taxtype."', '".$category_id."', '".$itemcode."')";
			  @mysql_query($query);
			  fputs($fp, '0">Product Added');
	         $xmlresponse .= '0">Product Added';
			}
Bear in mind, these are code snippets, as I'm trying to save you the trouble of having to read through the XML parsers, etc. Essentially, every other value has NO problem, and, furthermore, in PHP5/MySQL4.1, it works great... PHP4/MySQL4... notsomuch.

Any ideas? Anyone heard anything about newline changes from PHP4 to PHP5? I can't imagine it's a Win/Linux thing... too weird... the HTTP headers shouldn't matter either way. And, honestly, I don't think it's a SQL thing.

Thanks for all of your help.

Posted: Thu Aug 10, 2006 4:05 pm
by volka
Your script has no mysql error handling at all making it hard to debug.
see http://de2.php.net/mysql_error

Posted: Thu Aug 10, 2006 4:36 pm
by feyd
I would also suggest changing addslashes() to mysql_real_escape_string().

Posted: Thu Aug 10, 2006 5:06 pm
by Ollie Saunders
jlevinson wrote:Any ideas?
Not really, I can't explain why it would do that. Are you able to isolate the problem?

Try echoing the variable right before it is being inserted into the database or manually issue a hand emulation of the query to the database so see if the problem is occuring at data level.

Posted: Fri Aug 11, 2006 12:50 am
by RobertGonzalez
A few more suggestions:

1. Change these:

Code: Select all

@mysql_query($query);
to these:

Code: Select all

if (!mysql_query($query)) die('The query failed: ' . mysql_error());
Right now, not only are you not error handling, you are actually suprresing errors.

2. Check to make sure that your extensions/php.ini settings on the local machine match that of the production machine. A lot of times, when I hear 'It works on this one but not on that one' it is because the development environment and production environment don't match.