374 posts
  • Bought between 10 and 49 items
  • Contributed a Blog Post
  • Contributed a Tutorial to a Tuts+ Site
  • Exclusive Author
  • Has been a member for 4-5 years
  • Referred between 1 and 9 users
  • Sold between 1 000 and 5 000 dollars
_rohan says

Right, here’s my problem. I’ve got a databsae, and I wanted to tun a quick filtering script through it. I have a form, with two dropdowns, one for each DB field.

eg: Field – Name Options in dropdown – *, John, Harry

Field – Age Options in dropdown – *, 20, 30

Then, in the PHP , I use $_POST to get the values of the dropdowns. I use WHERE in the MySql query to get the desired results:

WHERE name=$name AND age=$age

($name and $age store the inputs)

If the inputs are not ’ * ’, it works fine. But I can find no way to display the results when the selected option is ’ * ’. Just FYI , ’ * ’ means ‘anything’.

So, can anyone help?

Thanks.

352 posts
  • Author had a File in an Envato Bundle
  • Author had a Free File of the Month
  • Bought between 10 and 49 items
  • Exclusive Author
  • Has been a member for 5-6 years
  • Referred between 50 and 99 users
  • Sold between 10 000 and 50 000 dollars
+1 more
danharper says

Just use an if statement to check whether a field is set as ‘’.

Try this

I wrote it quickly, so it may be slightly pseudo-code (and there may be a better way to do it).

So, as an example, if $name 'Dan' and $age 18, the SQL should be:

SELECT * FROM `tablename` WHERE `name` = 'Dan' AND `age` = 18;

If $name 'Dan' but $age ‘’ then it should be:

SELECT * FROM `tablename` WHERE `name` = 'Dan';

Or $name '*' but $age ‘18’:

SELECT * FROM `tablename` WHERE `age` = 18;

:)

374 posts
  • Bought between 10 and 49 items
  • Contributed a Blog Post
  • Contributed a Tutorial to a Tuts+ Site
  • Exclusive Author
  • Has been a member for 4-5 years
  • Referred between 1 and 9 users
  • Sold between 1 000 and 5 000 dollars
_rohan says

Thanks. That’s s good solution :-)

374 posts
  • Bought between 10 and 49 items
  • Contributed a Blog Post
  • Contributed a Tutorial to a Tuts+ Site
  • Exclusive Author
  • Has been a member for 4-5 years
  • Referred between 1 and 9 users
  • Sold between 1 000 and 5 000 dollars
_rohan says

But if you have 3,4 or more inputs, it becomes less useful :S

352 posts
  • Author had a File in an Envato Bundle
  • Author had a Free File of the Month
  • Bought between 10 and 49 items
  • Exclusive Author
  • Has been a member for 5-6 years
  • Referred between 50 and 99 users
  • Sold between 10 000 and 50 000 dollars
+1 more
danharper says

Right now I can’t think of a much better solution, since you can’t execute a SQL query like:

SELECT * FROM `tablename` WHERE `name` = '*';

Because it would be looking for a ‘name’ record containing an asterisk. And

SELECT * FROM `tablename` WHERE `name` = *;

Would return an error.

374 posts
  • Bought between 10 and 49 items
  • Contributed a Blog Post
  • Contributed a Tutorial to a Tuts+ Site
  • Exclusive Author
  • Has been a member for 4-5 years
  • Referred between 1 and 9 users
  • Sold between 1 000 and 5 000 dollars
_rohan says
Right now I can’t think of a much better solution, since you can’t execute a SQL query like: SELECT * FROM `tablename` WHERE `name` = ’*’;

Because it would be looking for a ‘name’ record containing an asterisk. And

SELECT * FROM `tablename` WHERE `name` = *;
Would return an error.

Yeah, that was my original problem. Anyway, I’ve done lots of repetition and coded it for five inputs. Thanks =)

26 posts
  • Sold between 10 000 and 50 000 dollars
  • Exclusive Author
  • Has been a member for 4-5 years
  • Bought between 50 and 99 items
  • Referred between 1 and 9 users
dm3studio says

Another solution may be to use LIKE operator function in a where clause

http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like

753 posts
  • Sold between 10 000 and 50 000 dollars
  • Referred between 10 and 49 users
  • Author had a Free File of the Month
  • Exclusive Author
  • Has been a member for 5-6 years
  • Bought between 1 and 9 items
  • Repeatedly Helped protect Envato Marketplaces against copyright violations
AdamGold says

Use an if statement to check if the field equals to ’*’. If it does, just don’t write the where operator in the query.

426 posts
  • Beta Tester
  • Bought between 10 and 49 items
  • Exclusive Author
  • Has been a member for 5-6 years
  • Referred between 10 and 49 users
  • Sold between 5 000 and 10 000 dollars
  • United States
vasilios says

This is a dynamic solution, but hasn’t been tested.

<?php // Name for the submit button (<input type="submit" [...] name="BLAH_BLAH_BLAH" [...] ?>)
$submitName = 'submit';

// Query WITHOUT the WHERE section.
$query = 'SELECT * FROM `tablename`';

// Build the WHERE section.
$where = array();

// Assumes the $_POST array contains the fields in this format: $_POST['field_name']
foreach($_POST as $key => $value) {
    if ($key == $submitName || $value == '*') continue;
    $where[] = '`'.$key.'` = "'.$value.'"';
}

// Append WHERE section to $query
if (count($where) > 0) $query .= ' WHERE '.implode(' AND ', $where);

// Anything else you want to append to $query should go here.
// $query .= ' ORDER BY `field_name` DESC';
// etc..
?>
by
by
by
by
by
by