Page 1 of 1

Help needed with .csv importing

Posted: Thu Apr 02, 2009 9:01 am
by Noobie
Hello everyone!

I'm attempting to upload a .csv file and then save the info in a table. I can do this using this code:

Code: Select all

<?php
$uploaddir = '/home/public_html/uploads/';
$uploadfile = $uploaddir . basename($_FILES['users']['name']);
$uploadfilename = basename($_FILES['users']['name']);
 
if (move_uploaded_file($_FILES['users']['tmp_name'], $uploadfile)) {
   echo "<h2>Sucessful Upload</h2><p>File is valid, and was successfully uploaded.</p>";
   } else {
      echo "<p>Problem Uploading</p>";
   }
 
  include("/dbsetup.php");
      $handle = fopen ('/home/public_html/uploads/users.csv', 'r');
       while (($data = fgetcsv($handle, 1000, ',', '"')) !== FALSE)
      {
         $query = "INSERT INTO users VALUES ('". implode("','", $data)
         ."')";
         $query = @mysql_query($query);
          }
?>
BUT I've got two problems with this.

1. I need the uploaded information to completely overwrite what's currently in the table - currently it's appending info to the stuff there (never had to do this before - do I empty the table somehow before inserting new stuff?)

2. One of the fields is a password - not sure how to fit in the MD5 conversion bit that I'd use with a normal update query.

Thanks in advance.

Re: Help needed with .csv importing

Posted: Sun Apr 05, 2009 11:09 am
by Noobie
Hi - thanks for the reply!

I got it working using REPLACE:

Code: Select all

$filename='/home/public_html/uploads/users.csv';
  $handle = fopen("$filename", "r");
  while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
  {
  $import="REPLACE users(id,titlex,first_name,last_name,company,groupx,password) values(' ','$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]')";
    mysql_query($import) or die(mysql_error());
   }
fclose($handle);
But I've got a problem... apostrophes break the upload (e.g. in names for instance).

I tried adding:

Code: Select all

$data  = array_map('mysql_real_escape_string', $data);
Thinking I was being clever (first mistake!) but that just uploaded everything surrounded by "mysql_real_escape_string"!! :oops:

Any suggestions on how to add mysql_real_escape_string to the array?

Thanks

Re: Help needed with .csv importing

Posted: Tue Apr 07, 2009 11:42 am
by Noobie
Thanks McInfo - I've never heard of array_walk() before.

I'll give it a try and let you know if it works for me!

thanks.

Re: Help needed with .csv importing

Posted: Tue Apr 07, 2009 11:55 am
by Noobie
I'm getting an error now - not sure what I've done wrong...

The error message is:

Warning: implode() [function.implode]: Invalid arguments passed in /home/public_html/admin/add.php on line 31

Code: Select all

include("/home/public_html/includes/dbsetup.php");
 
$filename='/home/public_html/uploads/users.csv';
$handle = fopen("$filename", "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
 
array_walk($data, 'mres_walk', $dbh);
function mres_walk (&$datum, $i = 0, $link_identifier = null)
    {
    if (is_resource($link_identifier))
     $datum = mysql_real_escape_string($datum, $link_identifier);
       else
        $datum = mysql_real_escape_string($datum);
     return true;
     }
     {
     $import = "REPLACE users(id,titlex,first_name,last_name,company,groupx,password) values(' ','".implode("','", $data)."')";
      }
 
fclose($handle);
Also - should the else have } and { around it?

Re: Help needed with .csv importing

Posted: Wed Apr 08, 2009 3:23 am
by Noobie
Ok so I've downloaded a php tidy for HTML-kit (which I no longer use) and installed it and run it on the bit of code. Still don't know where the error is. Probably would have been quicker just to tell me and add the nag on the end!

Code: Select all

 include("/home/public_html/includes/dbsetup.php");
 
  $filename='/home/public_html/uploads/users.csv';
  $handle = fopen("$filename", "r");
  while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
 
  array_walk($data, 'mres_walk', $dbh);
  function mres_walk (&$datum, $i = 0, $link_identifier = null)
  {
    if (is_resource($link_identifier))
    $datum = mysql_real_escape_string($datum, $link_identifier);
    else
    $datum = mysql_real_escape_string($datum);
    return true;
  }
  {
    $import = "REPLACE users(id,titlex,first_name,last_name,company,groupx,password) values(' ','".implode("','", $data)."')";
  }
 
  fclose($handle);

Re: Help needed with .csv importing

Posted: Fri Apr 10, 2009 6:38 am
by Noobie
Thanks - I'll give that a go!

Re: Help needed with .csv importing

Posted: Fri Apr 10, 2009 12:57 pm
by ryankroonenburg
McInfo wrote:I really helps if you properly indent your code. Why did you change the indentation of the mres_walk() function anyway? I can see what you did wrong; and you will too when you indent your code properly.
Indenting is the key to programming efficiently. You can save hours of trawling though code if you learn to indent well. Unfortunately we all seem to learn the hard way :-)