What is Open MySQL Admin?

Open MySQL Admin is a sophisticated Administration Tool written in PHP for administration/manipulation of MySQL Database through Internet browsers. It allows admin to perform almost every SQL function from within an easy and quick layout. Its remarkable feature is that, it does not send loads of data to client browser, and makes management so fast and easy especially because of having GZIP enabled.

Furthermore it contains tips for beginner and moderate level administrators, almost everywhere in the layout. It also provides one-click functions like Repair, Drop, Empty, Select, and etc within the left side of Database & Tables view.

Open MySQL Admin claims to be ideal environment for MySQL administration for those who have been using lazy MySQL managers as well as for those who fear to touch SQL engine. Spend about 20 minutes reading this document and get life-time ease.

Requirements of Open MySQL Admin?

Recomended version of MySQL server is 3.23.56 or higher in order to run Open MySQL Admin perfectly.
Open MySQL Admin was tested on PHP version 4.2.3 so higher version may accomodate it easily.
Open MySQL Admin occupies about 300kb to 400kb of disk space, where 60% of the size is ocupied by the images used in documentation.

Salient Features

Open MySQL Admin provides more and more shortcut functions to save much of your/server time, such as, Copy / Move Table to other/same database even if table is of Gigabytes in size, Copy / Move all tables of a database instantly alongwith another utility function of database Cloning.

In set of table level functions Open MySQL Admin also provides a big list several programming languages you can dump your SQL code for, dump structure or structure and code, such as MySQL itself, PHP, Perl, Python, CGI, C++, and even creates little backup in CSV format.

Admin can even download the tables in Microsoft Excel Worksheet .xls file format instantly.

It also creates previews of structure and data records in an organized manner suitable for printing.

Furthermore it generates sophisticated forms for your tables which can directly be published over the web without any fear, form options include Direct Mail on form submission, Direct entry to Database, or both.

While insertion of new row(s)/record(s) it provides easily understandable tips as well as MySQL functions to include directly in final query.

The record selection and searching area Select provides a sophisticated Expression editor to implement Where clause of the query manually within a graphical editor with logical connectors.

The big table level feature is Installer creation; Open MySQL Admin creates easy to use PHP based Table Installers which are the best for SQL migrants.

Open MySQL Admin supports multiple internal languages, for now I have included only one file for English language, but if anyone wishes to have Open MySQL Admin running in his/her own language, s/he must write the language file him/herself. Discussion and question are entertained at www.scriptsector.com.

If your server allows GZIP then average trasnfer is 100 kb to 140 kb per sec (original plain/text/html) on a 56k modem with Dial-up connection.

Further features can be found in detailed documentation.

Basic Configuration to run on website safely (or local computer)


Best thing is that, it works on http easily, using a cookie for admin identification.
If you are using it on localhost (local pc) then edit config.php and set variable $DM_OMA to empty string as shown here

$DM_OMA = "";

and enter the default user and password as shown here

$dbuser = "root"; //user is root you change to whatever you have
$dbpass = "root"; //password is root, you may change it

So that Open MySQL Admin does ask you for authorization all the time.


$dbuser = "";
$dbpass = "";

and set variable $DM_OMA to ".yourdomain.com" as shown below:

$DM_OMA = ".sriptsector.com";

So that the Open MySQL Admin prompts for authorization before entering IDE.
Admin login page preview:

Understanding the Layout


Basically the layout of Open MySQL Admin is divided in two parts. The left side we call is Database and Tables List Browser and the right part of the layout must be called the The Main. The first time you open or refresh Open MySQL Admin, you will see the databases only in left side of the layout. Where tables' names are emerged with these databases as a tree list. You may click image to expand the list of tables in the database and then collapse the list by clicking image . Following image will give you more clear idea of what I am talking about.



IMPORTANT NOTE : Wherever in the layout, you find image , it points to further expandable options. Just click it and options will expand.


There are also some quick/most frequently used functions provided within this left side of the layout. You can see some characters like (C . D ) in front of each database name and [R, D, E, M, I, S] in front of each table name. These functions provide you ease as well as save your time, (defined below).

What are functions (C . D)?
These are database level functions, you can perform these functions simply by clicking them. Where C is for Creating new table in respective database, and D is for Deletion/Drop of database (this action deletes all the tables within the database as well as removes the database itself).

