PHP optional query building

A

andylpsx

New Member
Messages
29
Reaction score
1
Points
3
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 Joe's 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

Code: 
$s = "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.
 
descalzo

descalzo

Grim Squeaker
Community Support
Messages
9,373
Reaction score
326
Points
83
Are you saying that you want to have a web based form that will query the database?
That you will have a form with 50-75 fields?
That you want the script to use just those fields filled out?
 
A

andylpsx

New Member
Messages
29
Reaction score
1
Points
3
Yes, a form that will query the database and the database has around 50 to 75 fields. We are not usually looking for one specific person but for multiple people that meet certain criteria.
 
descalzo

descalzo

Grim Squeaker
Community Support
Messages
9,373
Reaction score
326
Points
83
A rough idea --

PHP: 
<!DOCTYPE html>
<html>
<head><title>Test</title>
</head>
<body>

Hello. Please fill out some of the fields below<br/>


<div>
<form action="" method="POST">
<input type="text" name="name" /> Name<br />
<input type="text" name="occupation"/>Occupation<br />
<input type="text" name="city"/>City<br />
<input type="text" name="state"/>State<br />
<input type="submit" />

</form>
</div>

<?php

$whereClause = "WHERE ";

foreach( $_POST as $k => $v ){

  if( $v != "" ){

    $whereClause = $whereClause . $k . "='" . $v . "' AND ";

  }


}

echo "WHERE clause built from your entries: " .  $whereClause . " 1 = 1";



?>
</body>
</html>

NOTE: does not check for SQL injection, etc. Just to give you some starting ideas.
 
A

andylpsx

New Member
Messages
29
Reaction score
1
Points
3
descalzo said:
A rough idea --

PHP: 
<!DOCTYPE html>
<html>
<head><title>Test</title>
</head>
<body>

Hello. Please fill out some of the fields below<br/>


<div>
<form action="" method="POST">
<input type="text" name="name" /> Name<br />
<input type="text" name="occupation"/>Occupation<br />
<input type="text" name="city"/>City<br />
<input type="text" name="state"/>State<br />
<input type="submit" />

</form>
</div>

<?php

$whereClause = "WHERE ";

foreach( $_POST as $k => $v ){

  if( $v != "" ){

    $whereClause = $whereClause . $k . "='" . $v . "' AND ";

  }


}

echo "WHERE clause built from your entries: " .  $whereClause . " 1 = 1";



?>
</body>
</html>

NOTE: does not check for SQL injection, etc. Just to give you some starting ideas.
Click to expand...


This work flawlessly but can you explain or send me a doc on how this works: foreach( $_POST as $k => $v ){
I ran it and it works but I want to know why and how, if you don't mind.


EDIT: I am having a problem when trying to query the database I get this error: Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''SELECT * FROM VolunDB WHERE fname='andy' AND 1 = 1'' at line 1' in /home/petrzilk/public_html/Database/testSearch.php:14 Stack trace: #0 /home/petrzilk/public_html/Database/testSearch.php(14): PDO->prepare(''SELECT * FROM ...') #1 {main} thrown in /home/petrzilk/public_html/Database/testSearch.php on line 14

my code is:
PHP: 
<?php

$whereClause = "WHERE ";

foreach($_POST as $k => $v ){

  if( $v != "" ){
    $whereClause = $whereClause . $k . "='" . $v . "' AND ";
  }
}
$db = new PDO('mysql:host=localhost;dbname=petrzilk_test;charset=utf8', 'petrzilk_dbAdmin', '***********'); // Connecting to Database
   $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Error statement
   $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
   $query = $db->prepare("'SELECT * FROM VolunDB " . $whereClause . " 1 = 1'");
   $query->execute();
   $result = $query->fetch(PDO::FETCH_OBJ);
?>
 
Last edited:
Skizzerz

Skizzerz

Contributors
Staff member
Contributors
Messages
2,928
Reaction score
118
Points
63
@descalzo's code does not check for SQL injection, which if your form is accessible from the web is an absolute necessity. Since you are using PDO, read up on prepared statements and placeholders.
 
You must log in or register to reply here.
Top