Page 1 of 2

A Shorthand Notation for SQL for Lazy ppl

Posted: Wed Apr 01, 2009 7:36 am
by max529
Hi all,

I am attaching a class file on which I have spent a lot of time ,and I desperately need some comments about

whether it is as useful (as i think it is) or not.This class exports a short hand like syntax for SQL.This means you can

do more with less code,and with less errors.

If you ever skipped checking for an existing username ,just before adding a new username to a table,just because

of the trouble of writing the required query, Then this class might solve some problems for you .

We can Consider a database table to be two dimensional php array.The first index of the array being the primary

key field,Second index,the column names .Now ,when you want to store something in an array ,you dont write

small scripts(like SQL) ,instead you write

Code: Select all

$array[10]['name']='user_1';

Then why,every time you want to get or set ,the `name` field for n'th row from the `user` table ,you have to write

an SQL query to do that?I mean you could express the requirement, which is to get the name field from 10th row

of user table by using a simpler syntax ,a syntax native to php.like

Code: Select all

 
$name=$user[10]->name;
This class exports a syntax that enables you to do just that,and more. It allows you to specify what you want from

your database with a lot fewer words.

It can be seen as a high level query language because ,It does to SQL what C does to Assembly language.

A few things this class can do for you...

1.Reduces the code required to carryout an sql operation
for example to insert a row into a table `user` you can do as

Code: Select all

$db->user=  array('name'=>'user_1','no'=>45);

to get the `name` column from table user with primary key value 10

Code: Select all

$name=$db->user(10)->name;
to update name column from table user with primary key value 10 ,to 'user_2'

Code: Select all

$db->user(10)->name  ='user_2' ;

2.Reduces errors by eliminating the strings inside strings ie, constructs Like "name='user' ",this is bad because a missing single quote in a double quoted string is hard to detect.


For example,to get the id of the user with name='user' from table user ,

Code: Select all

$id=$db->user->name('user')->id;
now if you miss that single quote around user ,the editor ,if it supports syntax checking will find that out as soon as you

finish typing it...

3.Automatically renames columns in a join

This feature is helpful if two tables in join contains the fields with same name,ex id field..

Code: Select all

$rows=$db->_join->user('u')->comments('c')->end;
   echo $rows->u_id(10)->u_name; 
   //  the name of user with id =10 from user table,aliased as 'u' becomes u_name.The `id` in user table aliased as 'u' becomes u_id.
   echo $rows->u_id(10)->c_comment
   //  the comment field in comments table becomes c_comment.
4.Automatically escapes the double quotes in string values .

The class only escapes a double quote only if t has not already been escaped.ie it does not do the usuall get_magic_quotes
to find out wether the string has been already escaped.

Please see the howto.html in the rar file for more features,and there are lots of them......

Download from
crdb.tar
Contains class file,a howto guide and a demo
(68.53 KiB) Downloaded 513 times
I need some comments on this desperately...so any one reading this,please leave some comments.
Regards,
Max

Re: Mysql Wrapper Class using magic functions

Posted: Mon Apr 06, 2009 5:21 am
by max529
HI,

As no one has replied yet,I am uploading the tar file as attache ment .You dont have to have a phpclasses.org membership...Please check and let me know of your comments.Please download class from

Thanks,
Max

Re: Mysql Wrapper Class using magic functions

Posted: Tue Apr 07, 2009 10:46 pm
by max529
hi,

Will some one please tell why no one is replying to this post..........

Re: Mysql Wrapper Class using magic functions

Posted: Tue Apr 07, 2009 10:50 pm
by John Cartwright
Maybe you should try explaning the benefit of using your code, and perhaps some use-case examples of the problems your code solve.

Serving us a file and asking for a review of it is asking a lot, especially when we know very little of the contents. We need enticement :)

Re: Mysql Wrapper Class using magic functions

Posted: Wed Apr 08, 2009 12:52 am
by max529
Hi,

Thanks for the reply.

There is an html document howto.html in that tar file i attached which explains the usage.I am sorry I didnt mentioned it earlier..

Regarding benifits..here are few

1.Reduces the code required to carryout an sql operation
for example to insert a row into a table `user` you can do as

Code: Select all

$db->user=  array('name'=>'user_1','no'=>45);

to get the `name` column from table user with primary key value 10

Code: Select all

$name=$db->user(10)->name;
to update name column from table user with primary key value 10 ,to 'user_2'

Code: Select all

$db->user(10)->name  ='user_2' ;

2.Reduces errors by eliminating the strings inside strings ie, constructs Like "name='user' "
to get the id of the user with name='user' from table user ,

Code: Select all

$id=$db->user->name('user')->id;
3.Automatically renames columns in a join

Code: Select all

$rows=$db->_join->user('u')->comments('c')->end;
   echo $rows->u_id(10)->u_name; 
   //  the name of user with id =10 from user table,aliases as 'u'.the `id` in user table aliased as 'u' becomes u_id.
   echo $rows->u_id(10)->c_comment
   //  the comment field in comments table becomes c_comment.
4.Automatically escapes the double quotes in string values .