What are functions [R, D, E, M, I, S]?
These are the quick table level functions. Where R stands for Quick Repair Table, D for Delete/Drop Table, E for Empty the table (delete all records from table), M for Modify Structure of Table, I for Insert new row/record in table, and S for Select statement (you may call it Search).

Now, by clicking the Database name will open the operations that can be performed on a database, similarly clicking the table name will lead you directly to Table Structure View and some more functions. Which will be defined later in this documentation.

On top of left side of layout there are links HOME and Documentation, I don't think there is any need to define them. That was all the left side of the layout.

Very First Display in Right side of layout


First option in right side of layout is used for creating new database (if you have permissions to do so), as shown here:



Just enter new name of database to create, and click button 'Create'.

After that, you have option to run MySQL query directly. This is very useful if you have SQL backup of any old database and you wish to restore it. Simply paste the SQL code and click button 'Run query now'. Or if you are good in writing MySQL queries yourself, it will save a lot of your time. This direct query runner must look like this:



Below that you have 'Quick Database Operations' where functions/operations (which can be performed over a database) are listed under each database name. This also saves a lot of time, instead of going onto database page and performing action.

Database level functions

These functions appear almost everywhere within the layout, but detailed list of database level functions appears in right side of layout by clicking the database name.

After clicking the database name you will see a list of database functions in right frame of the layout.

First of all we have 'Run MySQL query', it is a bit different than the first one you saw on very first page. If a table level query is being executed, current database is considered default for that table, where on first page our queries must contain database names alongwith table names.

Secondly we have 'Quick Database Operations', where few of them must be defined for your clarification.

Copy
Copy in database level means to make copies of all the tables in current database. Where you have two options either to copy within same database, or to another database. By clicking Copy, Open MySQL Admin leads you to another page which displays something like image below (assuming Advanced Options are expanded):



First you select the database from the drop-down list shown on right side; secondly if tables are to be copied within same database, the field (with value Copy_of_) must be filled for prefix of all the new tables to be created as copy of existing tables.

Move
Move in database level means to move all the tables from current database to another database. When you click this option, it will lead you to another page where there is a drop-down list of databases excluding the database name you are copying tables from.

Clone
Clone means to create exact copy of currently selected database. This operation creates new database (name of database asked by user), and copies all the tables (of exact structure and data) to newly created database.

SQL Data code
This option dumps down entire database including tables' Structures and their Data and generates few reasonable remarks within the generated MySQL code.

SQL Structure code
This option dumps down entire database including tables' structures and generates few reasonable remarks within the generated MySQL code, which may be executed on any MySQL server in future.

SQL Data+Structure code
This option performs both of above operations and generates single code.

PHP Data code
This option dumps down entire database including tables' Data (not structure) excluding remarks in generated MySQL code. Then it creates an installer type PHP script for you which can be very useful to migrate to other MySQL hosts easily. It uses default admin log to create PHP script for establishing MySQL connection.

PHP Structure code
This option dumps down entire database including tables' Structures excluding Data without any remarks in generated MySQL code. Then it creates an installer type PHP script mentioned above.

PHP Data+Structure code
This option performs both of above operations and generates single code.

Let us move to Table level functions and operations.

Table level functions

Few of these functions have already been defined here. This topic throws some more light on functioning of these options as well as covers some more functions, which appear in the right frame of layout after you click the table name in left part of layout (under database name), as shown below:



We will first define the highlighted part (with red border) of the above shown image, we define each function from left to right.

Repair
It just repairs the table for any kind of key corruption.

Drop
It lets you Delete / Drop currently selected table, but prompts you a message box of [OK] [Cancel] for confirmation before deletion.

Empty
It lets you Delete all the row(s)/record(s) in currently selected table, but prompts you a message box of [OK] [Cancel] for confirmation before deletion of the data. I does no suffer the table structure any way.

Modify
This is a bit advanced option and is not recommended for beginner level administrators. It lets you modify the overall structure of currently selected table. It leads you to a page where each field of the table is brought to you for modification, as shown here:



In this page you may also drop any of the fields by checking the checkbox in left most column of each field under image , filed will be dropped/deleted form table after click over button 'Alter Now'. We will not be demonstrating you the MySQL table structure (, you may obtain further information on field types, length, attributes, and extra at www.MySQL.com).

Insert
This function is used to insert new row/record in currently selected table. After clicking this option it will lead you to a page similar to following image:



