Error writing text to mySQL table -

I am trying to write a text field to a mySQL table.

Most of the time the query functions ok but there are a couple of records that are tripping the process up.

Attached is one of the records which fails to write - As a beginner on the subject can a more experienced php'er take a look and tell me what could be causing the problem. Thanks

The field in the table is set to LongBlob and for the most part seems to accept the html and other odd quotations and fractions - for example : "ROOM ONE      14 x 218, Whalley (4 miles),<br /> Hurst Green (2 ¼ miles)." writes ok  
(Ref. 41 Brook Villas)<br />SITUATION AND LOCATION<br /><br />Lying in the popular Ribble Valley village of Waddington, Brook Villas lies close to the award winning Coronation Gardens and has open aspects to the front over the village stream.  <br /><br />To reach the property travel from Clitheroe in the direction of Waddington and on entering the village continue through on the main road passing Waddow View on the left hand side and bear left into a small unmade road by the first bridge.  Brook Villas is the first row of stone built houses on the left with aspects over the village stream.<br /><br />Waddington itself offers a host of amenities including junior school, churches, Post Office and General Store and recreational area and there are good main routes to adjoining towns and villages including Clitheroe, West Bradford, Grindleton and Slaidburn.<br /><br />The deceptively spacious family accommodation comprises:<br /><br />ENTRANCE VESTIBULE:			with mosaic tiled floor, bevelled glazed inner door<br /><br />ENTRANCE HALLWAY:			with boarded floor, central heating radiator<br /><br />SITTING ROOM:	1211 x 1210 (approx 3.94m x 3.92m) with boarded floor, cant bay window, cast iron fireplace with tiled inset and matching hearth enclosing open fire, two double central heating radiators, arched alcove<br /><br />LIVING ROOM:	134 x 140 (approx 4.06m x 4.30m) with boarded floor, central heating radiator, stone fireplace enclosing multi fuel fire, built-in storage cupboard to side of chimneybreast, double French opening doors to rear patio area, dado rail, two wall light points<br /><br />KITCHEN:	96 x 711 (approx 2.90m x 2.44m) with range of oak kitchen units incorporating laminated base and matching wall cupboards, built-in appliances including gas hob unit, electric oven, extractor canopy, washing machine, refrigerator, inset single drainer 1 ½ bowl sink unit, casement window, separate non opening window, gas fired combination central heating boiler, adjoining store place under stairs<br /><br />STAIRCASE TO FIRST FLOOR:	<br /><br />LANDING:	with spindle balustrade, central heating radiator, built-in storage under stairs<br /><br />FRONT BEDROOM:	96 x 1011 (approx 2.90m x 3.34m) with transom window, central heating radiator<br /><br />FRONT BEDROOM:	74 x 98 (approx 2.22m x 2.97m) with transom window, central heating radiator, built-in wardrobe in recess<br /><br />REAR BEDROOM:	105 x 141 (approx 3.17m x 4.32m) with ss window, double central heating radiator<br /><br />SEPARATE LOW SUITE W.C:	with half tiled walls, transom window<br /><br />BATHROOM:	with three piece suite comprising enamel panelled bath, pedestal wash hand basin, large shower cubicle, transom window, fully tiled walls, central heating radiator<br /><br />STAIRCASE TO SECOND FLOOR<br /><br />BEDROOM FOUR / PLAYROOM:	118 x 174 (approx 3.57m x 5.26m) with skylight window, access to storage areas under eaves, central heating radiator<br /><br />OUTSIDE <br /><br />To the front of the property is a small enclosed garden area and to the rear a private rear patio with timber storage shed.<br /><br />GENERAL INFORMATION<br /><br />Ribble Valley Borough Council  Tax Band E<br />Gas fired central heating <br /><br />VIEWING	STRICTLY BY APPOINTMENT WITH THE VENDOR'S AGENTS<br /><br />CALL: 01200 427331 <br />CLICK: www.mortimers-property.co.uk<br />COME IN: to our Clitheroe  office<br /><br />PRICE	£299,995<br /><br />

Open in new window

phthorntonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dr_dedoCommented:
use
mysql_real_escap ($data),
mysql_real_escape_string ($data)

and u should use the appropriate encoding to your data
0
phthorntonAuthor Commented:
Thanks for your imput but this made no difference, the Insert query is still crashing out when it gats to the data shown in the example above.

If I take the text (shown above) out of the file and replace it with some random text the query works and moves on to the next record.

Any other ideas?
0
gnarCommented:
Any problems with the currency character?  Is the database using a character set/encoding that supports that symbol?
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

