 |
|
|
|
Up to 87% savings on Geeks.com!
SECRET CAMCORDER
Prices too low to show! Expires 8/31 Take 10% Off All MCM Electronics Purcases!
|
By Sergey Skudaev
In this tutorial you will learn how to use phpMyAdmin to manage MySQL database. How to create database, tables, how to alter table, add fields, insert records, backup table and backup database.
phpMyAdmin is an open source php script. Download phpMyAdmin from www.phpmyadmin.net and unzip it in "htdocs" directory.
On my PC it is C:\Apache\Apache2\htdocs. Start your browser and type in the Address:
http://localhost/phpMyAdmin/.
phpMyAdmin home page displays.
I got error message, because I forgot to start MySQL. I started MySQL and refreshed browser page. A new error message displays:
The whole text in the message is:
phpMyAdmin tried to connect to the MySQL server, and the server rejected the connection.
You should check the host, username and password in config.inc.php and make sure that
they correspond to the information given by the administrator of the MySQL server.
Ok, let us open config.inc.php file and look at it contents. The config.inc.php file located in phpMyAdmin directory. Open the file in TextPad or Notepad and scroll down to Server Configuration.
/**
* Server(s) configuration
*/
$i = 0;
// The $cfg['Servers'] array starts with $cfg['Servers'][1]. Do not use $cfg['Servers'][0].
// You can disable a server config entry by setting host to ''.
$i++;
$cfg['Servers'][$i]['host'] = 'localhost'; // MySQL hostname or IP address
$cfg['Servers'][$i]['port'] = ''; // MySQL port - leave blank for default port
$cfg['Servers'][$i]['socket'] = ''; // Path to the socket - leave blank for default socket
$cfg['Servers'][$i]['connect_type'] = 'tcp'; // How to connect to MySQL server ('tcp' or 'socket')
$cfg['Servers'][$i]['extension'] = 'mysql'; // The php MySQL extension to use ('mysql' or 'mysqli')
$cfg['Servers'][$i]['compress'] = FALSE; // Use compressed protocol for the MySQL connection
// (requires PHP >= 4.3.0)
$cfg['Servers'][$i]['controluser'] = ''; // MySQL control user settings
// (this user must have read-only
$cfg['Servers'][$i]['controlpass'] = ''; // access to the "mysql/user"
// and "mysql/db" tables).
// The controluser is also
// used for all relational
// features (pmadb)
$cfg['Servers'][$i]['auth_type'] = 'config'; // Authentication method (config, http or cookie based)?
$cfg['Servers'][$i]['user'] = 'root'; // MySQL user
$cfg['Servers'][$i]['password'] = ''; // MySQL password (only needed
By default I had root user without password. To protect my database I set password aspirin. That is why phpMyAdmin cannot access my database. Since it is not good practice to use root user in php code let us created a new user.
Read One hour MySQL Tutorial to learn how to create users and set passwords to MySQL.
Login in MySQL as root user.
C:\mysql\bin>mysql -u root -paspirin
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 3.23.58-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
Create a new user
mysql> GRANT ALL PRIVILEGES ON *.* TO 'sergeys'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.25 sec)
mysql>
Now type user name and password in Server Configuration
$cfg['Servers'][$i]['user'] = 'sergeys'; // MySQL user
$cfg['Servers'][$i]['password'] = 'password'; // MySQL password (only needed
Save file. Refresh browser page. phpMyAdmin home page displays:
Click Databases drop down list. The list of databases displays:
Select your database and database tables (if any) display:
If no table created yet you can create one.Type table name and number of fields. Click Go button. Fields display for data entry
Enter fields names and attributes:
Click Save button. Table created
Here you can edit table fields. Click a pencil icon and change a field name or an attribute. If you want to delete a field click X icon.
Click Insert link to insert data in the products table. Table fields display for data entry. Enter your values. Enter 0 in auto-increment field.
Click Go button. Click Browse link. Inserted records display
Click SQL link. SQL text area displays for SQL query.
Edit query:
And press Go button. Found record displays.
Click Structure link. Table structure is displayed
If you want to add one more field click Go button next to Add new field label.
Enter field name, data type and other attributes The field will be added to the table. Since table has records you cannot add field in which Null is not allowed. That is why you have to enter Null in the Null field. Click Save button. New field will be added to the table.
To back up table data click Export button. Scroll down to "File name template:" field.
Enter table name, mark Save as file check box and click Go button Save dialog box displays:
Click Save button
Save products.sql file in the backup folder. Download complete Dialog box displays
Click Open button to see how records are saved.
Products.sql file contains create table statement and insert query for each record. To restore data just copy insert query in phpMyAdmin SQL text area and click Go. The records will be inserted in the table.
I did not describe all features of phpMyAdmin, but I give you a starting point.The rest you can learn on you own.
If you have any question or comments please post them in forum
Please rate the tutorial