SQL PHP

[kamana...@gmail.com]

Hi,
hi bosses . How are you . Hope you are all fine.

I want to share some experience of my concurrent work. So to tell
you the story I want
to say some points,

1. I have created a database. The database is big enough.

2. one of my friend is creating a php pages for user
interfaces.

3. Eventually the php contents are dependent on the database.

Now the problem is ,

1. To get or set the dinamic contents from or to the database the
interface writer needs
to know the tables all.
-- One thing is important that the database is complicated. Not
all the tables here are for
single page show. And some of the tables have no existence in
the user screen.

2. To create levels of user (I mean to maintain the page permission)
there is need
to know all the ACTIONs a user can perform.

A simple solution to this problem is to use an abstraction of this
database to the
interface code and vice versa. To accomodate this I used some STORED
PROCEDURES.
The PROCEDURES correspond to the ACTIONS.

For example,

To create a user of this group we can write a procedure like,

CREATE PROCEDURE create_user (
IN full_name CHAR
,IN email_address CHAR
,IN uid INT -- see below why we need this
)
BEGIN
......
//native code here from the procedure writer who knows the database
well.
......
END

Now the interface writer can use this procedure like,

CALL create_user('noname','noname.gmail.com',1);

And the user is created.

1. So the interface writer do not need to know the database
structure.
2. The 'create_user' procedure corresponds to the 'user creation
action'. The extra
parameter like 'uid' is used to find if the user can call this
procedure. If not the procedure can
return a status like 'access denied'. A permission table can be
maintained for this.

CREATE TABLE permission (
uid INT(11) UNSIGNED AUTO_INCREMENT
, procedure_id INT(11) UNSIGNED
,permitted ENUM('yes','no') DEFAULT 'no'
PRIMARY KEY (uid,procedure_id)
);

And offcource a procedure table like,

CREATE TABLE procedures (
procedure_id INT(11) UNSIGNED AUTO_INCREMENT
,procedure_name CHAR(40)
,PRIMARY KEY (procedure_id)
);
INSERT INTO procedure(procedure_name) VALUES('create_user');

Some questions,

1. why we did not use triggers here ?

ANS: If we use triggers , we still need to insert or do something
like that. And
we need to know the database structure.

Please tell me if you have any better IDEA.

[Himel Nag Rana]

Hey kams,

we are now working on php and mysql. And facing some of u mentioned.
But the better part is we are using a framework so that data passing
back and forward become very easy. We are using MOJAVI 3.0. I think u
can try this for a test drive. You will need only 2-3 days to learn
this. But unfortunately there is not enough documentation or tutorial.
But i know abt ur capability. U can solve it anyway. I know it.

Gamberimas! (let's do it)

HNR.

[Kamanashis Roy Shuva]

view this concept,

http://en.wikipedia.org/wiki/Three_tier

[shiplu]

NO TOP POSTING ..
SEE BELOW

On 6/16/07, Kamanashis Roy Shuva <kamana...@gmail.com> wrote:
>
>
> view this concept,
>
> http://en.wikipedia.org/wiki/Three_tier
>
> >
>

This should be a new post.
Not a reply of some older post.
Thanks

--
shout at http://shiplu.awardspace.com/

Available for Hire/Contract/Full Time

[Kamanashis Roy]

Hi,

For some explanation,

>
> This should be a new post.
> Not a reply of some older post.
> Thanks
>

This is the idea. This explains the position of mysql procedure
in a development.

For example,

tier 1 : web page scripts/interface written in php/perl

tier 2 (integration) : mysql procedure to poll data from mysql as the
interface needs.

tier 3 : Backend mysql database to store data in an efficient way (I mean
we do
not think much about the interface things, we think about efficient way of
data
representation )

This is a design.

The relational database gets complex for some simple UI query. And it is
better to keep
it appart from the web interface. And create a middle layer of stored
procedure. The stored
procedure can relate and get the data as the interface needs, sometime it
checks the
validity of the data and a lot more(see the references).

References:

http://en.wikipedia.org/wiki/Stored_procedures
http://en.wikipedia.org/wiki/Three_tier