SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    Pragmatic Programmer halfasleeps's Avatar
    Join Date
    Feb 2006
    Location
    Altoona, PA. USA
    Posts
    1,945
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how to update a mysql table from a file??

    Im am trying to set up a data-integration with a diamond vender so that I can make a diamond search application.

    they said they can place a file on my server via ftp everyday in any format I want with the data.

    so what I want to do is make a table on my DB to hold this data and make a script that can take that file and update the entire table with all the information from the file.

    the problem is I dont know how to make a script to do that or what kind of file I would need.

    If anyone can shed any light on this I would really appreciate it.

    (By the way there will be around 40,000 diamonds/rows passed)

    THANKS.
    Altoona Design
    Freelance Flex developer for hire.
    ActionScript Programmer with 8 Years Experience.

  2. #2
    SitePoint Addict
    Join Date
    Aug 2007
    Location
    GR
    Posts
    352
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    One possible simple format would be an entry one line, with values (fields) separated by comma like a csv file.
    Then parsing is easy, you loop each line, explode it & construct the query.
    But. Data entry from users is always prone to errors, because users don't have technical abilities. I would suggest using a small data entry application, which guides the user to successfull data entry.

  3. #3
    Pragmatic Programmer halfasleeps's Avatar
    Join Date
    Feb 2006
    Location
    Altoona, PA. USA
    Posts
    1,945
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    @steve I am not sure what you mean?

    the data is coming from the diamond venders data-base I am sure. I don't think anyone is actually typing up 40,000 diamonds each day. I am sure they can give me a .csv file, they said any format I want.

    the problem is I don't know how to make a script that can take the csv or whatever type of file and replace the information in my database with the information from the file.

    if there is a way I can just select the information from the file like I would from a data-base that would work too and I wouldnt need to add it to the data-base, but I am not sure if there is a way to do that.
    Altoona Design
    Freelance Flex developer for hire.
    ActionScript Programmer with 8 Years Experience.

  4. #4
    SitePoint Addict
    Join Date
    Aug 2007
    Location
    GR
    Posts
    352
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, I thought that they will add some rows each day and the database is already 40K.

    Then ask for a format with each row in a line,separated with commas and string data quoted.

    Then parsing would be:
    PHP Code:
    <?
    $fp 
    fopen($pathtofile,'r');
    $line ='';
    $data_arr = array();
    while (!
    feof($fp)) {
        
    $line fgets($fp);
        
    $tmp_arr explode(','$line);
        
    array_push($data_arr$tmp_arr);
        
    $tmp_arr = array();
            }
    fclose($fp);
    ?>
    This will give you a 2-dimensional array which you can loop and add data to your database.

  5. #5
    Pragmatic Programmer halfasleeps's Avatar
    Join Date
    Feb 2006
    Location
    Altoona, PA. USA
    Posts
    1,945
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thank you, the data is always changing because other wholesalers might have sold diamonds and what not so its always going to wipe clean and reinsert the diamonds usually around 40,000


    ok so if my table is like this:

    Shape | Carat | Cut | Price | Stock_num

    I should just get a text file and it should look like:

    "round","1.0","good","5000","3dfss45"
    "princess","0.96","excellent","7000","dfg56gt"
    "marquise","1.06","fair","2000","gdfgdff"
    "oval","2.0","poor","6000","et5g54trt"

    Please specify.

    also if it is a loop of 40,000 will it be ok or crash?


    Thank you soo much for your help.
    Altoona Design
    Freelance Flex developer for hire.
    ActionScript Programmer with 8 Years Experience.

  6. #6
    Pragmatic Programmer halfasleeps's Avatar
    Join Date
    Feb 2006
    Location
    Altoona, PA. USA
    Posts
    1,945
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I just built a sample to test it out and it works great THANK YOU SO MUCH!!!

    i thought it would be alot more code than that but thats pretty simple.
    Altoona Design
    Freelance Flex developer for hire.
    ActionScript Programmer with 8 Years Experience.

  7. #7
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Some notes,

    There's more to csv than just commas, so I'd suggest using fgetcsv to read it.

    As far as the size, 40k is not that much and running it on daily basis won't crash it. But if you do not need any pre-processing, you probably will not want to read it into memory (array), rather just add to database as you read from file.
    Saul

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    another possibility is to simply load the incoming csv file into a table

    advantage: 1 command



    the only reason you would loop over the individual rows and construst an INSERT statement for each one would be because you needed to do editing, transformations, error-correction, and so on (if this is not needed, the LOAD is definitely more efficient)

    once you have uploaded the file to a "holding" table, you can do editing, transformations, error-correction with simple SQL queries, including saving clean results to your application's tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by halfasleeps View Post
    I am sure they can give me a .csv file, they said any format I want.
    everybody can do csv, so start with that, but if they can do xml, that's a stronger option
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    Pragmatic Programmer halfasleeps's Avatar
    Join Date
    Feb 2006
    Location
    Altoona, PA. USA
    Posts
    1,945
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmm, I was jsut using a .txt file with my test, would it be better to use a csv file?

    i dont think anything needs to be updated, I just need to remove all fields from data base and then reinsert the data.

    Is there a way to remove all rows from the table before I insert the new rows?

    thanks guys
    Altoona Design
    Freelance Flex developer for hire.
    ActionScript Programmer with 8 Years Experience.

  11. #11
    Pragmatic Programmer halfasleeps's Avatar
    Join Date
    Feb 2006
    Location
    Altoona, PA. USA
    Posts
    1,945
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    everybody can do csv, so start with that, but if they can do xml, that's a stronger option
    yes they said they can do xml, how would i code it to take the data from the xml and insert it into the db?

    Thanks.
    Altoona Design
    Freelance Flex developer for hire.
    ActionScript Programmer with 8 Years Experience.

  12. #12
    SitePoint Wizard Hammer65's Avatar
    Join Date
    Nov 2004
    Location
    Lincoln Nebraska
    Posts
    1,161
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If no altering of data needs to be done before inserting it, I would recommend truncating the table, then using the INFILE features of MySQL (I assume that's what you are using) to get the data into the database.

  13. #13
    SitePoint Addict
    Join Date
    Aug 2007
    Location
    GR
    Posts
    352
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by php_daemon View Post
    Some notes,

    There's more to csv than just commas, so I'd suggest using fgetcsv to read it.

    As far as the size, 40k is not that much and running it on daily basis won't crash it. But if you do not need any pre-processing, you probably will not want to read it into memory (array), rather just add to database as you read from file.
    I'm knit-picking but, without using arrays first (memory) you get a hold of the file much longer (locked) + you hold the connection object longer which in turn doesn't let you run an optimized insert (INSERT INTO tbl VALUES (),(),(),()...() <-- semi-colon

  14. #14
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by _Steve_ View Post
    I'm knit-picking but, without using arrays first (memory) you get a hold of the file much longer (locked) + you hold the connection object longer which in turn doesn't let you run an optimized insert (INSERT INTO tbl VALUES (),(),(),()...() <-- semi-colon
    Point taken, I asked for it.
    Saul

  15. #15
    Pragmatic Programmer halfasleeps's Avatar
    Join Date
    Feb 2006
    Location
    Altoona, PA. USA
    Posts
    1,945
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok guys I finally got the data-feed set up and when I run the script I get this:

    Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 35 bytes) in /home/httpd/vhosts/sussexjewelers.com/httpdocs/diamond_data/get_data.php on line 9

    line 9 is:
    $tmp_arr = explode(',', $line);

    Please advise on what I should do now?
    Altoona Design
    Freelance Flex developer for hire.
    ActionScript Programmer with 8 Years Experience.

  16. #16
    Pragmatic Programmer halfasleeps's Avatar
    Join Date
    Feb 2006
    Location
    Altoona, PA. USA
    Posts
    1,945
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok i did it all in the first callout without loading the content into one large array first and it worked.
    Altoona Design
    Freelance Flex developer for hire.
    ActionScript Programmer with 8 Years Experience.


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
  •