Member Avatar for toxicandy

I am currently working on an online website which houses a database inside that has 50-75 fields. I have what I believe may be a somewhat rudimentary question. Assuming we want to query the Database how would one go about making it so it builds a query statement using the fields that are not blank for instance, let's say I have 8 fields:

First name
Last Name
Phone
City
State
Address
Marital status
Occupation

Now let's say I had 10,000 and I want to make the following query

First name: Joe
Last Name:
Phone:
City:
State: New York
Address:
Marital status:
Occupation: Engineer

How would I build a query statement that would select All Joes that live New York and are Engineers? Let assume 3 people. How would I go about building that Query. I am using PHP:PDO to query my SQL. I know how to build queries and get the information over but I don't want to be that guy that rights 1000 different cases for every possible case like only First name and city are filled in or last name and state and so on and so forth. I know there is an easier way but I am not the most experienced scripter. I don't currently have any code because I am just starting on the HTML skeleton but I figured before I get to PHP I'd get some ideas. I am not asking for full code for all this only what this principle is called or how most sites do this. Is there a way I can store different parts of the query in PHP variables like

$s = "blah blah WHERE blah"

and then build the entire statement using variables. I doubted this method because I thought that it would be impossible to build a query statement with variables, I may be wrong but I am pretty sure it can't be done that way.

Any help would be much appreciated.

  • 3 Contributors
  • 2 Replies
  • 387 Views
  • 4 Hours Discussion Span
  • Latest Post Latest Post by DJBirdi
Member Avatar for Banderson

Yes you can use variables to build your query. One way is to build your form, validate whether or not fields in your form are blank and if so do not process those fields. The fields that are activated use them for your query. You can also put all the database and form fields in an array and process them that way.

Also since you are using PDO you can also look into prepared statements and transactions.

There are several ways to do this. These are just a couple of possibilities.

Edited by Banderson
Member Avatar for DJBirdi

This should just be a matter of combining WHERE and AND operators like this:

$handler = $dbh->prepare('SELECT * FROM users WHERE 
                    first_name = :first_name
                    AND state = :state 
                    AND occupation = :occupation');

$handler->execute(array(
                    ':first_name' => 'Joe', 
                    ':state' => 'New York', 
                    ':occupation' => 'Engineer'));
Edited by DJBirdi
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.