[eZACKe]

I'm building a directory system that lists members or a certain group. I need to add capabilities to search this directory.

The search should be simple, they'll be fields to enter search criteria for first name, last name, street address, city, state, etc. The user searching can enter all the criteria, or any subset of the criteria. If they only enter a first name and state for example, I'd do a query that searches for someone with the first name of whatever was entered AND with a state of whatever was entered.

This isn't negotiable, this is how the search must work.

My problem is how I'm going to build a PDO sql statement with the correct params when you never know what the user is going to enter.

I usually do my PDO statements like this, for example (note isn't code for my search, this is just some code I found to show how I do it):

$sql = "INSERT INTO siteContent (name, content, changer) VALUES ('titleContent', :content, :family)";
		$result2 = $pdo->prepare($sql);
		$params = array(
		'content' => strip_tags($_POST['changeTitle']),
		'family' => $_SESSION['family']
		);
		$result2->execute($params); 

Now though, the query is not set in stone, and even worse than that, the $params array is not set in stone.

What are my options? I can't wrap my head around how this can be done.

Thanks for the help!


EDIT: I DO know how I would do this without prepared statements. I would like to use them though and that's what I can't figure out.

This post has been edited by eZACKe: 27 October 2011 - 07:46 AM

[macosxnerd101]

I would just dynamically build the query. If a field isset(), include it in the query. Otherwise, don't append that field = ? in your query.

[eZACKe]

macosxnerd101, on 27 October 2011 - 10:45 AM, said:

I would just dynamically build the query. If a field isset(), include it in the query. Otherwise, don't append that field = ? in your query.

Yes that's how I will build the query for sure, but I can't figure out how I would do that with prepared statements because I can't really dynamically build the $params array? I think?

Maybe I'm just not seeing it. If you see it though and could share a small little snippet I'd greatly appreciate it.


EDIT: Unless I could just include all fields in the param?

Like:

$params = array(
        'field1' => $something,
        'field2' => $something2,
        'field3' => $something3
        );

And then if field1 isn't in the sql statement will PDO just skip over it or will that cause an error? I'm not sure how that works.

This post has been edited by eZACKe: 27 October 2011 - 07:51 AM

[macosxnerd101]

If the field isset(), include that value in the final array. That's it. You can push a value into an array using the syntax:

$arrName[] = newValue;

[eZACKe]

macosxnerd101, on 27 October 2011 - 10:57 AM, said:

If the field isset(), include that value in the final array. That's it. You can push a value into an array using the syntax:

$arrName[] = newValue;

Wow.. not sure why I didn't see that.

So I would do something like:

if(isset($_POST['firstName']))
{
   $sql .= " AND firstName = :fn";
   $params['fn'] = $_POST['firstName'];
}

This post has been edited by eZACKe: 27 October 2011 - 08:00 AM

[macosxnerd101]

Something along those lines, yes.

[CTphpnwb]

I'd to it like this:

$expected = array("firstName" =>"fn","lastName" => "ln");
foreach($expected as $key => $val) {
	if(isset($_POST[$key])) {
		$sql .= " AND ".$key." = :".$val;
		$params[$val] = $_POST[$key];
	}
}

That makes it easy to add/remove parameters.

[eZACKe]

CTphpnwb, on 27 October 2011 - 11:31 AM, said:

I'd to it like this:

$expected = array("firstName" =>"fn","lastName" => "ln");
foreach($expected as $key => $val) {
	if(isset($_POST[$key])) {
		$sql .= " AND ".$key." = :".$val;
		$params[$val] = $_POST[$key];
	}
}

That makes it easy to add/remove parameters.

Actually now that I'm thinking about it, this wouldn't 100% work, would it?

Because when you submit a form and leave one of the fields blank, that $_POST variable is still set for that field, correct? So it would be true for every field here.

So wouldn't you have to modify it to be more along the lines of:

expected = array("firstName" =>"fn","lastName" => "ln");
foreach($expected as $key => $val) {
	if($_POST[$key]) {
		$sql .= " AND ".$key." = :".$val;
		$params[$val] = $_POST[$key];
	}
}

This way it doesn't check if it's set, it checks if it wasn't left empty.

[CTphpnwb]

Yes. You should use !empty:

$expected = array("firstName" =>"fn","lastName" => "ln");
foreach($expected as $key => $val) {
	if(!empty($_POST[$key])) {
		$sql .= " AND ".$key." = :".$val;
		$params[$val] = $_POST[$key];
	}
}