This page facilitates you by providing different type of input areas for specific type of fields. NOTE The filed with two images on right side ( and ) is used for integer type of data and acts as a spinbox to increase or decrease value through these small images acting as buttons. For field of type SET it provides you a listbox with capability of multi-select. For fields of type ENUM it provides you radio buttons to select one value amongst defined ones.

In above image you see a small '?' image , it provides you quick tip for the respective field it is located in. It usually appear in the fields with auto_increment.

Under first column you have checkboxes, which allow you to exclude a field and its value while insertion of new row/record (if any of them is checked).

Fourth (4th) column contains advanced options. After clicking the image you will see a list of MySQL functions to apply on the field value while inserting new row/record. This option looks like below, if expanded:



The option 'Display query on next page' is found almost under every function of Open MySQL Admin. It facilitates you to view the final MySQL query created against your last action.

Select
Select statement, used for searching and displaying resulting rows meeting the search criteria. This option leads you to a page similar to following image:



If you are beginner level admin then we recommend you to study the WHERE in SQL first. In 'Where' clause field you enter the condition to be checked while filtering data rows/records.

Then we come to lowest part of this page Sort by, if the checkbox on left side of this option is checked, it will order the resulting rows/records according to selected field, in ascending or descending order by selecting among radio buttons on right side of this option.

If option Fields to Display in Result is expanded, it looks like:



This option compiles the result including only the selected fields. You have noticed that all fields of table are selected in the list, you may unselect unwanted ones.

Now we move back to Advanced Options for WHERE clause. It must look something similar to following when expanded:



This is the expression editor for your select statement. It is skipped if there is even a single character in above mentioned 'Where clause' textbox. This expression editor provides you almost everything you need to make your query perfect. It lets you compare the existing values of fields with the ones you enter also with the help of MySQL functions.

NOTE: the unchanged field expression will not be included in final query. And, if 'L.Connection' Logical connection is left empty and there is an expression following it, Open MySQL Admin will automatically add LOGICAL AND on the required place to make the query complete. Now click button 'Get Results' to run the final query.

Copy
This option allows you to make copy of current table (incluidng structure and data). You may copy it to another database or even make exact copy of it in same database by entering different name of the table.

Move
Function 'Move' allows you to make move current table to another database. Where current database will not appear in the drop-down list of databases.


Rename
This simply allow you to rename a table.

Table level Printing and User PHP Form creation (utility functions)

Now come to Utility Functions, as shown here



We go from left to right defining functions.

Print Preview Structure
This option generates a fancy view of the structure of currently selected table, suitable for printing purpose. Simply click and see what does it generate, which will be similar to following:



Print Preview Data
This option also generates fancy view of the data in currently selected table, something similar to:



Create Use PHP Form
This page creates a full-fledged PHP based User Form for you intended to be filled by others/users. Page looks like this:



This page provides you three options on top of the page, which are so clear, that:
* The final form should send mail only when submitted by user, or
* Only entry will be submitted to database (no mail will be sent), or
* Both of above options (send mail as well as add entry to database).

Below that there are two fields 'Emil Subject' and 'Email Address' which must be filled if the selected option contains mail, otherwise these may be ignored, or set empty.

'Title of the page' is the title that will be displayed on the top of user's browser window, and 'Description' that will be shown on the top of final form page.

Then it comes to 'Fields Display Labels'. These are the labels which will be shown on the left side of each input field on user form. If you wish not to display actual fields' names, then fill out the input textboxes in front of each field name.

Finally 'Last Message', this is the message which will be displayed to the user who will fill the form, after submission of the form. There are some more interesting options, which can be customized after generating PHP form code and reading the commented text in it. Now hit button 'Create Form + Script Now', upload the resulting code as .php file. Before uploading make sure the MySQL database information is correct in the final PHP code (If form is being uploaded to a server other than the one it is created on).

Table level back-up and code generation for PHP, Perl, Python, C++, CGI, and MySQL itself

These options can be viewed in the right portion of layout after clicking the table name (in left part of layout). Then expanding the options of More functions for back-up and code generation .

Under this heading, you have too many options to generate code of your favorite programming language, such as, MySQL itself, PHP, Perl, Python, C++, and CGI. And two more options of table dump in the form of coma delimited format CSV as well as allows you to download back-up of table in the form of Microsoft Excel .XLS file. These options must look as shown in following image:



