Modern advanced sql generation from an array of criteria

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

thinsoldier
Forum Contributor
Posts: 367
Joined: Fri Jul 20, 2007 11:29 am
Contact:

Modern advanced sql generation from an array of criteria

Post by thinsoldier »

Can anyone point me to any open source project that has an advanced search form with many form fields and the search logic is implemented with OO msyqli or PDO?

Form complexity example: http://thinsoldier.com/wip/advanced-sea ... xample.png
Last edited by thinsoldier on Sat Dec 03, 2016 4:29 pm, edited 1 time in total.
Warning: I have no idea what I'm talking about.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Modern advanced search form examples

Post by Christopher »

I am not clear what part of the "advanced search form" you mean? There seem to be three parts to a search form: 1) generating the form, 2) validating the form submssion, and 3) converting data from the form to SQL (or similar) to do a query and get results. Which part are you talking about?
(#10850)
thinsoldier
Forum Contributor
Posts: 367
Joined: Fri Jul 20, 2007 11:29 am
Contact:

Re: Modern advanced search form examples

Post by thinsoldier »

I'm referring to converting data into the sql query text.
Warning: I have no idea what I'm talking about.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Modern advanced search form examples

Post by Christopher »

In that case, it depends on what pattern you want to use to access the data. There are many OO query builders out there.

The simplest would be a custom Model class like:

Code: Select all

$searchModel = new SearchModel($db);
$results = $searchModel->find($formData);
The find() method would validate and escape any form data it used to create the required SQL.

If you wanted to use the same code for different search forms, then you could have the Controller/View map the data onto function calls like:

Code: Select all

$searchModel = new SearchModel($db);
$searchModel->setCategory($formData['category']);
$searchModel->setSource($formData['origin']);
$searchModel->setExpired($formData['expired']);
$results = $searchModel->find();
Any way you do it, the SQL building will be tedious with that many fields.
(#10850)
thinsoldier
Forum Contributor
Posts: 367
Joined: Fri Jul 20, 2007 11:29 am
Contact:

Re: Modern advanced search form examples

Post by thinsoldier »

I'd rather see a real world open source project that has purpose built code for their search form needs and then deconstruct that to get an understanding. I've looked at a bunch of PDO tutorials that just go over the basics and then I look at my forms that I want to rebuild and am overwhelmed by the gulf between what I can find in endless tutorials and what I actually need to do.
Warning: I have no idea what I'm talking about.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Modern advanced search form examples

Post by Celauran »

Have you looked at Doctrine? It has a nice fluent interface to its DBAL that you could leverage here.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Modern advanced search form examples

Post by Christopher »

I'm really not clear what you think is mysterious about SQL building is. It is as simple as:

Code: Select all

$where = [];
if ($formData['category']) {
    $where[] = "category LIKE '%{$formData['category']}%'";
}
if ($formData['expired']) {
    $where[] = "expired='Y'";
}

$sql = 'SELECT * FROM products WHERE ' . implode(' AND ', $where);
PS - example to demonstrate idea, please use prepared statements
(#10850)
thinsoldier
Forum Contributor
Posts: 367
Joined: Fri Jul 20, 2007 11:29 am
Contact:

Re: Modern advanced search form examples

Post by thinsoldier »

Christopher wrote:PS - example to demonstrate idea, please use prepared statements
Exactly. Prepared statements and possibly other concepts that don't visualize clearly in my mind when I look at my massive sql statements and the massive amount of php logic surrounding many parts of them. It's something that looking at basic tutorials is not giving me the confidence to tackle. I really need to find a real world open source project with a search form of comparable flexibility to what I do and see if I can read that *real* code with all its warts and cracks and workarounds for whatever real world stumbling blocks those devs encountered that aren't covered in any way in any basic tutorial.

I'm not asking for basic examples. I'm asking if anyone is aware of any open source project that uses modern sql oop techniques like PDO and has a fairly flexibile/logic-heavy search form that utilizes almost every form field type there is.

I can find some open source projects with the kind of forms I'm referring to but they all haven't been updated since 2010 or earlier and use plain old mysql_*.
Warning: I have no idea what I'm talking about.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Modern advanced search form examples

Post by Christopher »

Are you using a framework? If you are then there is at least a baseline and probably a forms manager and query builder.

If not then have you looks at various standalone forms manager and query builder?

And do you have a preferred pattern for database access? If not how simple or complex is your database?
(#10850)
thinsoldier
Forum Contributor
Posts: 367
Joined: Fri Jul 20, 2007 11:29 am
Contact:

Re: Modern advanced search form examples

Post by thinsoldier »

Christopher wrote:Are you using a framework? If you are then there is at least a baseline and probably a forms manager and query builder.
Ages ago was told by superiors "If you use code other people made and you don't know how to fix it you've <span style='color:blue' title='I&#39;m naughty, are you naughty?'>smurf</span> us and you're fired." The more recent reason was "frameworks are too much overhead for our slow server". So, no. It's all php4 styled code.
Christopher wrote:If not then have you looks at various standalone forms manager and query builder?
No, just lots of super basic PDO tutorials.
Christopher wrote:And do you have a preferred pattern for database access? If not how simple or complex is your database?
No, and I assume since I'm not awesome at database design or constructing efficient queries it's probably not that complex although I personally find it complex.
Warning: I have no idea what I'm talking about.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Modern advanced search form examples

Post by Christopher »

It sounds like you need to code your own solution that will be tailored to your codebase. And the fact that the tutorials for this kind of thing are all based on different framework's solutions means that you will not find general examples. Give that, Celauran and I could probably walk you through the concepts. There are lots of different concepts in this area -- everything from the TableDataGateway pattern to Domain Driven Design. All are ways to solve problems and some mix will be right for you. As we always seem to ask around here, it would be of interest to see a rough example of the kind of code you are currently using. A cut down, working version of your Detailed Criteria Search form would be the best place to start.
(#10850)
thinsoldier
Forum Contributor
Posts: 367
Joined: Fri Jul 20, 2007 11:29 am
Contact:

Re: Modern advanced search form examples

Post by thinsoldier »

Christopher wrote:It sounds like you need to code your own solution that will be tailored to your codebase. And the fact that the tutorials for this kind of thing are all based on different framework's solutions means that you will not find general examples. Give that, Celauran and I could probably walk you through the concepts. There are lots of different concepts in this area -- everything from the TableDataGateway pattern to Domain Driven Design. All are ways to solve problems and some mix will be right for you. As we always seem to ask around here, it would be of interest to see a rough example of the kind of code you are currently using. A cut down, working version of your Detailed Criteria Search form would be the best place to start.
I am so sorry it took me so long to get back here. Is your offer still available?
Warning: I have no idea what I'm talking about.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Modern advanced search form examples

Post by Christopher »

thinsoldier wrote:I am so sorry it took me so long to get back here. Is your offer still available?
Of course! There are a number of possible solutions. We just need the one that suits your style and your current implementation. From your example (http://thinsoldier.com/wip/advanced-sea ... xample.png) you have many values in your from. Do you have the form working? Are you creating any SQL from all those form values?

I often implement a Form Model that is initialized when the user enters the page and holds the form values and state until the submission is accepted without errors. Once the submission is accepted, the Form Model can be given to the Search Model to perform the search. That way there are formal interfaces for everything.
(#10850)
thinsoldier
Forum Contributor
Posts: 367
Joined: Fri Jul 20, 2007 11:29 am
Contact:

Re: Modern advanced search form examples

Post by thinsoldier »

Christopher wrote: Of course! There are a number of possible solutions. We just need the one that suits your style and your current implementation. From your example (http://thinsoldier.com/wip/advanced-sea ... xample.png) you have many values in your from. Do you have the form working? Are you creating any SQL from all those form values?
Yes that particular form works. It was written way back in 2008 using 2002 era php-noob skills, lol.

There's a similar but simpler form on the public side of my sites written in slightly "newer" code but that code still feels so dirty and fragile... half of it is sql comments!
Christopher wrote: I often implement a Form Model that is initialized when the user enters the page and holds the form values and state until the submission is accepted without errors. Once the submission is accepted, the Form Model can be given to the Search Model to perform the search. That way there are formal interfaces for everything.
I'm mostly focused on just taking criteria, discarding any disallowed criteria, and generating sql from the criteria. The super long term goal is to have the querying of data availalbe through a single central object that can be used easily with minimum php code or exposed at its own REST url (probaly only responding to GET requests) for js-based sites or native apps to consume.

Code: Select all

$instructions = [
'criteria'=>[
        'not-expired' => 1,
        'not-hidden' => 1,
        'application-completed' => 1,
        'category' => 'commercial sale',
        'island' => 420,
        'property-type' => [71,23,44,11]
    ],
'config'=>[
        'format' => 'json',
        'paginate' => 1,
        'paginate-limit' => 20,
        'paginate-offset' => 60
    ]
];
$data = new ListingsQuery( $instructions );
would hopefully return the same data as

[syntax]
http://
example.com/
datasource.php?not-expired&not-hidden&application-completed&category=commercial+sale&island=420&property-type=71,23,44,11&json&paginate&pag-limit=20&pag-offset=60
[/syntax]

But that dream is a long way off.

I really just desperately want to see someone else's code that builds a complex query from many optional inputs to try to find out if there's a drastically different and better way than how I've done.

I've asked 2 people who have done a few dozen CodeIgniter sites between them how they would do something like this with CI's $this->db object and they say they just wouldn't, lol.

I think I should be using some kind of "query builder" because one of my recurring problems in my public search code is depending on which unusual combination of fields are searched on, the resulting query might have a syntax error due to how many multi-line comments I have in the sql that is generated.
Last edited by thinsoldier on Fri Dec 02, 2016 6:34 pm, edited 1 time in total.
Warning: I have no idea what I'm talking about.
thinsoldier
Forum Contributor
Posts: 367
Joined: Fri Jul 20, 2007 11:29 am
Contact:

Re: Modern advanced search form examples

Post by thinsoldier »

Christopher wrote: From your example (http://thinsoldier.com/wip/advanced-sea ... xample.png) you have many values in your from. Do you have the form working? Are you creating any SQL from all those form values?
A gist of the code behind that form.

Just remember most of this was written in 2000-single-digit, lol.

https://gist.github.com/thinsoldier/b30 ... b1de8da4d4

[gist=b30aa03341e7043666d959b1de8da4d4]gistfile1.txt[/gist]
Warning: I have no idea what I'm talking about.
Post Reply