...

View Full Version : Multiple Query changes



authorandrew
11-22-2011, 05:22 PM
Hello CodingForums!
I'm working on a web application which displays a grid of results. I want to have a sidebar which filters these results based on what the user clicks on in the sidebar. Here is the code I have so far:


$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) //connect to db
or die('Error connecting to MySQL server. Maybe it went to buy doughnuts.');

$query = "SELECT * FROM ff_faces";
if ($_GET['gender']) {
$gender = $_GET['gender'];
$query = $query . " WHERE " . $gender;
}
if ($_GET['ethnicity']) {
$ethnicity = $_GET['ethnicity'];
$query = $query . " WHERE ethnicity_id=" . $ethnicity;
}
$result = mysqli_query($dbc, $query);

When the user clicks the link, the information to augment the query is passed by a GET (i.e. ethnicity or gender). The query is then reloaded with the filtered content.

But I have no way to make the filter work for both ethnicity AND gender. In other words, if the user filters by male, it will show all male, but then if she clicks 'caucasian', for example, it will return all results that are caucasian of both genders. I need a way to have these queries stack on top of each other on the query so that the correct results are returned.

Hopefully that was clear, if not, please ask me. Thanks!
Andrew

Fou-Lu
11-22-2011, 06:00 PM
The problem is the where. You cannot have multiple WHERE within a query. The easiest change is a little hacky, but you can modify the query to SELECT * FROM ff_faces WHERE 1=1, then use AND instead of WHERE within the $query concatination.

BTW, the gender handling will need to change. Its not addressing any property.

XterM
11-23-2011, 02:39 AM
like as Fou-Lu's clue, I try to make it code



$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) //connect to db
or die('Error connecting to MySQL server. Maybe it went to buy doughnuts.');

$query = "SELECT * FROM ff_faces where 1=1 ";
if ($_GET['gender']) {
$gender = $_GET['gender'];
$query .= " and WHERE GENDER='".$gender."'"; //i don't know name of your gender field. so I asumme it fieldname is "GENDER"
}
if ($_GET['ethnicity']) {
$ethnicity = $_GET['ethnicity'];
$query .=" and ethnicity_id=" . $ethnicity;
}
$result = mysqli_query($dbc, $query);


hope it helps

authorandrew
11-23-2011, 02:04 PM
OK, I think I understand.

Fou-Lu, for some reason I have the GET for gender pass as gender=1, but the ethnicity pass only as 1; it works but they're different :P

My problem is that the user won't always click Gender then Ethnicity. They may only click Ethnicity. Or maybe they'll only click gender. But if they click both, I need to be able to adjust to that :P

AA

authorandrew
11-23-2011, 06:52 PM
Here is my optimized code, but which still only performs a single search with no nesting:


if (isset($_GET['gender'])) {
$gender = $_GET['gender'];
$where_clause .= "gender = " . $gender;
}
if ($_GET['ethnicity']) {
$ethnicity = $_GET['ethnicity'];
$where_clause .= "ethnicity_id = " . $ethnicity;
}
if (isset($where_clause)) {
$query .= " WHERE " . $where_clause;
}

Thanks!
Andrew

Fou-Lu
11-23-2011, 06:56 PM
Here is my optimized code, but which still only performs a single search with no nesting:


if (isset($_GET['gender'])) {
$gender = $_GET['gender'];
$where_clause .= "gender = " . $gender;
}
if ($_GET['ethnicity']) {
$ethnicity = $_GET['ethnicity'];
$where_clause .= "ethnicity_id = " . $ethnicity;
}
if (isset($where_clause)) {
$query .= " WHERE " . $where_clause;
}

Thanks!
Andrew

Still won't work. You need to add an AND or OR clause to separate multiple conditions depending on if you want to drill lower or open wider.

authorandrew
11-23-2011, 07:10 PM
I realize that. My problem is that I'm not sure how to do that while still allowing the user to only select one of the two narrowing down options (gender and ethnicity)

