By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,350 Members | 1,309 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,350 IT Pros & Developers. It's quick & easy.

web query builder in php

P: n/a
fel
does anybody know of any *web* query builder in PHP, something like
the Access query builder, or similar, done in PHP or similar?
I've looked for it trough the net, but nothing is colse to what I
need.

I'm not looking forward to build my own,I hardly have any time.

all I find is programmer tools, I need something a USER (that has a
good understanding of the database schema, and some SQL knowledge) can
use, not a phplib.
any help is greatly apreciated.

thanks in advance.

Apr 26 '07 #1
Share this Question
Share on Google+
8 Replies


P: n/a
On Apr 26, 9:22 pm, fel <felipeval...@gmail.comwrote:
does anybody know of any *web* query builder in PHP, something like
the Access query builder, or similar, done in PHP or similar?
I've looked for it trough the net, but nothing is colse to what I
need.

I'm not looking forward to build my own,I hardly have any time.

all I find is programmer tools, I need something a USER (that has a
good understanding of the database schema, and some SQL knowledge) can
use, not a phplib.

any help is greatly apreciated.

thanks in advance.
why not set up phpmyadmin with a limit user, or use something like
mysql query browser - a windows application, again with a limited user.

Apr 26 '07 #2

P: n/a
On 27 apr, 02:22, fel <felipeval...@gmail.comwrote:
does anybody know of any *web* query builder in PHP, something like
the Access query builder, or similar, done in PHP or similar?
I've looked for it trough the net, but nothing is colse to what I
need.

I'm not looking forward to build my own,I hardly have any time.

all I find is programmer tools, I need something a USER (that has a
good understanding of the database schema, and some SQL knowledge) can
use, not a phplib.
Using ActiveX control is a bad idea? These guys are made HTML demo
page of their control: http://www.activequerybuilder.com/livedemo.html
- looks nice.

Serge

Apr 27 '07 #3

P: n/a
Hi Fel,

Yeah, i could have made phpPeanuts in something that really only needs
some menu links and a database and it will guess together an entire app,
but in my experience that is not what end users want. So with the
current version you also need to create a class file for each table with
a few methods. The framework will do the rest. Ask you users to test it
and they will tell you that you are doing well but that they do need it
just to be a little bit different. Adding some more methods, and maybe
some specific classes will make them already a lot happier. Maybe that
will do, maybe they will ask more for severl times. In practice i
allways need these class files anyway, then why would i try to get rid
of them?

To say it differently: one fool can aks more questions then a thousend
wizards can answer. Or in terms of software: one user can think up more
(and other) improvements then even the most powerfull tool can support.
And the more powerfull the tool is, the harder it is to configure. The
only reasonable solution is to make the tool do what is commonly needed
and allow programmers to amend, specialise and override its funcions so
they can meet specific demands of specific users and their business.
This is what i did with phpPeanuts. You can download it for free.

Yes, it is meant for programmers to configure and build applications,
because that tends to be necessary to make customers happy. If you
really think you can forecast what your users will need i bet i can
extend phpPeanuts to become the tool that does it without a single line
of code. But foregive me that i am not convinced yet so you will have to
convince me first, for example by paying me for the work i will have to
do to realize *your* vision. I estimate that that will still be 95 % you
get for free and only 5 % you have to pay for. If that is still too much
for you, i suggest you learn meta-programming yourself.

Greetings,

Henk Verhoeven,
www.phpPeanuts.org.


fel schreef:
does anybody know of any *web* query builder in PHP, something like
the Access query builder, or similar, done in PHP or similar?
I've looked for it trough the net, but nothing is colse to what I
need.

I'm not looking forward to build my own,I hardly have any time.

all I find is programmer tools, I need something a USER (that has a
good understanding of the database schema, and some SQL knowledge) can
use, not a phplib.
any help is greatly apreciated.

thanks in advance.
Apr 27 '07 #4

P: n/a
fel wrote:
does anybody know of any *web* query builder in PHP, something like
the Access query builder, or similar, done in PHP or similar?
I've looked for it trough the net, but nothing is colse to what I
need.

I'm not looking forward to build my own,I hardly have any time.
I'm guessing you're looking for one of those report writers that we all used
in the '80s and '90s in our LAN-based apps, of which the last man standing
seems to be Crystal Reports.

My own framework, Andromeda, includes a report writer, and the only people
who look for it are those "old folks" 35 who even know they exist. This
also means it is the least mature part of the framework because it gets the
least pressure from users.

If you want a report writer, you may may be interested in a lot of the other
features we have in Andromeda.

