18/11/16

MySQL PDO project to Manage database, table and records


This is a simple program developed using basic functions of PHP - PDO for managing MySQL database. To keep this simple various extra features like creating records, adding tables, executing SQL etc are not included. 

While working on any script user can keep this to monitor record addition, deletion etc. 

This script is developed keeping in mind the universal requirements where user need not write table name , column name etc to get the records.

How to use this script.

You must have installed PHP 5 with PDO support and MySQL ready with database connection
Place all the files in a folder ( default is plus-mysqladmin ) 
Open config.php file and enter your MySQL login details. 
Now open index.php file. 

Features of the script

Based on the MySQL privilege you have entered in config file you can browse and check tables and records stored in MySQL server. This script is developed by using various functions available in PHP- PDO so user need not enter them manually. For example while showing records of a table the columns names are read first and shown in header cells of the table. Number of columns to be shown are also taken dynamically by using the table name. 

Home page will list out all the system details like MySQL version, Operating system etc. 
Top menu will show link to Databases. On visiting this page you can see all the databases created on your MySQL server. 
Listing database
To know more on listing database read show database query.

Click the database you want to see the details. You will get full list of tables you have created. Top navigational menu you can use to return to database page to switchover to a different database. 
Listing all tables of a database
After selecting a database the show_table.php page will list out all the tables created. 

You can read more one how to List tables of a database.

Along with the table list the total number of records present in the table will be displayed. 
There will be one link against each table to display all the records of the table. 
Read more on Displaying columns of a table 
Showing records of a table
One more link is there to show the structure of the table. ( or column details ) 
You can read more on how to show structure of a table
Structure of a table
User can delete all the records of a table by clicking a link. Before deleting a confirmation dialog box will be displayed. Similarly on confirmation users can drop or delete a table. 

Safety & security

This script is for testing or development environment only. If you are using in production system then take sufficient care to restrict the access by adding .htaccess password protection to the directory. There are features like emptying records and delete tables within this script so exposing them to others may damage your site.

Where to use this script

While working on a project developers have to frequently check the database after any updation, insertion or deletion of any record. By using this script you can easily navigate between different database and tables to watch the changes. 

There are different pages used for this script. Here are them with purpose. 

config.php : Keep your MySQL login details. All files will call or include this file to establish database connection. Read here to learn more on Database connection. 

menu.php : This file is linked from all other files and it displays the top navigational menu. This file receives all parameters from query string and prepares the variables. 

index.php : This is the default page which will read operating environment and display them. The query used for this is here .

"SHOW VARIABLES LIKE '%version%'"

The main code part is here 

database.php : This file list out all the database created in MySQL installation. The query used is here

SHOW DATABASES

By looping we display all the database with link to display tables. Here is the code. 

To know more on listing database read show database query

show-tables.php This file receive the name of the database from query string ( through menu.php file ) and displays all the tables present inside the database. Here is the query

SHOW TABLES from DATABASE



You can read more one how to List tables of a database.

While listing tables by using loop we will read out the number of records present in the table. By using the name of the table we will keep links to display all the records and structure of the table. 

We will use another two links (for each table ) which on confirmation from user will delete records and delete the table. 

show-records.php This page will receive database name and table name from query string ( menu.php ) and then display all the records. To make it universal, the script will find out the column names, number of columns from the available table name. Using this by just passing the table name the script will display all the records of the table. 

You can read how to display columns with records of a table 

show-structure.php To display column type and its length we will use INFORMATION_SCHEMA available in our MySQL server. Here is the query for this.

SELECT COLUMN_NAME,COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = '$table_name' AND table_schema = '$database_name'

In above query we will use our database name and table name by collecting from query string. 

You can read more on how to show structure of a table.

How to use

Download the zip file at end of this page. 
Open config.php file and enter database details ( root access ) 
Open the index.php page to see the MySQL details. 
Inside the file show-tables.php the line saying include "drop-empty-table.php"; is commented ( removed ) to prevent accidental removal of records or deleting of tables. You can use this line after taking sufficient care. ( preferable in development environment )

Feel free to post your comments below ( or use the forum ) for any clarification or suggestion.

0 nhận xét: