SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Jul 2004
    Location
    Media
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question updating table data in phpMyAdmin

    I have a MySQL table called book_info that contains all of our companie's book data. We want to revise the categories and subcategories for our titles, so I did a dump into CSV for Excel. Now that I've fixed a good portion of the categories and subcats, I'd like to update just those two columns.

    Main table: book_info
    Unique Identifier: book_isbn
    Category: category_code
    Subcat: category_id

    I know there must be a way to update the book_info table by using the book_isbn as the matching field, but I don't know enough about phpMyAdmin or MySQL to write a script to do this. Can anyone help please?

    Much appreciated,
    Ian

  2. #2
    SitePoint Addict pointbeing's Avatar
    Join Date
    Jun 2004
    Location
    London, UK
    Posts
    227
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If your Excel spreadsheet is in exactly the same layout as the database table, you might try it this way:

    1) Take a backup of the table and make sure you know how to restore it if anything goes wrong (this is always step 1, whatever you're doing )

    2) Drop the table and re-create it without any data

    3) Save the file as CSV (and not .xls)

    3) Use the LOAD DATA INFILE syntax to import the data from the file

    This could do it.

    Though, if the spreadsheet is now in a different layout to the db table - you may need to script this with a programming language such as PHP - reading the file line by line and executing SQL UPDATE statements for each one.

    Let us know how you get on

  3. #3
    SitePoint Member
    Join Date
    Jul 2004
    Location
    Media
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, so if I understand correctly, there's no update syntax I could use to match up the book_isbn field and import the category_code and category_id fields? This is something Filemaker 5 does in its Import menu, so I had hoped SQL had a script, either in the LOAD DATA INFILE or otherwise, that would do the same thing. If I did format the upload file in the exact column format as the book_info table, could I then just import the updated data in the category_code and cateogory_id fields?

    Speaking of the LOAD DATA INFILE, what syntax do you use for a file on a local drive, say C:, to upload data from?

    Many thanks for the help!

    Quote Originally Posted by pointbeing
    If your Excel spreadsheet is in exactly the same layout as the database table, you might try it this way:

    1) Take a backup of the table and make sure you know how to restore it if anything goes wrong (this is always step 1, whatever you're doing )

    2) Drop the table and re-create it without any data

    3) Save the file as CSV (and not .xls)

    3) Use the LOAD DATA INFILE syntax to import the data from the file

    This could do it.

    Though, if the spreadsheet is now in a different layout to the db table - you may need to script this with a programming language such as PHP - reading the file line by line and executing SQL UPDATE statements for each one.

    Let us know how you get on

  4. #4
    SitePoint Addict pointbeing's Avatar
    Join Date
    Jun 2004
    Location
    London, UK
    Posts
    227
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by schifferbooks
    Ok, so if I understand correctly, there's no update syntax I could use to match up the book_isbn field and import the category_code and category_id fields? ... If I did format the upload file in the exact column format as the book_info table, could I then just import the updated data in the category_code and cateogory_id fields?
    I can't see a really simple solution without scripting it. I can see exactly why you only wish to update those columns (after all, they're the only ones you've changed, I guess?). On the other hand, if the other columns have not been changed at all, there's no harm in overwriting them all with a LOAD DATA INFILE. Problem comes if you have a different column structure in your spreadsheet to what you have in the table (and I can't see your spreadsheet or your table from here so I don't know how much work that would involve to get them to be the same)

    Having said that, maybe there's a simple solution that I'm missing - maybe someone else here will have suggestions?

    Quote Originally Posted by schifferbooks
    Speaking of the LOAD DATA INFILE, what syntax do you use for a file on a local drive, say C:, to upload data from?
    Simplest possible case:

    Code:
    LOAD DATA INFILE 'C:\path\to\file.txt' INTO TABLE My_Table
    Though you'll possibly need to tweak the field seperators - here's the manual entry:
    http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html

    Note that this assumes that the file and the database are on the same machine. If not, you would need to copy the csv file onto the machine where the db is and work from there.


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
  •