special character getting lost in translation....

[acook]

I'm running into a problem.  I have a php page that queries a database (sybase via odbc) and dumps the results in a mySQL database.  Here's the code:

 

<?php

$db_user = "";
$db_pass = "";
$dsn = "OPASRPT";

$conn = odbc_connect($dsn, '', '');
$conn2 = mysql_connect("localhost","user","");

$yesterday = date("Y-m-d",mktime(0,0,0,date("m"), date("d")-1, date("Y")));

//THE QUERY

$query = "SELECT Create_date, Submitter FROM Call_Log WHERE (Create_date>={ts '$yesterday 00:00:00'} AND Create_date<={ts '$yesterday 23:59:59'})";


//THE EXECUTION

$result = odbc_exec($conn, $query) or die("Query failed, could not connect to table.  Are you sure it exists?");


//THE LOOP

//SETS AN EMPTY ARRAY
$results_array = array();
while(odbc_fetch_row($result))
{
  $results_array[] = array(  //ARRAY NEEDED FOR STINKIN' IF/ELSE
  'Create_date' => odbc_result($result, 1),
  'Submitter' => odbc_result($result, 2),

  );
  
}

//NO RESULTS?  
if (empty($results_array))
{
  exit('<p align=center><font face=Tahoma size=2><br><b>There is nothing to import (THIS SHOULD NEVER HAPPEN).</b></p>');
}

else
{
  
foreach($results_array AS $this_row)
  {

mysql_query("INSERT INTO `reporting`.`hd_reporting_call_logs` (`Create_date` , `Submitter`) VALUES ('{$this_row['Create_date']}', '{$this_row['Submitter']}')");

}

}
//CLOSING THE CONNECTION
odbc_close($conn);
mysql_close($conn2);
?>

 

Everything transfers over fine EXCEPT there are several records where the submitter has a special character in it (a ').  His name is Frank D'Antonio.  Does anyone know if this is a problem with my array?  I've run the same query (over ODBC) using another program (like excel) and it works fine.  Any ideas?

[kenrbnsn]

When you insert the data into the mysql database, you should use the mysql_real_escape_string() function to escape any characters that will cause problems in mysql, this includes the single quote.

 

Ken

[acook]

Ok, thanks!  Do you know how I could implement this into my current code? 

[acook]

I actually tried:

 

foreach($results_array AS $this_row)
  {

mysql_real_escape_string(mysql_query("INSERT INTO `reporting`.`hd_reporting_call_logs` (`Create_date` , `Submitter`) VALUES ('{$this_row['Create_date']}', '{$this_row['Submitter']}')"));

}

 

but I'm still having the same problem... does it need to be applied elsewhere?

[kenrbnsn]

No, the function need to be applied to each value being inserted, not to the query.

 

Ken

[acook]

Thanks, I figured it out!!