How to go in and edit SQL like it's an html file?

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-

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.

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

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.

-R

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

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.