Table Structure in front of each language name, dumps the table structure and generates directly executable code for respective language. And Table Structure and Data does the same including Data record(s)/row(s) of the table. Clicking MS-Excel XLS Version will directly download an ms-excel formatted worksheet file for you, similar to the Get CSV version, which will prompt you the file save dialogue to save file to appropriate location on your hard-drive.

Table level PHP Installer creation


This option is visible in the same place, and must look like following image:



NOTE : you can only use this function when table is not empty.

This is very useful function if you have to hand-over the database table to third-party. Or you have to migrate to other host. It seems to be of similar functionality of PHP Structure Code and PHP Data+Structure Code, but No, it is not. This option will lead you to another page of following type:



On this page you will be asked to enter page title which will be displayed on the browser window title-bar, 'Description' some useful text which may be helpful for you while running it on other MySQL server. No. of rows to insert per attempt, this option is a bit advance but not hard to understand. Open MySQL Admin creates installer that will not put any burden on the server. So, to avoid any server problems it allows you to make such installer that will run in episodes automatically. If this field is appearing then enter a suitable number of rows to be inserted per attempt/episode.

Enter a suitable message in the textbox in front of Message After Installation is complete which will be displayed when installer has completed the work.

Now we move to the most important part of installer creation Prompt user for database/table information (Default Values). You see following preview after expanding this option:



As per the recommendation, I suggest you to leave the host, database, user, and password fields empty, so that your privacy remains secure even if an unauthorized user gets into running the installer. Although when you run the installer, it prompts user for the host information again.

Option Create Database if not found will add option to the installer to create the named database if it is not already existent on the respective MySQL server. And option Drop/Delete Table if already exists will add code for deletion of the table of same name if it already exists on the MySQL server.

Sophisticated set of steps to Create new table

There are three steps involved in the table creation. This option is found in the page appears when you click 'Create Table' in the database base page, or when you click C in front of database name in left portion of layout.


First Step:

Enter the table name, and number of fields for new table, then click button 'Proceed >', as shown in following image:




Second Step:

In this step you will have to enter field names, select their types, and enter length of fields.





now click button 'Proceed >' to get to last step.


Third (last) Step:

In this step you have to provide the remaining information of the table structure. You can see in the below image, that, the column for fields type, contains set of textboxes for fields of type ENUM and SET.



On submission of this information, type of field 'developer' will be converted to ENUM('Y', 'N') and type of field 'lang_command' will be saved as SET('PHP', 'ASP', 'C++', 'Perl', 'Python', 'CGI').


If option 'Drop Table if already exists' is checked, it will drop/delete the table of same name (if already exists in current database). Further, about rest of the columns please consult MySQL manual found on http://www.MySQL.com.

Now whenever you insert data in this table you will find view as follows:
(It is displayed just to let you know the purpose of ENUM and SET types)



And if you create a (publishable) PHP User Form, will looks somehow like this:

About the Author

Ali Imran is the SEO of Flaxweb Technologies. I Basically developer and use to develop products of my own interest. Usually people like my custom made products because I concentrate on provision of ease as much as possible. I am advanced developer of PHP, Flash MX ActionScript, SWiSH-Max SWiSHScript, C++, and author of a Book on SWiSHScript, which has been declared as TEXT at some institutions in Thailand and Canada (details can be found here). Further, I like to write my own database engines, and have wrote some called EDB and LiteDB (both based on PHP). I also wrote a Forum Software which does not require any database on back-end, but still runs much more faster than database driven messageboards, (details and demo can be found here).

I started my web journey in year 2000 when I was programmer of only C/C++ and Pascal language. I never do a permanent job for any company, because I have my own 4 web-shops, where I sell my own created products of PHP, SWiSH, Flash, ASP etc, but I always welcome work for extra ordinary PHP-MySQL based systems (AT HOME).

I hold degree of MBA (Masters in Business Administration [specialization in Marketing, at Pakistan Television]), as well as MCS (Masters in Computer Science).

Developers' Notes

Open MySQL Admin is based on three PHP files, one folder for images, one folder for Language files, and one folder for about 84 text templates (text files). CSS may be changed by editing style.css found in 'templates' folder.

Terms of use

This software is provided as it is, without any warranty. Its source may be modified under GNU GPL.

Companies or Individuals must get permission from me in order to embed and/or sell it alongwith their commercial products and/or services. To contact me just email me at sales@swish-db.com or info@flaxweb.com with subject 'Open MySQL Admin COMM'