If you don't want to adopt a framework, you are probably looking at rolling
your own report writer. It is easy enough to get a basic flat lister going
if you've got a good data dictionary that defines the columns. You can get
clever and automatically figure out the joins if the dictionary lists
foreign keys. We've got code in Andromeda you can examine. Even grouping
is pretty easy, the trouble doesn't start until you put in the filters. At
this point we punted on that and are having using enter SQL expressions
like "dob < '1990-01-01'".

Try using fpdf to generate the output, it's well documented and gives nice
PDF files, with some smooth stuff for headers, footers, auto page breaks,
page numbers and all of that necessary and cumbersome stuff.

And let us know how it turns out!
--
Kenneth Downs
www.secdat.com
www.andromeda-project.org
Apr 28 '07 #5

P: n/a
Kenneth Downs wrote:
(..) the trouble doesn't start until you put in the filters. At
this point we punted on that and are having using enter SQL expressions
like "dob < '1990-01-01'".
Interesting, i have learned to think of SQL injection as a security
breach, you seem to be doing it on purpose?**

PhpPeanuts has got the filters. It can even do navigational queries like:
SELECT hours.* FROM hours WHERE category.name BETWEEN 's' AND 'w'
But not expressed in SQL, but in a Query form GETting the following:
http://examples.phppeanuts.org/examp...v1=s&pntF1v2=w

