Manage MySQL Database Using phpMyAdmin
Looking to take the management of your MySQL database into your own hands? Not sure where to begin? It isn’t as difficult as you would think.
Many hosting providers, cheap hosting included, offer many useful database management tools you can rely on to get the job done. If yours does not, don’t worry! There are a variety of third-party applications you can install on your computer instead. Today, let’s walk through creating a database and managing it using phpMyAdmin.
phpMyAdmin is one tool many hosting providers offer in order to administer your MySQL database via the Web. Although you create the database in cPanel or Plesk, phpMyAdmin allows you to drop databases, create or alter tables, manipulate fields, add or delete fields, manage keys, execute SQL statements, and export data into other formats, and features a powerful user interface.
The open-source software, maintained by The phpMyAdmin Project, is available in 64 different languages. To access phpMyAdmin using cPanel, simply log in to cPanel and click on the phpMyAdmin icon in the Databases section. In Plesk, click the ‘Website & Domains’ icon, then the ‘Databases’ icon. Choose the database you wish to manage by clicking on it, and then click the ‘Webadmin’ icon. This will take you directly to phpMyAdmin.
Creating A Database
Before you can manipulate a database, you have to create it in MySQL. This cannot be done using phpMyAdmin. To do this, you need to go to cPanel, choose ‘MySQL Databases’, and head to the ‘Create New Database’ box. Give the database a name in the ‘New Database’ field, and click ‘Create Database.’ The actual name of your database will contain your cPanel username. So if your username is ‘fox’ and you’ve named the database ‘customers’, the actual database name will be ‘fox_customers’.
Great, you have a database! Now, let’s add a table. Enter the name of your table and the number of fields you want it to contain, and click ‘Go.’
You should see a screen where you’ll enter field names and properties.
- TYPE. This will assign a data type to each field.
- LENGTH/VALUES. This sets the length of the field, and all possible values the field can contain.
- COLLATION. Choose the data collation for the fields.
- ATTRIBUTES. There are four choices: binary, unsigned (positive numbers or 0), unsigned zerofill (positive numbers or 0 with leading zeros), or on update current_timestamp (the value of a field has a timestamp for a default value, automatically updated).
- NULL. States whether or not the field is allowed to be NULL.
- DEFAULT. Here, you set a default value for the field.
- EXTRA. This defines whether or not the field value is auto-increment, and the buttons displayed below let you define whether or not there is an Index defined, allowing you to specify the Index type.
- COMMENTS. Add any comments about the field. It will be included in the database sql code.
After assigning field properties for all fields contained in the database, there is a text box for Table Comments. Choose the MySQL Storage Engine and Collation, and click the ‘Save’ button. You’ll see after the save button ‘Or Add (text box) Column(s)’. Use this to add any additional fields prior to saving, clicking the ‘Go’ button instead of ‘Save.’
Populating The Table With Data
Click the ‘Insert’ tab, enter data in the proper fields, and click the ‘Go’ button to save it.
Managing Your Database
phpMyAdmin makes the process of managing your MySQL database a breeze! Once you’re in the application, you’ll see a list of the various tables, what actions are allowed with each one, how many records they contain, the storage engine, collation, size of the tables, and overhead.
All of the possible actions you can perform to a table are:
- BROWSE – The only tables you are able to browse are the ones that contain records. Click the Browse icon, and a window pops up displaying the list of records contained in the table. Click the ‘Pen’ icon to edit the given record.
- STRUCTURE – This displays the structure of the table: names of fields; types; collations; attributes; default values; and whether or not a field can be NULL. Search for specific values by clicking on the appropriate action icon, edit the structure of a field, or delete a field altogether. Also, you are able to define indexes as Primary, Unique, Index, and Fulltext. Look to the Index area to find all indexes assigned to the table, as well as the fields for which they are set. Additionally, you can edit or delete them. The Structure screen also displays the Space Usage and Row Statistics.
- SEARCH – Use this action to initiate a search query for the given table. To do this, you can either write the WHERE clause, or put to use the ‘query by example’ function. Click the ‘Go’ button to execute the query. Example: you want to see all of the records that contain a field value beginning with ‘d’. Select the fields you want to show, choose the ‘LIKE’ operator from the drop-down menu, and input the field value ‘d%’. Click ‘Go,’ and your results will be displayed.
- INSERT – This action will insert records into your table. Fill in all values and click ‘Go’ to perform this function.
- EMPTY – This empties your table entirely, removing all data but saving the empty table.
- DROP – Use this to delete the entire table and all associated records.
Now, you are managing your own MySQL database! Did you think it would be this easy?
internet database with sql