Userwise Menu Design from mysql Database.

I was designing a database driven application. I thought to manage following utilities through the application.

1. User Creation.
2. Menu Design from database.
3. Giving rights to access the menu from the database.

To accomplish this, I have to design first the database tables. Here is an brief idea how I come near to my requirements.

1) Table design for User Creation.

CREATE TABLE `users`
(
`userid` smallint(3) unsigned NOT NULL auto_increment, // The Unique Identity for each user.
`username` varchar(30) NOT NULL // User Name
`userpass` varchar(32) NOT NULL, // Password for the user. MD5 is algorithm can be used.
`division` varchar(30) default NULL, // Division of user to which he/she belongs.
`name` varchar(50) default NULL, // Name of the User.
`label_to_print` varchar(500) default NULL, // Required if you want print any data for report printing.
`valid_from` date default NULL, // User can login from this Valid_from Date
`valid_to` date default NULL, // User can login from this Valid_to Date
`ipaddress` varchar(200) default NULL, // Required if you want to put IP restriction for any user.
`created_by` varchar(30) default NULL, // User created by the the user.
`entry_date` datetime default NULL, // Data of user creation.
`status` varchar(10) default NULL, // Status of user. User can login only if the status is Active.
PRIMARY KEY (`userid`),
UNIQUE KEY `username` (`username`)
)

2) Menu Design from Database.
I have created two tables to design the menu.
1. Table mainmenu to display root menu
2. Table submenu to leaf menu

CREATE TABLE `mainmenu` (
`mainmenuid` int(11) NOT NULL auto_increment, // Unique menu id
`mainmenuname` varchar(50) default NULL, // Main Menu name to display.
`mainmenumodule` varchar(50) default NULL, // Main menu module name to which it belongs.
`mainmenustatustext` varchar(255) default NULL, // Status message text.
`image` varchar(255) default NULL, // Image to display on menu.
`height` int(11) default NULL, // Image height
`width` int(11) default NULL, // Image width.
`userid` varchar(50) default NULL, // The record entered by the user.
`entry_date` datetime default NULL, // The record entry date.
UNIQUE KEY `mainmenuid` (`mainmenuid`)
)

CREATE TABLE `submenu` (
`mainmenuid` int(11) NOT NULL, // This column refer to mainmenu Table.
`screenid` int(11) NOT NULL, // Screen id. It should be sequential.
`print_order` smallint(6) default NULL, // Order in which to display it
`screename` varchar(50) default NULL, // Leaf menu name to display.
`screenlink` varchar(255) default NULL, // Link or filename to load the file
`image` varchar(255) default NULL, // Image file name to display
`height` int(11) default NULL, // Image height
`width` int(11) default NULL, //Image width.
`screenstatustext` varchar(255) default NULL, // Status bar message
`active` varchar(20) default NULL, // Screen is Active or Inactive.
`userid` varchar(20) default NULL, // The record entered by the user.
`entry_date` datetime default NULL, // The record entry date.
PRIMARY KEY (`mainmenuid`,`screenid`)
)
3) Table to give rights to access the menu from the database.
CREATE TABLE `user_right` (
`mainmenuid` int(11) NOT NULL, // MainMenu id
`screenid` int(11) NOT NULL, // Screen id
`username` varchar(20) NOT NULL, // Username who has access to mainmenuid+screenid
`valid_from` date default NULL, // Rights to access the screen from date.
`valid_to` date default NULL, // Rights to access the screen to date.
`userid` varchar(20) default NULL, // The record entered by the user.
`entry_date` datetime default NULL, // The record entry date.
PRIMARY KEY (`mainmenuid`,`screenid`,`username`)
)

Now create a simple view to fetch the data from the above table.
DELIMITER $$
DROP VIEW IF EXISTS `menu`$$
CREATE VIEW `menu` AS select `a`.`username` AS `username`,`c`.`mainmenuname` AS `mainmenuname`,`b`.`screename` AS `screename`,`b`.`screenlink` AS `screenlink`,`a`.`valid_from` AS `valid_from`,`a`.`valid_to` AS `valid_to` from ((`user_right` `a` join `submenu` `b`) join `mainmenu` `c`) where ((`a`.`mainmenuid` = `c`.`mainmenuid`) and (`a`.`screenid` = `b`.`screenid`) and (`a`.`mainmenuid` = `b`.`mainmenuid`)) order by `c`.`mainmenuid`,`b`.`print_order`$$
DELIMITER ;

Now insert the data in appropriate tables and you have done. Just execute the following query to access the screen rights for a single user.

Select * from menu where username="";

girishpadia@gmail.com

Enahncement to mysql_query php function.

I have extended mysql_query function to have more flexibility. It is a php file say "run_query.php". Include this file in your php application. To use this file follow these steps.
1) Create a table in mysql with the following structure.
CREATE TABLE `errorlog`
( `query` varchar(5000) default NULL,
`error` varchar(5000) default NULL )

2) Create a php file and paste following code. Save the file (say file name is run_query.php).

< ? php
function mysql_query_($query)
{ // Paste here code to connect to mysql database.
$username='root';
$password='';
$host='localhost';
$db_name="xyz";
$conn1=mysql_connect($host,$username,$password) or die("Unable to connect to mysql server");
$select1=mysql_select_db("$db_name",$conn1) or die("Unable to connect to mysql database");
$curdate = date("d-m-Y H:i:s");
if(mysql_query($query) == true) // The queri is fired and checked.
{
if(substr(strtoupper($query),0,6) == 'INSERT' substr(strtoupper($query),0,5) == 'UPDATE' substr(strtoupper($query),0,5) == 'DELETE') // The query is checked for Insert/Update/Delete
{
$fp=fopen("trans.sql","a"); //If query is type of Insert/Update/Delete then it will store the query into trans.sql file.
if($fp==null)
{
die("File cannot be opened. Try again !!!");
}
$printline = "/* $curdate : */ $query ;";
fprintf($fp,"\r\n%s",$printline);
fclose($fp);
return true;
}
else
{
return mysql_query($query);
}
}
else
{
$error = mysql_error();
$error = addslashes($error);
$query = addslashes($query);
mysql_query("insert into errorlog values('$query','$error')");
return false;
}
}
? >

3) Include this file in your any php application. (Command to include file : )
4) Use mysql_query_ function instead of mysql_query function.
e.g.
/*< ? php $query = "select * from employee";
$result = mysql_query _($query)
? >
*/
Advantages.

1. You can log query and error in your database table (errorlog).
2. A sql file created/appendended whenever any Data manipulation query is fired. So you can have a complete transaction log in query format.
3. This sql "trans.sql" file will help you in "point in time recovery" in case of the database is crashed.

Your views ,comments and updation in this function are welcome.
Please mail on girishpadia@gmail.com