rustbckt

I replaced the function with yours and now I am getting a query error: Query FailedYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\'74578\',\'25000\',\'2\',\'Lots/Acreage\',\'Vacant Lot\',\'0\',\'0\',\'0\',\'El' at line 1

Here is some other code that I have tried but it still has the same errors. Is there another character that I can use instead of apostrophes when the file is imploded? <? # first get a mysql connection as per the FAQ $fcontents = file ('loading.txt'); # expects the csv file to be in the same dir as this script for($i=0; $i<sizeof($fcontents); $i++) { $line = trim($fcontents[$i],"\t"); $arr = explode("\t", $line); #if your data is comma separated # instead of tab separated, # change the '\t' above to ',' $sql = "insert into mlsdb values ('". implode("','", $arr) ."')"; mysql_query($sql); echo $sql ."<br>\n"; if(mysql_error()) { echo mysql_error() ."<br>\n"; } } ?>

Thank you for the quick reply! I am still suck, I tried putting in the string but it is not working and not having any luck on to where to put the string. I placed it just before the insert but that seems to only get errors. Where/how would I put that string in?

I have found some code that allows me to input a tab separated file into a mysql database. The code works perfect except on one part of the file. It looks like when it opens the file up it gets a error when one of the columns has a apostrophe in it and skips that line on the text file. I have been looking at the code and trying things but have not found any way for it to ignore apostrophes in the text file. Any help on changing the code would be great. Here is the code that starts the process: include("config.php"); include("fileClass.php"); $objFile=new exportFile; $objFile->connect(); $objFile->exportFileToDatbase("Loading.txt","\t","r","mlsdb",78); // File name,Seprator,mode,tablename,field ?> Here is the code that seems to be getting the error with the apostrophy <? class exportFile { var $Query_ID=0; var $connection=0; function connect() { if($this->connection==0) { $this->connection=mysql_connect(HOST,USERNAME,PASSWORD) or die("<b>Database Error</b><br>".mysql_error()); $SelectResult = mysql_select_db(DB, $this->connection) or die("Couldnot Select Database".mysql_error()); } else { echo "Connection Couldnot be Established"; die(); } } function query($sql) { $this->Query_ID=mysql_query($sql,$this->connection); if(!$this->Query_ID) { echo "Query Failed".mysql_error(); } else { return $this->Query_ID; } } function exportFileToDatbase($filename,$de,$mode,$tablename,$fieldno) { $fd=fopen($filename,"$mode"); while(!feof($fd)) { $line=fgets($fd,5000); $f=explode($de,$line); for($i=0;$i<$fieldno;$i++) { $a[]=trim("'$f[$i]'"); } $value=implode(",",$a); unset($a); $sql="insert into $tablename values($value)"; //echo $sql; $this->query($sql); } } } ?> The file is just a simple tab separated file and it seems to be reading it right. It will get over 10000 entries in and the only ones that have errors are the ones with apostrophes in the text. The error it gests is: Query FailedYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'creek frontage on Hightower Creek and great mountain views. Approximately 10 min' at line 1 PHP version: 5.2.11 Mysql version: 5.1.37 Thank you for your help.

Sorry about that. I have updated the tags so it displays better. Thank you. <?php # Author: Keegan # Email: keegan@sifizm.com # Web Site: www.sifizm.com # I run this script from a cron job every night to update # the mysql database I use with my employee web site # so it matches my local database every day. Feel free to # modify it to meet your specific needs. If you find it # usefull, drop me an email and let me know. # edit the follow six items to use the script # first connect to your mysql database # i have my connection settings in a diferent file # so i just include that file in all my scripts include("connections/mls.php"); # assign the tables that you want to import to to the table array $table = array( 'mlsdb', ); # if the first row of your csv file contains column headings: # $columnheadings=1 # if the first row does not contain column headings and should be imported: # $columnheadings=0 $columnheadings = 1; # contains the email address you want the results sent to $emailaddress = "email@someaddress.com"; # contains the subject you want the message to have $subject = "Enter Subject Here"; # contains the email address that will show in the from line $emailfrom = "email@someaddress.com"; # you should not have to edit anything below this line # perform the required operations for every table listed in the table array foreach ($table as $tablename) { # empty the table of its current records $deleterecords = "TRUNCATE TABLE `$tablename`"; mysql_query($deleterecords); # intialize your counters for successful and failed record imports $pass = 0; $fail = 0; # the csv file needs to be the same name as the table, # comma seperated with the columns in the same order as the table, # and in the same dir as this script $filecontents = file ("$tablename.txt"); # .csv is added to the table name to get the name of the csv file # every record in the csv file will be inserted into the table unless an error occurs with that record for($i=$columnheadings; $i<sizeof($filecontents); $i++) { $insertrecord = "Insert Into `$tablename` Values ($filecontents[$i])"; mysql_query($insertrecord); if(mysql_error()) { $fail += 1; # increments if there was an error importing the record } else { $pass += 1; # increments if the record was successfully imported } } # adds a line to the email message we will send stating how many records were imported # and how many records failed for each table $message .= "Table $tablename: Success=$pass Failure=$fail \n"; } # set to the date and time the script was run $runtime = (date("d M Y H:i")); # add the run time to the body of the email message $message .= "\nTime of the message: $runtime (server time zone)\n\n"; # Send the email message mail($emailaddress, $subject, $message, "From: '$emailfrom'"); ?> Here is my text file:

