8 Replies - 565 Views - Last Post: 27 October 2011 - 05:48 PM Rate Topic: -----

#1 eZACKe  Icon User is offline

  • Garbage Collector

Reputation: 120
  • View blog
  • Posts: 1,278
  • Joined: 01-June 09

Can't figure out how I would do this - Using PDO

Posted 27 October 2011 - 07:41 AM

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


Is This A Good Question/Topic? 0
  • +

Replies To: Can't figure out how I would do this - Using PDO

#2 macosxnerd101  Icon User is offline

  • Self-Trained Economist
  • member icon




Reputation: 10803
  • View blog
  • Posts: 40,256
  • Joined: 27-December 08

Re: Can't figure out how I would do this - Using PDO

Posted 27 October 2011 - 07:45 AM

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.
Was This Post Helpful? 0
  • +
  • -

#3 eZACKe  Icon User is offline

  • Garbage Collector

Reputation: 120
  • View blog
  • Posts: 1,278
  • Joined: 01-June 09

Re: Can't figure out how I would do this - Using PDO

Posted 27 October 2011 - 07:48 AM

View Postmacosxnerd101, 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

Was This Post Helpful? 0
  • +
  • -

#4 macosxnerd101  Icon User is offline

  • Self-Trained Economist
  • member icon




Reputation: 10803
  • View blog
  • Posts: 40,256
  • Joined: 27-December 08

Re: Can't figure out how I would do this - Using PDO

Posted 27 October 2011 - 07:57 AM

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;


Was This Post Helpful? 1
  • +
  • -

#5 eZACKe  Icon User is offline

  • Garbage Collector

Reputation: 120
  • View blog
  • Posts: 1,278
  • Joined: 01-June 09

Re: Can't figure out how I would do this - Using PDO

Posted 27 October 2011 - 08:00 AM

View Postmacosxnerd101, 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

Was This Post Helpful? 0
  • +
  • -

#6 macosxnerd101  Icon User is offline

  • Self-Trained Economist
  • member icon




Reputation: 10803
  • View blog
  • Posts: 40,256
  • Joined: 27-December 08

Re: Can't figure out how I would do this - Using PDO

Posted 27 October 2011 - 08:01 AM

Something along those lines, yes.
Was This Post Helpful? 0
  • +
  • -

#7 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3097
  • View blog
  • Posts: 10,883
  • Joined: 08-August 08

Re: Can't figure out how I would do this - Using PDO

Posted 27 October 2011 - 08:31 AM

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.
Was This Post Helpful? 1
  • +
  • -

#8 eZACKe  Icon User is offline

  • Garbage Collector

Reputation: 120
  • View blog
  • Posts: 1,278
  • Joined: 01-June 09

Re: Can't figure out how I would do this - Using PDO

Posted 27 October 2011 - 05:22 PM

View PostCTphpnwb, 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.
Was This Post Helpful? 0
  • +
  • -

#9 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3097
  • View blog
  • Posts: 10,883
  • Joined: 08-August 08

Re: Can't figure out how I would do this - Using PDO

Posted 27 October 2011 - 05:48 PM

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];
	}
}

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1