Andrew

Fou-Lu
11-23-2011, 08:02 PM
If you can only select one of the two, the easiest thing to do is manufacture a select or radio set to force it on HTML side. From PHP side, you can use an else if to dictate that, but it means the first will always prevail.

authorandrew
11-24-2011, 01:09 AM
I don't understand what you're saying.

Here's all I need to do:
The sidebar is a filter application for the entire database. One can sort only by gender, or one can sort only by ethnicity. Also, one can be able to sort by male and then further narrow down male results by an ethnicity. Is it possible to do this?

Andrew

XterM
11-24-2011, 01:46 AM
Here is my optimized code, but which still only performs a single search with no nesting:


if (isset($_GET['gender'])) {
$gender = $_GET['gender'];
$where_clause .= "gender = " . $gender;
}
if ($_GET['ethnicity']) {
$ethnicity = $_GET['ethnicity'];
$where_clause .= "ethnicity_id = " . $ethnicity;
}
if (isset($where_clause)) {
$query .= " WHERE " . $where_clause;
}

Thanks!
Andrew

how if there is ethnicity AND gender?
or without ethenicity and gender?

it will be error on queries.

Fou-Lu
11-24-2011, 03:33 AM
I don't understand what you're saying.

Here's all I need to do:
The sidebar is a filter application for the entire database. One can sort only by gender, or one can sort only by ethnicity. Also, one can be able to sort by male and then further narrow down male results by an ethnicity. Is it possible to do this?

Andrew

Are you sorting or filtering? You are interchanging the words. The sort means you only sort the resultset by whatever you have provided, and does not require a WHERE clause. If you need to add a filter, but only allow one, then you should write it as a select or radio menu instead to prevent multiples. I don't see a reason to code a filter without allowing all potential options to drill through it.

authorandrew
11-28-2011, 03:55 PM
I need to filter the results. I need to be able to filter using 1 criteria, but also possibly 2, depending on user input.

Andrew

Fou-Lu
11-28-2011, 06:32 PM
Okay, then we go back to post #2. You simply manufacture the query where if a condition exists you append an AND statement to the query.


$query = "SELECT * FROM ff_faces WHERE 1=1";
if (isset($_GET['gender']))
{
$query .= ' AND gender="' . mysql_real_escape_string($_GET['gender']) . '"';
}
if (isset($_GET['ethnicity']))
{
$query .= ' AND ethnicity="' . mysql_real_escape_string($_GET['ethnicity']) . '"';
}

That assumes that both of these HTML fields are now just the value, not 'gender=m' for example (which you sorta indicated you did previously).

authorandrew
11-28-2011, 07:32 PM
Again, this doesn't allow for nested searches. If I click to filter by 'male' for example, the faces all show up only those listed in the DB as male. But then if I click ethnicity: caucasian, for example, it lists results from both genders.

The problem is that when the user clicks a button, the page reloads, thus resetting the query to WHERE 1 (and nothing else) every time. I need some way to preserve the previous query across the board.

Fou-Lu
11-28-2011, 08:12 PM
Again, this doesn't allow for nested searches. If I click to filter by 'male' for example, the faces all show up only those listed in the DB as male. But then if I click ethnicity: caucasian, for example, it lists results from both genders.

The problem is that when the user clicks a button, the page reloads, thus resetting the query to WHERE 1 (and nothing else) every time. I need some way to preserve the previous query across the board.

How are you submitting this data?

authorandrew
11-28-2011, 10:55 PM
Through a GET. I have links which are configured like so:
www.example.net?gender=1

Andrew

Fou-Lu
11-29-2011, 12:16 AM
You'll need to append to your url's as well. Look into using http_build_query, then you can simply add an associative offset for what it is to what its value is.

authorandrew
11-29-2011, 12:30 AM
OK, I think I see what you mean, and that gives me somewhere to work towards. I'll post back with my results!

Andrew



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum