PHP MySQL + Create Search Query Based off Form Selection

Question

I have a HTML form that people can select some or all off to search a database for member profiles.

Some of the options are:

Male/Female
Age
Location
check boxes like intentions or interests
etc

I need to tailor a MySQL query to meet the selection the member has chosen.

I'm asking because I built a custom search like this before and it turned into a complete mess with multiple queries depending on what was selected.

Would it be best to just build one query and have parts that are added depending on what is selected?

Does anyone have a ruff example?

Database Schema:

I have a number of tables with the related information so I would need to use joins. That said everything works on one primary key PID so it would all join on this.

How does your DB schema look?

BenM
– BenM

2014-01-09 22:43:48 +00:00
Commented Jan 9, 2014 at 22:43 — BenM
– BenM, Commented Jan 9, 2014 at 22:43

rod_torres · Accepted Answer · 2014-01-09 22:51:29Z

2

You could do something like this:

<?php
  $whereClause = '';
  if($_GET['gender'] == 'male'){
    $whereClause .= ' AND gender = "M"';
  }
  if($_GET['age'] != ''){
    $whereClause .= ' AND age = "'.$_GET['age'].'"';
  }
?>

answered Jan 9, 2014 at 22:51

rod_torres

3462 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rod_torres Over a year ago

yes but as you can see I put the AND at the beggining of each string, so it depends that you have something already in the where clause like WHERE id = 1 '.$whereClause.'if you don't you could still use it but change it a little

rybo111 · Accepted Answer · 2014-01-10 00:17:06Z

1

I would use an array:

$where = array();
if($_GET["gender"]!=""){
  $clean = mysqli_escape_string($db, $_GET["gender"]);
  array_push($where, "gender = '$clean'");
}
// etc...
$where = implode(" AND ", $where);
$sql = "SELECT * FROM table WHERE $where";

answered Jan 10, 2014 at 0:17

rybo111

12.6k4 gold badges64 silver badges74 bronze badges

Collectives™ on Stack Overflow

PHP MySQL + Create Search Query Based off Form Selection

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related