The class only escapes a double quote only if t has not already been escaped.

Regards,
Max

Re: Mysql Wrapper Class using magic functions in PHP 5

Posted: Wed Apr 08, 2009 5:23 pm
by John Cartwright
You have only demonstrated the most basic queries. I think once complexities arise in SQL, which most queries in a production environment are, your library interface will struggle. Lets start by outlining a not so simple queries:

Code: Select all

SELECT `foobar`, COUNT(`id`) AS `count`
FROM `mytable`
WHERE DATE_SUB(NOW(), INTERVAL 30 MINUTE) > `created`
GROUP BY `foobar`

Re: Mysql Wrapper Class using magic functions in PHP 5

Posted: Thu Apr 09, 2009 2:50 am
by max529
Hi,
This is not a complete replacement of SQL.But The full power of SQL statements is not required in many situations.
For me,queries like you specified is normally required only when creating reports.I am talking about small scale web applications,in which programmers like me work a lot,and 75% of the quries we use are simple ones.For complex queries you probably use something like phpmyadmin to create and test queries before you actually incude it in your script.So there is no issue of having to type in the query,you just copy paste the completed query from phpmyadmin.Some queries takes more time to type in than to figure out the query itself.This class is intented to be used in such cases.

for example if `products` and `categories` are connected by an intersection table,and you want to know the category name for a product ,normaly you would use a join like

Code: Select all

SELECT * FROM products `p`  JOIN category_products `cp`  ON  p.id=cp.product_id JOIN categories `c` ON  c.id=cp.category_id WHERE p.id=10;
if you use this class,for getting the category name of a product with id 10 ,you can do something like

Code: Select all

$caetgory_id=$db->products(10)->category_id;
$category_name=$db->category($category_id)->category_name;
can do that even in a single line

Code: Select all

 
$category_name=$db->category($db->products(10)->category_id)->category_name;
I find this syntax more readable than decrypting a long sql join query..what you think?

Many complex queries can be split like this...just thinking about a join makes my head ache...I find this really helpful.i dont know ,may be its just me.

I mean, this should be used along with SQL queries.Keeping this in mind ,I have made the constructor of this class accept a database link only,instead of the whole server details,So that the link may be used with other mysql functions.

Lastly,Regarding your Query,if you remove that count function ,that can be represented by this class

if your query was

Code: Select all

 
SELECT `foobar` 
FROM `mytable` 
WHERE DATE_SUB(NOW(), INTERVAL 30 MINUTE) > `created` 
GROUP BY `foobar`


Then it could be represented as

Code: Select all

$rows=$db->mytable->created('<',(object)"DATE_SUB(NOW(), INTERVAL 30 MINUTE)")->group_by()->foobar;
I hope you have downloaded the tar file and have gone through the howto guide.Because there are more features in it than i have posted in this thread.

Thanks,
Max

Re: A Shorthand Notation for SQL in PHP5

Posted: Wed Apr 15, 2009 7:25 am
by max529
John Cartwright.....are you there..........??? it would be nice if you could post some response..

Re: A Shorthand Notation for SQL in PHP5

Posted: Wed Apr 15, 2009 9:25 am
by php_east
for me, i use a wrapper class, and it does all the mysql works. it uses standard query langauge. the emphasis is for portability and for predictable behaviour. another reason for not being attracted to this idea is that i don't see adding another dependency while gaining brevity as something wise for the moment. i'd rather stay with long readable codes and queries.

thanks.

Re: A Shorthand Notation for SQL in PHP5

Posted: Wed Apr 15, 2009 11:00 pm
by max529
Hi,

Thanks php_east for response.What you say is true at the moment.But I think this can be made portable too...

Can you please tell me why the behaviour is not predictable..

And at last I know why this post recieves very poor responses.

The kind of people who will find this class useful ,the kind of people who find the concepts appealing ,are exactly the kind that wont care to read a long post, like this..still worse..they wont even a be member in forums like this..even if they joined..they never came back once their issue was fixed...

So what do i do about something like that.....??

Re: Mysql Wrapper Class using magic functions in PHP 5

Posted: Thu Apr 16, 2009 6:00 am
by jayshields
max529 wrote:Hi,
This is not a complete replacement of SQL.But The full power of SQL statements is not required in many situations.
This is exactly what annoys me about libraries (or whatever) trying to simplify SQL. These things just make things easier for beginners. They're no good for people that are actually quite versed in SQL.

