insert error because of apostrophe in text file

[rustbckt]

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.

[PFMaBiSmAd]

All string data put into a query must be escaped so that sql special characters in it does not break the syntax of the query.

 

See this link - http://php.net/mysql_real_escape_string

[rustbckt]

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?

[rustbckt]

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";
} 
}
?>

[jcbones]

From post number one, replace this function with the one provided.

 

function query($sql) {
$sql = mysql_real_escape_string($sql);
$this->Query_ID=mysql_query($sql,$this->connection);
if(!$this->Query_ID) {
	echo "Query Failed".mysql_error();
} else {
	return $this->Query_ID;
}
}

[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

[PFMaBiSmAd]

As was previously stated, string data that is put into the query is escaped. The whole query itself is not.

[PFMaBiSmAd]

In the original posted code, find the $a[] = .... line of code and add the following line right before it -

 

$f[$i] = mysql_real_escape_string($f[$i],$this->connection); // add this line of code 
$a[]=trim("'$f[$i]'"); // existing line of code

[jcbones]

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

 

Yes, I added it into the wrong section.  My mistake, but the problem has been identified already.  I knew better, but I insist on doing this stuff when I can barely hold my eyes open.