Editing A MySQL Database

7 replies
One of my websites has people signup for a service and the Country selection was unfortunately never set up properly. Instead of beginning with a complete list of countries, my programmers provided a very limited list to start and an Admin function where I can type in additions. BUT, this has resulted in a drop-down option list completely out of alphabetical order.

Someone has provided me with a file "countries.sql". In my hosting control panel >Databases >phpMyAdmin I've located and clicked on the main database for the website and I see the table in question: tblcountry and clicked to browse it.

But, without guidance, I'm reluctant to progress any further.

Do I now click to Empty it, then browse and locate the file "countries.sql" on my computer and simply import it? These seem to be the logical steps, but I've never before messed with MySQL databases and I don't want to screw anything up.

Any advice would be greatly appreciated.

Thanks
#database #editing #mysql
  • Profile picture of the author Manfred Ekblad
    You make me nervous :p

    Before you do anything, do you have a backup? If yes, have you confirmed that the backup is working by restoring it to another database?

    If not, I wouldn't mess with a database for a live website if you never used MySQL and if you don't know exactly what you are doing.

    You're right about the basic concept though. Depends a bit whats in that .sql-file, the import might stop after the first lines if it tries to create a new table. Without knowing the exact contents of the .sql-file it will be a bit risky to give you any advice.
    {{ DiscussionBoard.errors[2052159].message }}
  • Profile picture of the author theIMgeek
    The potential trouble with this is that it seems unlikely that your countries.sql file is a match for your actual database table.

    You see, an SQL file would run through all it's records, trying to put "Afghanistan" into a column marked "country_names" (for example) but it would run into errors if your database table used the name "countries" instead of "country_names".

    So, it may not be so cut and dried as a simple SQL import.

    On another note, are you happy with the countries you currently have listed? It would be a simple matter in the website code to sort those into alphabetical order. No need to replace the data, unless it's lacking.

    -Ryan
    Signature
    FREE WSO: Protect and Automatically Deliver Your Digital Products

    Ask the Internet Marketing Geek
    <-- Happy to help with technical challenges
    MiniSiteMaker.org <-- Free software to make your mini-sites fast and easy
    {{ DiscussionBoard.errors[2052233].message }}
    • Profile picture of the author AnniePot
      Hmmm.... I'm glad I was cautious - I didn't think it could be that simple. Actually a programmer has PMd me and offered to help so that's the obvious route I should take.
      Thanks for your input though :-)
      {{ DiscussionBoard.errors[2052323].message }}
  • Profile picture of the author stma
    If you paste in the chunk of code here that lists the countries we can fix it so it's alphabetical very easily.
    {{ DiscussionBoard.errors[2053175].message }}
  • Profile picture of the author Frank Wright
    Hi AnniePot.

    You have a tricky situation because you have to make sure that the database table name and columns in your countries.sql file matches tblcountry in your database. Otherwise, dropping the table and importing the countries.sql would not work correctly for you.

    I would suggest that you do the following and post the results here so that I can take a look.
    1. In phymyadmin, click on on tblcountry.
    2. Click on the "Export" tab.
    3. Uncheck the "Data" option.
    4. Click on the "Go" button.
    This would output the sql script showing the structure of tblcountry. Copy and paste the output here.

    Next, open the countries.sql file using any text editor, copy and paste the first 10 lines of code here.

    With the 2 output, I would be able to advise you better.
    {{ DiscussionBoard.errors[2054220].message }}
    • Profile picture of the author AnniePot
      Thanks everyone for your advice. I'm playing safe and getting it fixed by an expert :-)
      {{ DiscussionBoard.errors[2056509].message }}
  • Profile picture of the author Manfred Ekblad
    Whew... I can breathe again

    Good luck with your expert, let us know if there is something else we can assist you with!
    {{ DiscussionBoard.errors[2057803].message }}

Trending Topics