PHP Developers Network
http://forums.devnetwork.net/

Modern advanced sql generation from an array of criteria
http://forums.devnetwork.net/viewtopic.php?f=1&t=142593
Page 1 of 2

Author:  thinsoldier [ Tue May 24, 2016 4:49 pm ]
Post subject:  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

Author:  Christopher [ Tue May 24, 2016 6:19 pm ]
Post subject:  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?

Author:  thinsoldier [ Tue May 24, 2016 10:20 pm ]
Post subject:  Re: Modern advanced search form examples

I'm referring to converting data into the sql query text.

Author:  Christopher [ Wed May 25, 2016 2:13 pm ]
Post subject:  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:
Syntax: [ Download ] [ Hide ]
$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:
Syntax: [ Download ] [ Hide ]
$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.

Author:  thinsoldier [ Thu May 26, 2016 12:31 am ]
Post subject:  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.

Author:  Celauran [ Thu May 26, 2016 6:11 am ]
Post subject:  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.

Author:  Christopher [ Thu May 26, 2016 3:49 pm ]
Post subject:  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:
Syntax: [ Download ] [ Hide ]
$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

Author:  thinsoldier [ Fri May 27, 2016 3:18 pm ]
Post subject:  Re: Modern advanced search form examples


Author:  Christopher [ Fri May 27, 2016 4:48 pm ]
Post subject:  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?

Author:  thinsoldier [ Fri May 27, 2016 6:55 pm ]
Post subject:  Re: Modern advanced search form examples


Author:  Christopher [ Sun May 29, 2016 1:16 pm ]
Post subject:  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.

Author:  thinsoldier [ Tue Nov 29, 2016 3:29 am ]
Post subject:  Re: Modern advanced search form examples


Author:  Christopher [ Tue Nov 29, 2016 12:15 pm ]
Post subject:  Re: Modern advanced search form examples


Author:  thinsoldier [ Fri Dec 02, 2016 6:41 pm ]
Post subject:  Re: Modern advanced search form examples


Author:  thinsoldier [ Fri Dec 02, 2016 7:05 pm ]
Post subject:  Re: Modern advanced search form examples


Page 1 of 2 All times are UTC - 5 hours
Powered by phpBB® Forum Software © phpBB Group
http://www.phpbb.com/