phthorntonAuthor Commented:
No, the currency character appears in other records that import ok.

The php file that loads the data is shown below and i've attached the text file that it imports.

I have also attached an export of the data that makes it into the propdesc table (exported using phpMyAdmin).


<?php 
 
//Clear the database
 
$deleteData1 = "TRUNCATE mmp.basicdata";
mysql_query($deleteData1) or die ('Error, empty database failed');
$deleteData2 = "TRUNCATE mmp.extendeddata";
mysql_query($deleteData2) or die ('Error, empty database failed');
$deleteData3 = "TRUNCATE mmp.lettingsdata";
mysql_query($deleteData3) or die ('Error, empty database failed');
$deleteData4 = "TRUNCATE mmp.mediadata";
mysql_query($deleteData4) or die ('Error, empty database failed');
$deleteData5 = "TRUNCATE mmp.propdesc";
mysql_query($deleteData5) or die ('Error, empty database failed');
 
//Read the text data file
$fp = fopen('data/newData.txt','r'); 
if (!$fp) {echo 'ERROR: Unable to open file.</table></body></html>'; exit;} 
 
//Parse the file and insert data into MySQL
while (!feof($fp)) { 
$line = fgets($fp, 10240);  
list ($AGENT_REF, $ADDRESS_1, $POSTCODE1, $POSTCODE2, $SUMMARY, $DESCRIPTION, $BRANCH_ID, $STATUS_ID, $BEDROOMS, $PRICE, $PRICE_QUALIFIER, $PROP_SUB_ID, $CREATE_DATE, $UPDATE_DATE, $DISPLAY_ADDRESS, $PUBLISHED_FLAG, $LET_TYPE_ID, $LET_FURN_ID, $LET_RENT_FREQUENCY, $TRANS_TYPE_ID, $NEW_HOME_FLAG, $MEDIA_IMAGE_00, $MEDIA_IMAGE_TEXT_00, $MEDIA_IMAGE_01, $MEDIA_IMAGE_TEXT_01, $MEDIA_IMAGE_02, $MEDIA_IMAGE_TEXT_02, $MEDIA_IMAGE_03, $MEDIA_IMAGE_TEXT_03, $MEDIA_IMAGE_04, $MEDIA_IMAGE_TEXT_04, $MEDIA_IMAGE_05, $MEDIA_IMAGE_TEXT_05, $MEDIA_IMAGE_06, $MEDIA_IMAGE_TEXT_06, $MEDIA_IMAGE_07, $MEDIA_IMAGE_TEXT_07, $MEDIA_IMAGE_08, $MEDIA_IMAGE_TEXT_08, $MEDIA_IMAGE_09, $MEDIA_IMAGE_TEXT_09, $MEDIA_IMAGE_10, $MEDIA_IMAGE_TEXT_10, $MEDIA_IMAGE_11, $MEDIA_IMAGE_TEXT_11, $MEDIA_IMAGE_12, $MEDIA_IMAGE_TEXT_12, $MEDIA_IMAGE_13, $MEDIA_IMAGE_TEXT_13, $MEDIA_IMAGE_14, $MEDIA_IMAGE_TEXT_14, $MEDIA_IMAGE_15, $MEDIA_IMAGE_TEXT_15, $MEDIA_IMAGE_16, $MEDIA_IMAGE_TEXT_16, $MEDIA_IMAGE_17, $MEDIA_IMAGE_TEXT_17, $MEDIA_IMAGE_18, $MEDIA_IMAGE_TEXT_18, $MEDIA_IMAGE_19, $MEDIA_IMAGE_TEXT_19, $MEDIA_IMAGE_20, $MEDIA_IMAGE_TEXT_20, $MEDIA_IMAGE_21, $MEDIA_IMAGE_TEXT_21, $MEDIA_IMAGE_22, $MEDIA_IMAGE_TEXT_22, $MEDIA_IMAGE_23, $MEDIA_IMAGE_TEXT_23, $MEDIA_IMAGE_24, $MEDIA_IMAGE_TEXT_24, $MEDIA_IMAGE_60, $MEDIA_IMAGE_TEXT_60, $MEDIA_IMAGE_61, $MEDIA_IMAGE_TEXT_61, $MEDIA_FLOOR_PLAN_00, $MEDIA_FLOOR_PLAN_TEXT_00, $MEDIA_FLOOR_PLAN_01, $MEDIA_FLOOR_PLAN_TEXT_01, $MEDIA_FLOOR_PLAN_02, $MEDIA_FLOOR_PLAN_TEXT_02, $MEDIA_FLOOR_PLAN_03, $MEDIA_FLOOR_PLAN_TEXT_03, $MEDIA_DOCUMENT_00, $MEDIA_DOCUMENT_TEXT_00, $MEDIA_DOCUMENT_01, $MEDIA_DOCUMENT_TEXT_01, $MEDIA_DOCUMENT_02, $MEDIA_DOCUMENT_TEXT_02, $MEDIA_DOCUMENT_03, $MEDIA_DOCUMENT_TEXT_03, $MEDIA_DOCUMENT_50, $MEDIA_DOCUMENT_TEXT_50, $MEDIA_DOCUMENT_51, $MEDIA_DOCUMENT_TEXT_51, $MEDIA_DOCUMENT_52, $MEDIA_DOCUMENT_TEXT_52, $MEDIA_VIRTUAL_TOUR_00, $MEDIA_VIRTUAL_TOUR_TEXT_00, $MEDIA_VIRTUAL_TOUR_01, $MEDIA_VIRTUAL_TOUR_TEXT_01, $MEDIA_VIRTUAL_TOUR_02, $MEDIA_VIRTUAL_TOUR_TEXT_02) = split ('\|', $line); 
 
$basicdata = "INSERT INTO basicdata (AGENT_REF, ADDRESS_1, POSTCODE1, POSTCODE2, SUMMARY, DISPLAY_ADDRESS, PUBLISHED_FLAG, TRANS_TYPE_ID) VALUES ('$AGENT_REF', '$ADDRESS_1', '$POSTCODE1', '$POSTCODE2', '$SUMMARY', '$DISPLAY_ADDRESS', '$PUBLISHED_FLAG', '$TRANS_TYPE_ID')";
 
$extendeddata = "INSERT INTO extendeddata (AGENT_REF, BRANCH_ID, STATUS_ID, BEDROOMS, PRICE, PRICE_QUALIFIER, PROP_SUB_ID, CREATE_DATE, UPDATE_DATE, NEW_HOME_FLAG) VALUES ('$AGENT_REF', '$BRANCH_ID', '$STATUS_ID', '$BEDROOMS', '$PRICE', '$PRICE_QUALIFIER', '$PROP_SUB_ID', '$CREATE_DATE', '$UPDATE_DATE', '$NEW_HOME_FLAG')";
 
$lettingsdata = "INSERT INTO lettingsdata (AGENT_REF, LET_TYPE_ID, LET_FURN_ID, LET_RENT_FREQUENCY) VALUES
('$AGENT_REF', '$LET_TYPE_ID', '$LET_FURN_ID', '$LET_RENT_FREQUENCY')";
 
$propdesc = "INSERT INTO propdesc (AGENT_REF, DESCRIPTION) VALUES ('$AGENT_REF', 'mysql_real_escape_string($DESCRIPTION)')";
 
mysql_query($basicdata) or die ('Error, insert basicdata failed');
mysql_query($extendeddata) or die ('Error, insert extendeddata failed');
//mysql_query($lettingsdata) or die ('Error, insert lettingsdata failed');
mysql_query($propdesc) or die ('Error, insert propdesc failed');
 
//Echo the data to screen
echo ' 
<table style="border:2px solid #ccc">
<tr> 
<td style="border:1px solid #eee">'.$AGENT_REF.'</td> 
<td style="border:1px solid #eee">'.$ADDRESS_1.'</td> 
<td style="border:1px solid #eee">'.$POSTCODE1.'</td> 
<td style="border:1px solid #eee">'.$POSTCODE2.'</td> 
<td style="border:1px solid #eee">'.$SUMMARY.'</td> 
<td style="border:1px solid #eee">'.$DESCRIPTION.'</td> 
</tr>
 
</table>';
 
$fp++; 
} 
 
fclose($fp); 
 
 
 
 
  
?> 

Open in new window

newData.txt
propdesc.txt
0
phthorntonAuthor Commented:
OK, I found the problem myself, it was a ' in the text which I now remove when tidying up the original document.

For the points though, can anyone provide me with a line of code to put in the php file above that will skip empty rows at the end of the text file?

Thanks for your help.
0
gnarCommented:
What about just checking the $line variable on line 22 to make sure it's not empty before proceeding?  If it is, just have it skip that block.  Might help in case you end up with a blank linein the input file later, too.
0
phthorntonAuthor Commented:
Thanks for the input, can you point me in the right direction, guess it would be something like "if isdefined"?
0
gnarCommented:
It'll be defined, just full of white space or something...

Maybe something silly like this:


if (trim($line)!='') {
 
Do all your goods here...  
 
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
phthorntonAuthor Commented:
Thanks for you help on this.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.