?
Solved

Error writing text to mySQL table -

Posted on 2008-02-06
9
Medium Priority
?
326 Views
Last Modified: 2013-12-12
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

0
Comment
Question by:phthornton
  • 5
  • 3
9 Comments
 
LVL 16

Expert Comment

by:dr_dedo
ID: 20833502
use
mysql_real_escap ($data),
mysql_real_escape_string ($data)

and u should use the appropriate encoding to your data
0
 

Author Comment

by:phthornton
ID: 20833600
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
 
LVL 2

Expert Comment

by:gnar
ID: 20833704
Any problems with the currency character?  Is the database using a character set/encoding that supports that symbol?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

Author Comment

by:phthornton
ID: 20835115
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
 

Author Comment

by:phthornton
ID: 20836034
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
 
LVL 2

Expert Comment

by:gnar
ID: 20836071
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
 

Author Comment

by:phthornton
ID: 20836201
Thanks for the input, can you point me in the right direction, guess it would be something like "if isdefined"?
0
 
LVL 2

Accepted Solution

by:
gnar earned 2000 total points
ID: 20836301
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
 

Author Closing Comment

by:phthornton
ID: 31428535
Thanks for you help on this.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question