phpMyAdmin
An introduction to the phpMyAdmin program.
Introduction
phpMyAdmin is a popular and free administration tool for MySQL that will allow you to do any operations you need to. You can also write SQL code to do this but I'll show you how to do it with phpMyAdmin so the beginners can get up and running.When you first log onto phpMyAdmin the main part of the page has tabs that show the different options you can use to format the database - like creating new tables. The left column will show a list of your tables. If you click on one of the tables the main page will show a different set of tabs that format each individual table.
I'm going to give tutorials on some of the tabs here (I'm going to review them out of order). It would be helpful to log onto your phpMyAdmin and follow the instructions below to get aquainted with administering your database. I should note that I am assuming that you have a database already created and named. If you don't then you can log onto the admin section of your hosting account and create a database.
After your database is created you will have a database that is all set to go but it won't have any data in it. In order to insert data into a database you will need to create a "table" within the database. A "table" is simply a section of the database where different categories of data go. The first thing we will do is look at the "Operations" tab because that is where you create tables.
DATABASE TAB - "Operations"
To create a table simply type in the table name and number of fields (columns) you'll need. You can map out how many fields you'll need before you do this but if you end up needing more later then you can add more later.
After you click "Go" then you will be asked to give detail about the columns by supplying 3 types of information for each column. First give the name of the column. Make the name of each field a single phrase. This means there shouldn't be any spaces in the name. If the name has more than 1 word, like Phone Numbers, then you can separate the words using an underscore (Phone_Numbers) or use CAPS for each word (PhoneNumbers) or make it the words joined (phonenumbers). The second thing you need to do is set the columntype. Column types require a longer explanation so I get into them in detail in the next page. The third thing you need to do is set the field length. This is related to the column type.
An important technique in setting up a table in a database is to create the first column as an "ID" column. You should do this by default on every table you create. This column will assign a unique ID to each row that in created in the table. This allows the database to associate a unique identity to each row. To do this you will want to name the column "ID" or something related like "userid". Then you will want to set the column type as integer because each ID will be a number. Then you will want to format the column to be an "auto-increment" column. This means that the first row entered will automatically be assigned a value of 1 and each row inserted after that will be given a value of 2, the next 3, etc. The next thing you will need to do is set this column to be the primary key of the table. Don't worry about what this means for now. After you are done then click "Save" or click "Add" if you want to add columns which you forgot.
The next option within the Database Operations tab allows you to rename the database. If you have an empty database then you can do this but if this database is already being used in your site then you'll have to rewrite your scripts to point to the new name or you'll get errors.
The next option will allow you to copy the database to another database. You can copy only the structure of the database (the columns and their formatting), only the data in the database, or both. You may need to check "CREATE DATABASE before copying" if the database doesn't already exist.
DATABASE TAB - "Export"
This option allows you to export and download all the data in the database. First, you need to select the tables you want to export and then select what format you want them downloaded in. If you plan on editing it the file then export it in CSV for MS Excel or just regular Excel format (you can save the Excel file as a CSV later).
Then you'll want to name the exported file and probably click "Save as file" so you can download the file to your computer. If you don't click that then the file comes up inside phpMyAdmin. If it is a big file then it will take forever to load and may even crash the phpMyAdmin program. Then you can decide to compress the file or not by zipping it. I have had trouble unzipping exported database files before so if you have this trouble then jsut re-download it unzipped.
DATABASE TAB - "Structure"
The "Structure" option allows you to view a list of your tables while giving you a list of operations you can perform on each table. "Browse" lets you scroll through the different rows of data entered into the table. "Structure" lets you view the columns and colmun types of each table. "Search" allows you to search for each table for specific information. "Insert" lets you manually insert a record into the table. "Empty" will empty out the contents of the table. Don't worry about hitting this accidently as there is a confirm delete page. "Drop" empties out the table contents as well as also deleting the table itself. There is also a confirmation button for this.There is also information listed about each table. "Records" shows you the number of entries (rows) in the table. "Type" is the table type. This will be "myISAM" by default but there are a couple of other options you can switch it to if you need to. "Collation" refers to the character set used to store data in the text fields. The default collation in MySQL is latin1_swedish_ci. phpMyAdmin allows you to edit the collations of your database if you want to support non-English languages. "Size" refers to the amount of memory that particular table takes up. "Overhead" refers to the amount of dead space taken up in your tables by deleted rows and data. If your overhead becomes too big then you should clear it out.
TABLE TAB - "Browse"
"Browse" lets you scroll through and view the records in a table while giving you links to edit or delete the record. You can also sort by column headers as well as insert a new row and exporting the table.TABLE TAB - "Structure"
The "Structure" option for each table is similar to the "Structure" option for the database as it shows you different information about each column and operations to use on each column. Below the list of columns is the option to add or subtract columms.- "Field". The "Field" column tells you the field name. If it is the primary key then it will be underlined.
- "Type". This tells you the column type with the column length in parathesis. For example, "int(6)" means an integer column that only allows 6 digits to be stored. Data and text columns won't have set field lengths but you can learn more about this on my columntypes page.
- "Collation". This tells you the character set and you shouldn't have to worry about this unless you are dealing with foreign languages.
- "Attributes". Don't worry about this column for now.
- "Null". This will option will allow you to set the optopn to allow NULL values in that column.
- "Default". This allows you to ste a default value for the column.
- "Extra". This will show you if the column is set to auto-increment.
- "Browse distinct values". This option will tell you all of the unique value within that column.
- "Change". This option simply allows you to change any of the other options listed here.
- "Drop." This option will delete that column and all the data associated with that column.
- "Primary." This option will allow you to set this columnas the primary key within that table.
- "Unique". This allows you to make a field unique.
- "Index". This allows you to make a field an index field.
- "Fulltext". This options allows you to add a FULLTEXT index to the column.
TABLE TAB - "Import"
Most people who import a file into a MySQL database will be import a file that in CSV (comma-separated files) format. First, select the file to import. Then you can leave the character set alone. Then go down to the file format options. Importing data into a mySQL table is very easy but there are a couple of things that need to pointed out so I will assume that is the way you will do it. After you click "CSV" you will be given more options.There is an option to over-write existing records. If you don't check this and there are already similar records in the table then you may get an error when you try to import. The same goes for "ignore duplicate rows." Below these options is an option for "Fields terminated by". There will be a semicolon entered as a default option. You will want to change this to a comma because the fields in a comma-seperated file are defined by commas.
Bookmark this page:
|
