SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Evangelist BJ Duncan's Avatar
    Join Date
    Jun 2007
    Location
    North Richmond
    Posts
    495
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Spreadsheet assistance please...

    G'day to all,

    I have searched the forums and google to try to find how to implement a way for php to be able to go through each record of a csv and insert into a database.

    I understand the SQL side of things, but just a little unsure of the php side.

    Could someone please provide guidence on where a good tutorial for this would be.

    Many thanks in advance.
    Regards,
    BJ Duncan

  2. #2
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,875
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Each record should end with a newline character which can be identified in PHP using \n

    You can split the file into records and the records into their separate values using explode()


    You can then just substitute the entries directly into the SQL from there.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  3. #3
    SitePoint Member
    Join Date
    Jun 2007
    Location
    Gold Coast, Australia
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    To add to felgall's comments, the easiest way to spilt the file into individual rows is to open the file using the fopen function and then read the individual rows using the fgetcsv function.

  4. #4
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This might be what you're looking for.

    PHP Code:
    $link mysql_connect($host,$dbuser,$dbpassword) or die('Cannot connect to database.');
     
    $selectdb mysql_select_db($database,$link);

    $fp fopen('/path/to/file','r');
     
    $values = array();
     while ((
    $row fgetcsv($fp1000',')) !== false) {
      if (isset(
    $row[0]) && $row[0] != '') {
       
    $values[] = "('".mysql_real_escape_string($row[0], $link)."','".mysql_real_escape_string($row[1], $link)."','".mysql_real_escape_string($row[2], $link)."')";
      }
     }

     if (
    count($values) > 0) {
      
    $uniquevalues array_unique($values);
      
    $query "INSERT IGNORE INTO table (data1,data2,data3) VALUES ";
      
    $query .= implode(','$uniquevalues);
      
    mysql_query($query) or die(mysql_error());
     }
     
    fclose($fp);

     
    mysql_close($link); 

  5. #5
    SitePoint Evangelist BJ Duncan's Avatar
    Join Date
    Jun 2007
    Location
    North Richmond
    Posts
    495
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the assistance guys, I will collaborate all the information and try and implement some function. I will post the end result when done hopefully to assist anyone else attempting this.
    Regards,
    BJ Duncan

  6. #6
    SitePoint Evangelist BJ Duncan's Avatar
    Join Date
    Jun 2007
    Location
    North Richmond
    Posts
    495
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Again, thanks for the assistance guys, just in addition to the importing, I have been able to mould the code provided by Spiderling into my application, but I have run tests with a csv document saved from windows excel and a similar csv from mac version of excel. I was having issues with the mac version. It was only grabbing the first row of data and not the rest. Is there any particular reason for this? Or would every file I upload have to come from windows excel csv to work?

    Many thanks again in advance
    Regards,
    BJ Duncan


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •