Modern advanced sql generation from an array of criteria
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
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
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.
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: Modern advanced search form examples
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
I'm referring to converting data into the sql query text.
Warning: I have no idea what I'm talking about.
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: Modern advanced search form examples
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:
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:
Any way you do it, the SQL building will be tedious with that many fields.
The simplest would be a custom Model class like:
Code: Select all
$searchModel = new SearchModel($db);
$results = $searchModel->find($formData);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();(#10850)
-
thinsoldier
- Forum Contributor
- Posts: 367
- Joined: Fri Jul 20, 2007 11:29 am
- Contact:
Re: Modern advanced search form examples
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.
Re: Modern advanced search form examples
Have you looked at Doctrine? It has a nice fluent interface to its DBAL that you could leverage here.
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: Modern advanced search form examples
I'm really not clear what you think is mysterious about SQL building is. It is as simple as:
PS - example to demonstrate idea, please use prepared statements
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);(#10850)
-
thinsoldier
- Forum Contributor
- Posts: 367
- Joined: Fri Jul 20, 2007 11:29 am
- Contact:
Re: Modern advanced search form examples
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.Christopher wrote:PS - example to demonstrate idea, please use prepared statements
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.
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: Modern advanced search form examples
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?
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
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'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:Are you using a framework? If you are then there is at least a baseline and probably a forms manager and query builder.
No, just lots of super basic PDO tutorials.Christopher wrote:If not then have you looks at various standalone forms manager and query builder?
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.Christopher wrote:And do you have a preferred pattern for database access? If not how simple or complex is your database?
Warning: I have no idea what I'm talking about.
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: Modern advanced search form examples
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
I am so sorry it took me so long to get back here. Is your offer still available?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.
Warning: I have no idea what I'm talking about.
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: Modern advanced search form examples
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?thinsoldier wrote:I am so sorry it took me so long to get back here. Is your offer still available?
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
Yes that particular form works. It was written way back in 2008 using 2002 era php-noob skills, lol.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?
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!
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.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.
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 );
[syntax]
http://
example.com/
datasource.php?not-expired¬-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
A gist of the code behind that form.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?
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.