Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 8 of 8
  1. #1
    Senior Member RDesignista's Avatar
    Join Date
    Feb 2012
    Location
    Coconut Tree City
    Posts
    822
    Member #
    30921
    Liked
    123 times
    Hi,

    I don't know if this is possible, but this is what I want to do: I want to add items to my store. Instead of adding it via admin GUI, I want to do it via the SQL file. Of course this is riskier because there are dependent items and one single type or missing comma can cause an error, but I feel comfortable with data and want to speed things up.

    Here's what I thought I could do:

    1. MySQLadmin: export .sql file
    2. Edit .sql file data to include new records
    3. Import .sql file (would overwrite current)
    4. Store would reflect new changes in data!

    However, I keep getting a message during import that the table already exists (because in the exported file, the first functional line is : " CREATE TABLE `products` ( ". I feel like this makes no sense, since I am just trying to replace what is already there!

    Advice on this would be helpful. I would prefer this method, but if not possible, a similar one would be just as useful. Thanks.

    -R-

  2.  

  3. #2
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,656
    Member #
    1234
    Liked
    137 times
    As you're finding out, you can't create a table that already exists. You would need to delete the existing one in order to replace it.

    Having said that, I'd stick with the GUI, or revamp the GUI to make it more streamlined.
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  4. #3
    Senior Member Webzarus's Avatar
    Join Date
    May 2011
    Location
    South Carolina Coast
    Posts
    3,322
    Member #
    27709
    Liked
    770 times
    What I normally do is, after exporting a working table... Make a copy... Keep it in a safe place as not to make any changes at all to it...

    Open the copy you want to modify and save all the changes.

    If you have access to the online myphpadmin console...

    Login, select the schema... Enter drop table (tablename)

    Then run the SQL import...

    If you know enough about the SQL language ... You could use a series of insert commands that would just add more items to the existing... Either by uploading the file or pasting the entire string into the myphpadmin editor.

  5. #4
    Senior Member RDesignista's Avatar
    Join Date
    Feb 2012
    Location
    Coconut Tree City
    Posts
    822
    Member #
    30921
    Liked
    123 times
    Hey guys,

    Thanks for the help. I was able to figure it out. In hindsight, my question seems pretty dumb.
    I do have one more question though... how does one determine dependencies between tables? For example, if I replace the "products" table, that will mean I need to replace the "products_description" table as well, which will mean I need to replace the "product_category" table also.

    -RON

  6. #5
    Senior Member RDesignista's Avatar
    Join Date
    Feb 2012
    Location
    Coconut Tree City
    Posts
    822
    Member #
    30921
    Liked
    123 times
    For anyone else that's reading this, here's FAST-POPULATING PRODUCTS IN A SQL DATABASE 101 (I'm playing with an installed Zencart 1.5 and have no experience with MySQL in this example):

    1. Go into mySQL via control panel. You will then be in MySQLAdmin, the place to edit your database.
    2. On the left will be tables that your shopping cart has plugged in during the installation process and from any items added via shopping cart admin module.
    3. Find the table named "product" (or whatever one you need to edit) and click on it. On the next page, click "export." You want to export it into a .sql file.
    4. Open the .sql file. At the top should be something that says "CREATE TABLE" and a list of attributes. There will be some line breaks afterwards and then there will be the records in this particular table.
    5. To add records, copy the format of the current records (the commas separate the attributes, which can be found under "CREATE TABLE").
    6. After you are finished, save a copy and go back to MySQLAdmin.
    7. Click on the table you are editing. On the next page, click "drop." This means to delete the information.
    8. Next, click import and upload your edited .sql file.
    9. If you made a mistake, it will show an error. Most of the errors I had were due to sections without values. You have to type NULL there. Also, if you have an apostrophe ' in your information, it needs to be "escaped" (set as data and not a language command) by preceding it with \ . Otherwise, it will mess up the .sql read. I recommend you edit your .sql file with Notepad++, so you can easily find the areas because it will color code things. If you have an error, you will need to drop the table again, as the command "CREATE TABLE" will create a new table everytime you have an unsuccessful import.
    10. After you finish, you may also need to edit other tables whose information is dependent on the information in your edited table. For example, my "product" table is associated with "product_description". They are connected via "primary key," which in my case is "product_id." If you do not edit the "product_description" table as well, the item won't show up in your shopping cart list of items.

    Finally, I want to recommend a combination of Microsoft Excel and Notepad++ for a job like this. Although you can just use Notepad, MySQL admin offers the option to export .sql files in .xls format. This will automatically create headers and separate data by grid (instead of commas in .sql files). If you are handy with Excel, this will accelerate your production incredibly, due to things like "Freeze pane", "concatenate command", and being able to fill in entire columns in seconds if they are all the same value. After you are finished, you will simply concatenate your data into the same format that you see in the .sql file. Copy those cells and "paste special" as data. Then paste that into your .sql file.

    Ok, sounds difficult as hell, but it's pretty straight-forward once you successfully do it for the first time like me. :dance:

    -R

  7. #6
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,656
    Member #
    1234
    Liked
    137 times
    Why do you want to do it manually though?

    Also, there's no need to delete and replace tables when you can update them with UPDATE.
    http://dev.mysql.com/doc/refman/5.0/en/update.html

    Regarding table relationships:
    http://en.wikipedia.org/wiki/Entity-...onship_diagram
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  8. #7
    Senior Member RDesignista's Avatar
    Join Date
    Feb 2012
    Location
    Coconut Tree City
    Posts
    822
    Member #
    30921
    Liked
    123 times
    Quote Originally Posted by Wired, post: 237374
    Why do you want to do it manually though?

    Also, there's no need to delete and replace tables when you can update them with UPDATE.
    http://dev.mysql.com/doc/refman/5.0/en/update.html

    Regarding table relationships:
    http://en.wikipedia.org/wiki/Entity-...onship_diagram
    Wired,

    I am doing it this way because I have no idea of MySQL commands. I am simply analyzing how data is laid out in .sql files and then creating my own data in the same format.

    I will take a look at that link though. At first glance though... looks like Hebrew to me. Thanks.

    -R

  9. #8
    Junior Member SimplyWebsites's Avatar
    Join Date
    Jul 2012
    Location
    Leicester
    Posts
    27
    Member #
    32160
    Create a backup of the old table

    CREATE TABLE backup_products SELECT * FROM products;

    This would create a copy of the table then you can just delete the old one and reimport.


Remove Ads

Posting Permissions

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

Search tags for this page

edit sql
,

edit sql file

,
edit sql files
,
editing an sql file
,
editing sql file to remove table
,
how to edit an sql file
,
how to edit sql
,
how to edit sql database file
,

how to edit sql file

,
sql file editor
Click on a term to search for related topics.
All times are GMT -6. The time now is 10:01 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com