I have this code I have been trying to get it to work but no luck. I am running on php 5 with mysql. The text file I have download nightly so I have the updated file. There is over 15,000 records in the text file but I have hit a wall on this. I am very very new to php and mysql any help would be great! Here is the code untouched I just have my connection and table in it: <?php # Author: Keegan # Email: keegan@sifizm.com # Web Site: www.sifizm.com # I run this script from a cron job every night to update # the mysql database I use with my employee web site # so it matches my local database every day. Feel free to # modify it to meet your specific needs. If you find it # usefull, drop me an email and let me know. # edit the follow six items to use the script # first connect to your mysql database # i have my connection settings in a diferent file # so i just include that file in all my scripts include("connections/mls.php"); # assign the tables that you want to import to to the table array $table = array( 'mlsdb', ); # if the first row of your csv file contains column headings: # $columnheadings=1 # if the first row does not contain column headings and should be imported: # $columnheadings=0 $columnheadings = 1; # contains the email address you want the results sent to $emailaddress = "email@someaddress.com"; # contains the subject you want the message to have $subject = "Enter Subject Here"; # contains the email address that will show in the from line $emailfrom = "email@someaddress.com"; # you should not have to edit anything below this line # perform the required operations for every table listed in the table array foreach ($table as $tablename) { # empty the table of its current records $deleterecords = "TRUNCATE TABLE `$tablename`"; mysql_query($deleterecords); # intialize your counters for successful and failed record imports $pass = 0; $fail = 0; # the csv file needs to be the same name as the table, # comma seperated with the columns in the same order as the table, # and in the same dir as this script $filecontents = file ("$tablename.txt"); # .csv is added to the table name to get the name of the csv file # every record in the csv file will be inserted into the table unless an error occurs with that record for($i=$columnheadings; $i<sizeof($filecontents); $i++) { $insertrecord = "Insert Into `$tablename` Values ($filecontents[$i])"; mysql_query($insertrecord); if(mysql_error()) { $fail += 1; # increments if there was an error importing the record } else { $pass += 1; # increments if the record was successfully imported } } # adds a line to the email message we will send stating how many records were imported # and how many records failed for each table $message .= "Table $tablename: Success=$pass Failure=$fail \n"; } # set to the date and time the script was run $runtime = (date("d M Y H:i")); # add the run time to the body of the email message $message .= "\nTime of the message: $runtime (server time zone)\n\n"; # Send the email message mail($emailaddress, $subject, $message, "From: '$emailfrom'"); ?> Here is my text file: ListNum ListPrice PhotoCount LstFormType LstPropertyType Bedrooms FullBaths PartialBaths City FetFrontage FetView LotSize FetCondition Remarks ListOfficeID ListOfficeName LotNumber FetBusinessType FetDesign FetConstruction FetSaleIncludes NumOfUnits LstArea Acreage ListAgentID YearBuilt ZipCode LstStatus PhotoTimeStamp LakeName LicensedOwner ListAgentCellPh ListAgentEmail ListAgentName RiverName FetTerrain MainFlrBedrms UpperFlrBedrms LowerFlrBedrooms TotalRooms ZoningCode SurveyAvailable Coventants FetRestrictions FetMasterBedroom FetParking FetCooling FetRoadSurface FetLake FetLaundryLocation Township FetMilesToTown Subdivision FetDriveway FetWater FetSewer FetWindows FetRoof FetBasement FetStyle FetRooms FetInterior FetExterior FetHeating FetAppliances FetFloors ListOfficeEmail ListOfficePhone ListOfficePhone2 AgentPhone StreetNumber StreetDirection StreetName Address State FetRecreation VirtualTourUrl Foreclosure 74578 25000 2 Lots/Acreage Vacant Lot 0 0 0 Ellijay Road .5 Acre Nice Laying Property! Easy to Build On! Great Area of Attractive Homes! WOOD02 WOODLAND REALTY, LLC 136 BL Gilmer County - Area 7 0.55 BCANTR 30540 Active 6/25/2009 5:51:30 PM False 706-273-9266 westrlty@ellijay.com Betty M. Cantrell Level, Gentle 0 0 0 False True Paved Ellijay Under 5 Miles Coosawattee River Public Util/Avail None woodlandrealty@etcmail.com 706-276-1700 800-875-9026 706-636-4111 136BL SUMMIT DRIVE 136BL SUMMIT DRIVE GA 0 94580 89900 Lots/Acreage Vacant Lot 0 0 0 Hiawassee Road Mountain, Lake .9 Great neighborhood good views of mountains and lake. Other lots available in Sutton Cove subdivision are #14 and # 8 in phaseII. Available in Phase I are lots #18 and #23 CLAND CHATUGE LAND COMPANY 13 Towns County - Area 4 0.9 VWALDR 30546 Active True 706-781-7674 chatugel@windstream.net Viola Dyer Waldroup Gentle 0 0 0 False True Paved Under 5 Miles Sutton Cove Phase II Public Util/Avail None chatugel@windstream.net 706-896-2940 800-893-2940 706-896-2940 13 CEDARCLIFF RD 13 CEDARCLIFF RD GA 0 95724 69900 1 Lots/Acreage Commercial Lot 0 0 0 Murphy NC Road 1.20A+- Lot has home site on top or remove dirt for a commercial location. Between several storage buildings. Fronts on 19-129 CAROL ERA CAROLINA MOUNTAIN HOMES Cherokee Co N.C. - Area 9 1.2 JPOSEY 28906 Active 4/23/2009 7:00:01 AM False 828-361-1548 joanposey@dnet.net Joan Posey-Neumann Rolling 0 0 0 True False Paved, State, See Remarks Notla 5-10 Miles Long Ridge Est. None None eracmh@brmemc.net 828-837-7322 800-747-7322 828-837-7322 15 HWY 19-129 S 15 HWY 19-129 S NC http://www.firsthometour.com/virtual_tours/views/virtual_tour.asp?virtual_tour_id=5065 0 95997 199900 1 Lots/Acreage Acreage 0 0 0 ELLIJAY Road, Branch Mountain, Year Round 13.15 LOCATION! ACREAGE IN THE CITY OF ELLIJAY. MANY HOME SITES AVAILABLE WOOD02 WOODLAND REALTY, LLC Gilmer County - Area 7 13.15 IHARPE 30540 Active 5/19/2009 7:00:00 AM False 706-273-9191 shadetree@ellijay.com Iris Davis Gentle, Rolling, Steep 0 0 0 True False Paved, State ELLIJAY Under 5 Miles Community/Available, None None woodlandrealty@etcmail.com 706-276-1700 800-875-9026 706-636-1010 HIGHWAY 5 NORTH HIGHWAY 5 NORTH GA 0 101666 449900 10 Lots/Acreage Acreage 0 0 0 Morganton Road, Creek Mountain, Year Round, Pasture, Creek 17.18 Acres HEMPTOWN CREEK AND HWY. 515 FRONTAGE. 17.18 acres of open pasture land, fenced, large red barn and 2,000 feet on Hemptown Creek (trout stream). Great property for horses and cattle. Access on Old Hwy. 76 and Hwy. 515. COMMERCIAL POTENTIAL! GEORG GEORGIA PRIME REAL ESTATE, LLC Fannin County - Area 6 17.18 HBRUCE 30560 Active 12/4/2008 5:38:55 PM True howellbruce@georgiaprime.com Howell Bruce Hemptown Level, Gentle, Flood Plain, Bottom Land 0 0 0 True False Gravel, Paved, County, State Morganton 5-10 Miles N/A None None info@georgiaprime.com 706-632-8555 877-632-1192 706-632-5671 HWY 515 OLD HWY. 76 HWY 515 OLD HWY. 76 GA 0 106251 1600000 1 Commercial Commercial 0 0 0 MURPHY Road 0 MOUNTAIN VISTA INN IS LOCATED IN BEAUTIFUL DOWNTOWN MURPHY AND OFFERS HIGH TRAFFIC AND VISIBILITY, SUPER LOCATION,40 ROOMS (SUITES,DELUXE,STANDARD) THREE LEVELS, A FULLY OPERATIONAL RESTAURANT AND 10,000 SG FEET OF BANQUET FACILITIES.MEETING ROOMS. MOUNTAIN VIEWS, EXCELLENT PARKING AREA. EXCEPTIONAL CONSTRUCTION. CALL JOY STEIN FOR APPOINTMENT ONLY. SERIOUS INQUIRIES PLEASE! VISTA VISTA REALTY 0 Restaurant/Food Service, Hotel/Motel 3-4 Story Concrete Block, See Remarks Cherokee Co N.C. - Area 9 0 JRODRI 1982 28906 Active 10/7/2008 2:34:01 PM False 828-361-4588 joy@brmemc.net Joy S. Rodriguez Level 0 0 0 0 False True Private, Large Area Central Electric Paved Under 5 Miles Public Utilities City Shingle Handicap Facilities, Ceiling Fans, Sprinkler System, Public Restrooms, Private Restrooms, Cable TV Available, Phone, See Remarks Large Lot, Signs, Security Lights, Trash Collection, See Remarks Central Carpet, Terrazzo/Tile vistarealty@brmemc.net 828-835-8800 888-835-0447 828-361-4588 78 TERRACE AVENUE 78 TERRACE AVENUE NC 0 106480 79500 5 Lots/Acreage Vacant Lot 0 0 0 Hiawassee Road, Creek Mountain, Year Round 1.194 acres Nine great creek front lots. Lots 1-8 have an engineered septic system. Most lots have approximately 100' creek frontage on Hightower Creek and great mountain views. Approximately 10 minutes east of Hiawassee in a quiet area of Towns County. Close to Lake Chatuge, Brasstown Bald, and the towns of Clayton and Helen, Georgia. Creek is state stocked and also contains native rainbow trout. Hi-speed internet available. C2SCE C-21 SCENIC REALTY 1 Towns County - Area 4 1.194 DAPPLE 30546 Active 7/20/2009 5:37:04 PM False 706-835-5007 dapplegate@windstream.net Diane Applegate Hightower Level, Gentle 0 0 0 False True Paved 5-10 Miles Creekside Upper High Shared Well Commercial/Available, None scenic21@brmemc.net 706-896-8633 800-997-4981 706-835-5007 LOT 1 CREEKSIDE LOT 1 CREEKSIDE GA http://tournow.net/show/44611/idx 0 106487 79500 8 Lots/Acreage Vacant Lot 0 0 0 Hiawassee Road, Creek Mountain, Year Round .71 acre Nice creek front lot. Great end location in beautiful Creekside at Upper Hightower Development. Lot 8 has an engineered septic system. Approx. 100' Hall Creek frontage and great mountain views. Drive cut in, just drive or walk down to the creek. Culdesac location. Approximately 10 minutes east of Hiawassee in a quiet area of Towns County. Close to Lake Chatuge, Brasstown Bald, and the towns of Clayton and Helen, Georgia. Hi-speed internet available. C2SCE C-21 SCENIC REALTY 8 Towns County - Area 4 0.71 DAPPLE 30546 Active 7/31/2009 2:25:29 PM False 706-835-5007 dapplegate@windstream.net Diane Applegate Hightower Level, Gentle 0 0 0 False True Paved 5-10 Miles Creekside Upper High Shared Well Commercial/Available scenic21@brmemc.net 706-896-8633 800-997-4981 706-835-5007 LOT 8 CREEKSIDE LOT 8 CREEKSIDE GA http://tournow.net/show/44611/idx 0 108255 19900 1 Lots/Acreage Vacant Lot 0 0 0 HAYESVILLE Road, Branch Mountain, Year Round 1.04 REDUCED!!!!! REDUCED!!!!! GREAT VIEWS SURROUND THIS 1 ACRE PARCEL IN EAGLE FORK VALLEY. SMALL BRANCH BORDERS. 2 BEDROOM SEPTIC PERMIT ON FILE. C2SCE C-21 SCENIC REALTY 1 Clay County - Area 8 1.04 TNEWEL 28904 Active 9/8/2006 7:30:36 PM True 770-265-9076 tnewell@brmemc.net Teresa Newell Level, Gentle 0 0 0 False True Gravel, Paved, State SHOOTING C 10-15 Miles EAGLE FORK PROPERTIE None None scenic21@brmemc.net 706-896-8633 800-997-4981 828-389-9595 LOT 1 EAGLE FORK RD. LOT 1 EAGLE FORK RD. NC 0

I have a very large text file that gets updated daily and need to import it into a mysql database. I am not sure what is the best way to go about getting this done. I have tried php but it times out before it gets done. The files has over 90 fields and 15,000 listings. It is a real estate database. Any help or direction would be great! The file is a .txt comma separated. Thanks,