If I had a simple query in your library, then one day wanted to add an ORDER BY to it (say ORDER BY wasn't supported) then I'd have to change quite a few lines of code to go back to the old method of querying.

Re: Mysql Wrapper Class using magic functions in PHP 5

Posted: Thu Apr 16, 2009 8:20 am
by max529
jayshields wrote:
max529 wrote:Hi,
This is not a complete replacement of SQL.But The full power of SQL statements is not required in many situations.
This is exactly what annoys me about libraries (or whatever) trying to simplify SQL. These things just make things easier for beginners. They're no good for people that are actually quite versed in SQL.

If I had a simple query in your library, then one day wanted to add an ORDER BY to it (say ORDER BY wasn't supported) then I'd have to change quite a few lines of code to go back to the old method of querying.
ORDER By is supported by this class,but that wont be the answer to your issue....

suppose the old query was to get all users with age=50,

Code: Select all

 
//using the class 
foreach($db->users->age(50)  as  $k=>$v)
{
echo $v->name;
}
and somehow you had to do it the old way...couldnt this be done by changing only 2 lines of code

Code: Select all

$result=mysql_query("SELECT.....");
while($v=mysql_fetch_object($result))
{
echo $v->name;
}
Just for your information , using order by on `name` field with the class would be like

Code: Select all

 
//using the class
foreach($db->users->age(50)->order_by()->name  as  $k=>$v)
{
echo $v->name;
}
it also supports order by on multiple fileds,descending and ascending etcetc...

Thanks,

Re: A Shorthand Notation for SQL in PHP5

Posted: Thu Apr 16, 2009 7:18 pm
by php_east
max529 wrote:Can you please tell me why the behaviour is not predictable..
And at last I know why this post recieves very poor responses.
i think the example by jayshields demonstrates what i said.

the user of your class will not be able to predict what are the sql included or not included. this makes if terribly difficult to use, even scary. so it is then safer to get back to standard query langauge. the emphasis is in the word standard.

for example, the wrapper class i use merely aids the mysql connection and retrieval process. the queries themselves are passed as is. most db classes are like that.

so this effort of yours would perhaps be welcomed if you include all sql query commands. that alone is a daunting task. then there is the element of upgrades. when sql upgrades, you need to issue an upgrade as well, but that is an issue not primary at the moment.

another factor is old habits die hard. to get a few hundred thousand sql users to change and use an new langauge is going to be a tough road. new sql users will need close support, if they were to use your library, but the idea of using something in between does not really appeal to new leaners. put yourselves in their position. if you wish to learn html for example, woldd you prefer to use a html generator, or learn manually the html commands ?

these are among the reasons for lack of interest that you observe. i feel a deeper study in to what your intended audience requires may be needed. it may lead to something more, you never know. the work/time spent on this was not wasted. it provided you with expericence. and you could use this very well for other class ideas you may have.

if you want some ideas on libraries, just pm me. i have a wealth of ideas. i don't have the resources to make many of them, so am giving them away.

Re: A Shorthand Notation for SQL in PHP5

Posted: Fri Apr 17, 2009 1:38 pm
by max529
the user of your class will not be able to predict what are the sql included or not included. this makes if terribly difficult to use, even scary. so it is then safer to get back to standard query langauge. the emphasis is in the word standard.
I had made a documentation for the class that list the sql functions supported by it...or am i getting you wrong here?
for example, the wrapper class i use merely aids the mysql connection and retrieval process. the queries themselves are passed as is. most db classes are like that.
so this effort of yours would perhaps be welcomed if you include all sql query commands. that alone is a daunting task. then there is the element of upgrades. when sql upgrades, you need to issue an upgrade as well, but that is an issue not primary at the moment.
have you read the documentation.do you think the operations supported wont cover at least 60% of queries used in a ,say shopping cart script.If i make it support all sql operations then it will be as complex as sql itself.
another factor is old habits die hard. to get a few hundred thousand sql users to change and use an new langauge is going to be a tough road. new sql users will need close support, if they were to use your library, but the idea of using something in between does not really appeal to new leaners. put yourselves in their position. if you wish to learn html for example, woldd you prefer to use a html generator, or learn manually the html commands ?
I am not trying to make people switch from SQL.All i want is to give people a choice.

A choice to write $db->user(10)->name ,instead of 'select `name` from `user` where `id`=10'

A simple syntax for simple purposes.Why do languages have syntax like a+=b;

in COBOL language you have to write ADD b TO a instead of a+=b;

And see what computer scientist and Turing Award recipient Edsger Dijkstra remarked about COBOL, "The use of COBOL cripples the mind; its teaching should, therefore, be regarded as a criminal offense."

Why do we use php instead of c for creating webpages?

Because it lets us concentrate on the problem..

Every time you want to store a value,php lets you do just that.It wont require you to correctly assign the data type,or if it is a string it wont require you to allocate the required amount of memory.Because those are issues that are not related to the problem we are trying to solve.

php hides characteristics of the underlying machine.It frees your mind..

Same thing i wanted to do with databases...when you want a value from a database table,You should be able to get it right away.You shouldn't be made to write a program ,just to get that value.You should not be made to worry about putting the quotes correctly,or decide the variable name to store the result set or the row array.
if you want some ideas on libraries, just pm me. i have a wealth of ideas. i don't have the resources to make many of them, so am giving them away.
No thanks....

Re: A Shorthand Notation for SQL for Lazy ppl

Posted: Sun Apr 19, 2009 6:24 pm
by Pulni4kiya
I have a question: why aren't you using mysqli?

And about the class... Maybe something like that is a good idea, yes. But an SQL query is more readable to me.

I don't like all those arrows (->). And it should be more intuitive.