HELPPP!!!!! trying to import to MYSQL.

Richardknox · #1 19-May-2007, 16:19

I found a script that I changed that allows me to import a large (8-9 megs) .csv into my sql database. The problem I am encountering, is when I get to a name with a " ' " in its name,

ex(ad l'aldosa L'Aldosa 2 42.5833333 1.6333333)

it crashes with the following error.have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'aldosa,L'Aldosa,02,42.5833333,1.6333333','','', '', '', '')'

Here is the script:

PHP Code:


<?
include "db_connection.php";
?>
<?

if(isset($_POST['submit']))
   {
     $filename=$_POST['filename'];
     $handle = fopen("$filename", "r");
     while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
     {
    
       $import="INSERT into cities(country, city, city_accented, region, latitude, longitude) values('$data[0]','$data[1]','$data[2]', '$data[3]', '$data[4]', '$data[5]')";
       
       mysql_query($import, $dblink) or die(mysql_error());
     }
     fclose($handle);
     print "Import done";
 
   }
   else
   {
 
      print "<form action='import.php' method='post'>";
      print "Type file name to import:<br>";
      print "<input type='text' name='filename' size='20'><br>";
      print "<input type='submit' name='submit' value='submit'></form>";
   }
   ?>

Could you show me a means to fix this bump in the road, because the file is to big to edit to remove the " ' ".

Thanks,
Richard

TurboPT · #2 19-May-2007, 17:58

see function mysql_escape_string().

Richardknox · #3 19-May-2007, 20:39

Well, I tried to using the function mysql_escape_string()., but all I managed was to get the name plus the mysql_escape_string addd to the city names, and it still stopped processing at the city names with the " ' " accent.

Can you give me a little bit more information on how I should be using this??

Thanks,
Richard

Richardknox · #4 19-May-2007, 21:12

Thanks. I was finally able to get it working. Here is the new script.

PHP Code:


<?
include "db_connection.php";
?>
<?

if(isset($_POST['submit']))
   {
     $filename=$_POST['filename'];
     $handle = fopen("$filename", "r");
     while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
     {
    $city = mysql_real_escape_string($data[1], $dblink);
    $city_accented = mysql_real_escape_string($data[2], $dblink);
       $import="INSERT into cities (country, city, city_accented, region, latitude, longitude) values('$data[0]', '$city', '$city_accented', '$data[3]', '$data[4]', '$data[5]')";
              
       mysql_query($import, $dblink) or die(mysql_error());
     }
     fclose($handle);
     print "Import done";
 
   }
   else
   {
 
      print "<form action='import.php' method='post'>";
      print "Type file name to import:<br>";
      print "<input type='text' name='filename' size='20'><br>";
      print "<input type='submit' name='submit' value='submit'></form>";
   }
   ?>

Thanks again for your help.
Richard

Richardknox · #5 24-May-2007, 08:34

I have noticed that once I post a request here for assistance on a problem with a script, or mysql, or anything else related to my website, an answer to my problem is soon to follow. This site provides me a means to talk out my problem, and some of the times, I am able to get my own answer, just by posting it here. Maybe posting helps to set in motion the flow of thoughts.

Thanks,
Richard

TurboPT · #6 24-May-2007, 10:56

Great!, glad we can help.

Sorry that I didn't add the mysql_real_escape_string() in my prior post, but you found it, and got it working!!

Keep posting.

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Moving data from MySQL to MySQL	meurer	MySQL / PHP Forum	0	25-Jan-2007 09:03
Cpanel downgrade MYSQL 4.1.X to MySQL 4.0.xx	Webhosting-live	Web Hosting Forum	1	01-Sep-2006 02:54
How to import structure of tables into ACCESS from MySQL?	_Y_	Computer Software Forum - Windows	0	02-Aug-2006 01:54
Windows: From only £20p/y,Linux: from $10p/m. ASP, ASP.NET, PHP, Free MySQL, +More	EyotaHosts	Web Hosting Advertisements & Offers	0	28-Jun-2003 13:54

HELPPP!!!!! trying to import to MYSQL.

Re: HELPPP!!!!! trying to import to MYSQL.

Re: HELPPP!!!!! trying to import to MYSQL.

Re: HELPPP!!!!! trying to import to MYSQL.

Re: HELPPP!!!!! trying to import to MYSQL.

Re: HELPPP!!!!! trying to import to MYSQL.