(Move your mouse crosor over the filter expression at the top to het the
form. The resulting page gives access to editable pages but that can be
changed with a few lines of code so that you get the report pages, like
it does from here:
http://examples.phppeanuts.org/examp...tLayout=Report
Try using fpdf to generate the output, it's well documented and gives nice
PDF files, with some smooth stuff for headers, footers, auto page breaks,
page numbers and all of that necessary and cumbersome stuff.
Sounds good! PhpPeanuts only outputs HTML. Might be nice to combine the
both, but Andromeda is licensed under GPL, so it would mean any
application programmers code that (indirectly) calls upon Andromeda
components will have to be GPL-ed*, i don't like to force that upon the
developers that are using phpPeanuts. Luckily there is another pdf
library that has a BSD-style license (like phpPeanuts), so i rather have
a litte more work whenever the need arrives for pdf output (i.e. a
customer wants to pay for it).

Furthermore, phpPeanuts is object-oriented, mapping the object's data to
database records. This allows one to add functions written in php to the
domain model, navigate it, build and maintain hierarchies and have
polymorphism. OTOH, If you want the freedom to JOIN and GROUP BY, the
table-oriented aproach of Andromeda will probably suit you better.

Greetings,

Henk Verhoeven,
www.phpPeanuts.org.

* It is my understanding of GPL that any code that links with code that
is licensed under GPL is a derived work, unless it does not depend ont
the code under GPL. Applications typically wil depend on the framework,
so if they are delevered they must be licensed under GPL to third
parties, which may mean the entire public. With an interpreted language,
calling methods must be condidered (dynamic) linking. Providing the full
adromeda source may allow the application developer to withhold his own
source, but that is kind of hard with PHP unless you only rent hosted
applications as a service, so no code is delevered.

** Yes, i read your remark on this subject on
http://www.andromeda-project.org/pag...SQL+Injections
It seems to me that you do not expect application developers to extend
the security beyond what can be arranged on the database.
Apr 29 '07 #6

P: n/a
Henk verhoeven wrote:
Kenneth Downs wrote:
>(..) the trouble doesn't start until you put in the filters. At
this point we punted on that and are having using enter SQL expressions
like "dob < '1990-01-01'".
Interesting, i have learned to think of SQL injection as a security
breach, you seem to be doing it on purpose**
** Yes, i read your remark on this subject on
http://www.andromeda-project.org/pag...SQL+Injections
It seems to me that you do not expect application developers to extend
the security beyond what can be arranged on the database.
Henk, thanks for the reply, it is always a pleasure to talk shop with
another framework author.

We probably need lots of discussion on the different assumptions between
Andromeda and phpPeanuts before we can draw conclusions. That being said,
I suspect, based on your remarks, and on prior experience, that your
framework connects to the database as a super-user and your code (or
application code) handles security. Andromeda does not do this. This
reflects not merely a different implementation of similar ideas about
security, but a deeply different ideas about security itself.

Because all security for a database application in the end becomes rules
about who can read or write to what table, Andromeda skips all unnecessary
and obfuscating layers and just specifies in advance who can read or write
to what tables. When the database is built the security is applied to all
tables. Users then connect with their own credentials. The job of the UI
is to conveniently present everything they are allowed to do. Anything
they are not allowed to do they should not see, not because it is insecure,
but because it would be confusing to show them something that they then
could not actually execute.

I do not expect application developers to extend the security because they
cannot. All code executes as the user and so is limited to the user's
security. (In fact, the only exception to this is the ability of the
programmer to elevate security ranking to
admin or some other level, but we needed that for only a single purpose,
which is now satisfied by other means, and we will be removing even that).

<snip>
>
>Try using fpdf to generate the output, it's well documented and gives
nice PDF files, with some smooth stuff for headers, footers, auto page
breaks, page numbers and all of that necessary and cumbersome stuff.

Sounds good! PhpPeanuts only outputs HTML. Might be nice to combine the
both, but Andromeda is licensed under GPL, so it would mean any
application programmers code that (indirectly) calls upon Andromeda
components will have to be GPL-ed*, i don't like to force that upon the
developers that are using phpPeanuts. Luckily there is another pdf
library that has a BSD-style license (like phpPeanuts), so i rather have
a litte more work whenever the need arrives for pdf output (i.e. a
customer wants to pay for it).

Two issues here. First is the GPL. My intention for licensing is that
applications are not considered derivative works, so I am investigating
whether I should switch to LGPL. More on that later.

Second issue is merging/combining. Who knows? Hard to say until we've
looked at each other's code.
>
Furthermore, phpPeanuts is object-oriented, mapping the object's data to
database records.
I first got into OO in the 90s, developed OO mania late 90s/early 2000s, and
can now say, "been there, done that, not interested". The inescapable trap
of OO framework development is assuming that code is a lasting asset, and
that if it could only be structured right, it would have lasting value, it
would at long last be "reusable." But it just ain't so. Code expires.
Data survives.

Now, once a person has seen the design cycle 2 or 3 times and realized that
the entire framework is doomed, he looks for something that will survive
the sea changes. This puts a person in the frame of mind to examine the
first fact of Andromeda: "All business rules resolve to database
specifications." From here we realize that what lasts is a description of
the business rules, which is nothing more than a description of the
database. (IF, and its a big IF, if you can work out what that description
itself must look like). The creative act in writing Andromeda was not the
coding, but describing the description.

This also leads to another huge difference between Andromeda and the others,
we have precious little code in the framework. The builder is about 6000
lines and the entire framework is another 10000, and we work hard to keep
the line count low. Minimize code, maximize data.
As I said before, there are so many differences in our approach that simple
things like "object oriented" require considerable conversation before we
can get at why you went one way and I went another.
This allows one to add functions written in php to the
domain model, navigate it, build and maintain hierarchies and have
polymorphism. OTOH, If you want the freedom to JOIN and GROUP BY, the
table-oriented aproach of Andromeda will probably suit you better.
None of those features require OO. In fact, nothing *requires* OO.

>
Greetings,

Henk Verhoeven,
www.phpPeanuts.org.

* It is my understanding of GPL that any code that links with code that
is licensed under GPL is a derived work, unless it does not depend ont
the code under GPL. Applications typically wil depend on the framework,
so if they are delevered they must be licensed under GPL to third
parties, which may mean the entire public. With an interpreted language,
calling methods must be condidered (dynamic) linking. Providing the full
adromeda source may allow the application developer to withhold his own
source, but that is kind of hard with PHP unless you only rent hosted
applications as a service, so no code is delevered.

--
Kenneth Downs
www.secdat.com
www.andromeda-project.org
May 1 '07 #7

P: n/a
Kenneth Downs schreef:
Henk verhoeven wrote:
>Kenneth Downs wrote:
>>(..) the trouble doesn't start until you put in the filters. At
this point we punted on that and are having using enter SQL expressions
like "dob < '1990-01-01'".
Interesting, i have learned to think of SQL injection as a security
breach, you seem to be doing it on purpose**
** Yes, i read your remark on this subject on
http://www.andromeda-project.org/pag...SQL+Injections
It seems to me that you do not expect application developers to extend
the security beyond what can be arranged on the database.

Henk, thanks for the reply, it is always a pleasure to talk shop with
another framework author.
Thank you for yours too, it is interesting.
>
We probably need lots of discussion on the different assumptions between
Andromeda and phpPeanuts before we can draw conclusions.
I agree. I am not trying to draw conclusions. The differences are
interesting enough.
That being said,
I suspect, based on your remarks, and on prior experience, that your
framework connects to the database as a super-user and your code (or
application code) handles security.
Well, yes and no. Yes, the default is to log in using settings made in a
settings script. I don't think that is a good thing, just seemed the
simpelest think that could possibly work. It can be overriden and i have
done so in several occasions.
No, my framewok has not got a built in security component. There's only
an example pointing in the direction of how to use the front controller
to check security, and a discussion about how to go on from there. So
security is basically left to the application developers.
>
(snip)
>
Two issues here. First is the GPL. My intention for licensing is that
applications are not considered derivative works, so I am investigating
whether I should switch to LGPL. More on that later.
If fpdf is under GPL instead of LGPL that may not be an option for those
parts that require fpdf.
But if your applications are not written in php but in this declarative
language i see in your tutorials i don't think there is a problem for
the applications, as long as no 'linking' takes place. Of course a JIT
compiler would blow this distinction to pieces, but so do RPC & SOAP...
>
Second issue is merging/combining. Who knows? Hard to say until we've
looked at each other's code.
I do not intend to merge. But there may be opportunities here and there
to reuse things, maybe design concepts, or maybe code. I agree that that
does require a lot more study. I intend to do some studying in the near
future, of several frameworks, to see what is available, and how to go on.
>
>Furthermore, phpPeanuts is object-oriented, mapping the object's data to
database records.

I first got into OO in the 90s, developed OO mania late 90s/early 2000s, and
can now say, "been there, done that, not interested". The inescapable trap
of OO framework development is assuming that code is a lasting asset, and
that if it could only be structured right, it would have lasting value, it
would at long last be "reusable." But it just ain't so. Code expires.
Absolutely.
Data survives.
But from an OO point of view there is no difference between data,
objects and code.
>
Now, once a person has seen the design cycle 2 or 3 times and realized that
the entire framework is doomed, he looks for something that will survive
the sea changes. This puts a person in the frame of mind to examine the
first fact of Andromeda: "All business rules resolve to database
specifications." From here we realize that what lasts is a description of
the business rules, which is nothing more than a description of the
database. (IF, and its a big IF, if you can work out what that description
itself must look like). The creative act in writing Andromeda was not the
coding, but describing the description.
Interetsing, i will certainly look into it later this year.

PhpPeanuts is third generation. I did look for something that could
survive too, but for me it where concepts that went into the designs. In
fact i did spend very litte time designing phpPeanuts when i started. I
started the eXtreme Programming (XP) way, building a first application
and the framework allmost simultaneously. I agree the application would
have been finisshed much earlier if i had not built the framework, so
according to XP i should not have built (most of) the framework, but i
had an idea of what the framework had to do, and i still think i built
the simpelest think that could possibly do that.
>
This also leads to another huge difference between Andromeda and the others,
we have precious little code in the framework. The builder is about 6000
lines and the entire framework is another 10000, and we work hard to keep
the line count low. Minimize code, maximize data.
I agree about keeping the amout of code low. Last beta is about 22200
lines of code (excluding examples, templates, styles and a javascript
include file). I think i can do with less code then many other
frameworks because the framework often implements a simple solution and
leaves it to the application developer to override it when he needs to.
>
As I said before, there are so many differences in our approach that simple
things like "object oriented" require considerable conversation before we
can get at why you went one way and I went another.
I the my for why i went the OO way is simply "Smalltalk".
>

>This allows one to add functions written in php to the
domain model, navigate it, build and maintain hierarchies and have
polymorphism. OTOH, If you want the freedom to JOIN and GROUP BY, the
table-oriented aproach of Andromeda will probably suit you better.

None of those features require OO. In fact, nothing *requires* OO.
Of course, the essence of OO is a few concepts, technically it's nothing
but data references, function references and a type hierachy. Anything
can be expressed with those. I use OO allmost because i grew up with it.
(Allmost, i did first learn procedural & structured programming, but
that now is a long time ago). I think the best feature of OO is
flexibility. If it's applied properly, of course.

To optimize for flexibility i do on purpose not make a distinction
between declarations (which i guess you call 'data') and code. Code that
is imparative in the current version of the framework may be replaced by
more generic code and some declarations later on. A later version of the
framework will probably allow the replacing of imparative application
code by more declarative code. But the other way around, code that is
declarative in the default implementations provided by the current
framework or the programming style shown in the examples may in a
specific application or in a later version of the framework be replaced
by a an imparative implementation, or by some code that does a database
query, some specific objects that model whatever however they like, or
maybe even by an implementation of a domain specific language, as long
as it produces the same kind of results. Or maybe even quite different
results that just happen to make sense to whoever lives then.

I got the impression our assumptions are quite different. To me it is
still interesting though to see what you are doing. Thanks for your
reaction.

Greetings,

Henk Verhoeven,
www.phpPeanuts.org.
May 1 '07 #8

P: n/a
Henk verhoeven wrote:
Kenneth Downs schreef:
>Henk verhoeven wrote:
>>Kenneth Downs wrote:
(..) the trouble doesn't start until you put in the filters. At
this point we punted on that and are having using enter SQL expressions
like "dob < '1990-01-01'".
Interesting, i have learned to think of SQL injection as a security
breach, you seem to be doing it on purpose**
** Yes, i read your remark on this subject on
http://www.andromeda-project.org/pag...SQL+Injections
It seems to me that you do not expect application developers to extend
the security beyond what can be arranged on the database.

Henk, thanks for the reply, it is always a pleasure to talk shop with
another framework author.
Thank you for yours too, it is interesting.
>>
We probably need lots of discussion on the different assumptions between
Andromeda and phpPeanuts before we can draw conclusions.
I agree. I am not trying to draw conclusions. The differences are
interesting enough.
>That being said,
I suspect, based on your remarks, and on prior experience, that your
framework connects to the database as a super-user and your code (or
application code) handles security.

Well, yes and no. Yes, the default is to log in using settings made in a
settings script. I don't think that is a good thing, just seemed the
simpelest think that could possibly work. It can be overriden and i have
done so in several occasions.
No, my framewok has not got a built in security component. There's only
an example pointing in the direction of how to use the front controller
to check security, and a discussion about how to go on from there. So
security is basically left to the application developers.

Hmmm, then this is a point where the frameworks cannot really be compared.
>
(snip)
>>
Two issues here. First is the GPL. My intention for licensing is that
applications are not considered derivative works, so I am investigating
whether I should switch to LGPL. More on that later.

If fpdf is under GPL instead of LGPL that may not be an option for those
parts that require fpdf.
But if your applications are not written in php but in this declarative
language i see in your tutorials i don't think there is a problem for
the applications, as long as no 'linking' takes place. Of course a JIT
compiler would blow this distinction to pieces, but so do RPC & SOAP...
>>
Second issue is merging/combining. Who knows? Hard to say until we've
looked at each other's code.

I do not intend to merge. But there may be opportunities here and there
to reuse things, maybe design concepts, or maybe code.
Actually that's what I meant. File-by-file comparisons and
cross-fertilization. Should have been more clear.
>
>>
>>Furthermore, phpPeanuts is object-oriented, mapping the object's data to
database records.

I first got into OO in the 90s, developed OO mania late 90s/early 2000s,
and
can now say, "been there, done that, not interested". The inescapable
trap of OO framework development is assuming that code is a lasting
asset, and that if it could only be structured right, it would have
lasting value, it
would at long last be "reusable." But it just ain't so. Code expires.
Absolutely.
>Data survives.
But from an OO point of view there is no difference between data,
objects and code.
Untrue in any meaningful way at the application level. Code is imperative,
data is declarative. They cannot be more different. A relational database
can be ACID compliant, transactional, and (of course) relational. Code
canot, whether it is assembly, COBOL or has a spotless OO pedigree.

The seductive power of ORM is the false belief that the disparate natures of
data and code can be glossed over by making one look like the other. Since
one cannot be made to look like the other, the amount of payback for ORM is
negligible except in trivial cases. The more cases you try to accommodate,
the clumsier and bulkier the ORM project becomes.

At this point, many ORM enthusiasts will say, "You can't say that because I
get great payback with my ORM system." My only reply at this point is to
say, "keep going, tell me how it works out 5 years from now."
>
>>
Now, once a person has seen the design cycle 2 or 3 times and realized
that the entire framework is doomed, he looks for something that will
survive
the sea changes. This puts a person in the frame of mind to examine the
first fact of Andromeda: "All business rules resolve to database
specifications." From here we realize that what lasts is a description
of the business rules, which is nothing more than a description of the
database. (IF, and its a big IF, if you can work out what that
description
itself must look like). The creative act in writing Andromeda was not
the coding, but describing the description.

Interetsing, i will certainly look into it later this year.

PhpPeanuts is third generation. I did look for something that could
survive too, but for me it where concepts that went into the designs. In
fact i did spend very litte time designing phpPeanuts when i started. I
started the eXtreme Programming (XP) way, building a first application
and the framework allmost simultaneously. I agree the application would
have been finisshed much earlier if i had not built the framework, so
according to XP i should not have built (most of) the framework, but i
had an idea of what the framework had to do, and i still think i built
the simpelest think that could possibly do that.
Yeah, the XP stuff is great fun. Great boosk.

>
I got the impression our assumptions are quite different. To me it is
still interesting though to see what you are doing. Thanks for your
reaction.
Same here.
>
Greetings,

Henk Verhoeven,
www.phpPeanuts.org.
--
Kenneth Downs
www.secdat.com
www.andromeda-project.org
May 5 '07 #9

This discussion thread is closed

Replies have been disabled for this discussion.