Search Filtering with PHP/MySQL
Like all the other post you will need to append all the conditions with AND like so. This is the cleanest answer so far. Remember to real escape your strings though use the mysqli OOP way instead of the old mysql. Just a suggestion.
Heres an example of a typical query.
The correct way:
SELECT * FROM donar WHERE name='dxenaretionx' AND sex='M';
The way you are doing it
SELECT * FROM donar WHERE name='dxenaretionx' sex='M';
Code:
function search_donar($_POST) {
$by_name = $_POST['by_name'];
$by_sex = $_POST['by_sex'];
$by_group = $_POST['by_group'];
$by_level = $_POST['by_level'];
//Do real escaping here
$query = "SELECT * FROM donar";
$conditions = array();
if(! empty($by_name)) {
$conditions[] = "name='$by_name'";
}
if(! empty($by_sex)) {
$conditions[] = "sex='$by_sex'";
}
if(! empty($by_group)) {
$conditions[] = "blood_group='$by_group'";
}
if(! empty($by_level)) {
$conditions[] = "e_level='$by_level'";
}
$sql = $query;
if (count($conditions) > 0) {
$sql .= " WHERE " . implode(' AND ', $conditions);
}
$result = mysql_query($sql);
return $result;
}
How to implement multiple search filters in php
This could work, assuming that your $_GET
keys match columns in your table:
$query = "SELECT * FROM table";
$filtered_get = array_filter($_GET); // removes empty values from $_GET
if (count($filtered_get)) { // not empty
$query .= " WHERE";
$keynames = array_keys($filtered_get); // make array of key names from $filtered_get
foreach($filtered_get as $key => $value)
{
$query .= " $keynames[$key] = '$value'"; // $filtered_get keyname = $filtered_get['keyname'] value
if (count($filtered_get) > 1 && (count($filtered_get) > $key)) { // more than one search filter, and not the last
$query .= " AND";
}
}
}
$query .= ";"
$query = "SELECT * FROM table WHERE name = 'name' AND city = 'city' AND age = 'age';"
I think this may work, although this code sample does not include sanitization against SQL injection, so you should probably add something to scrub potentially dangerous input
Updated: added $filtered_get = array_filter($_GET);
to filter out any empty fields from $_GET
array
Basic MySQL/PHP Filtering
PHP has a very good filter Function
http://php.net/manual/de/ref.filter.php
/*** use a callback filter to mysql_real_escape_string ***/
$answer = filter_input(INPUT_POST, "answer", FILTER_CALLBACK, array("options"=>"mysql_real_escape_string"));
/*** create an sql query ***/
$sql = "INSERT INTO quiz (answers) VALUES ('{$answer}')";
/*** echo the query ***/
echo $sql;
PHP mysql query issue when filtering from array and variables
You can construct this query from arrays, you can use the IN
clause in mysql to specify multiple OR
values
$regions = array('region1', 'region2', 'region3', 'region4');
$position = array('position1', 'position2', 'position3');
$regions = implode(",", $regions); // Converts array to string with comma as a separator
$position = implode(",", $position);
$sql = "SELECT * FROM `Table_Name` WHERE Joint_1 IN ($regions) AND Position IN ($position)";
echo $sql;
This gives you a query like this
SELECT * FROM Table_Name WHERE Joint_1 IN (region1,region2,region3,region4) AND Position IN (position1,position2,position3)
add in your own LIMIT
GROUP BY
or ORDER BY
parameters to suit your needs
Choose results by date filter PHP and MySQL
If dates are in YYYY-mm-dd
format then you can use it as below otherwise you need to change the format.
You can do as below :
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
$db_selected = mysql_select_db('foo', $link);
if (!$db_selected) {
die ('Can\'t use foo : ' . mysql_error());
}
//$from_date = $_POST['from_date'];
//$to_date = $_POST['to_date'];
$from_date = date("Y-m-d",strtotime('06/10/2015'));
$to_date = date("Y-m-d",strtotime('06/16/2015'));//$_POST['to_date'];
$query = "SELECT * FROM table WHERE from_date >= '".$from_date."' AND to_date <= '".$to_date."'";
// Perform Query
$result = mysql_query($query);
if (!$result) {
$message = 'Invalid query: ' . mysql_error() . "\n";
$message .= 'Whole query: ' . $query;
die($message);
}
while ($row = mysql_fetch_assoc($result)) {
print_r($row);
}
How to filter mysql rows in PHP where data is either empty or 0
You can use like this, it will not consider row with value 0
and AVG
itself excludes null
values
SELECT AVG(NULLIF(concept4, 0)) FROM KeyPad;
or using case (sample demo link)
SELECT AVG(case
when concept1 = 0 then null
else concept1
end) as c1
FROM KeyPad
so your full query:
SELECT AVG(NULLIF(concept1,0)) AS c1,
AVG(NULLIF(concept2,0)) AS c2,
AVG(NULLIF(concept3,0)) AS c3,
AVG(NULLIF(concept4,0)) AS c4,
AVG(NULLIF(concept5,0)) AS c5,
AVG(NULLIF(concept6,0)) AS c6
FROM KeyPad
If you want you can add condition in WHERE
similar like this according to your requirement:
SELECT AVG(concept1) AS c1
FROM KeyPad
WHERE concept1 > 0
Note: Where condition may change your desired output if grouped
wrongly with more than one column. If you want to check for individual field then useNULLIF
orCASE
multiple products filter from mysql data using php
You can try the following code snippet:
$sql="SELECT * FROM products";
extract($_POST);
if(isset($size) && isset($price))
$sql.=" WHERE size IN (".implode(',', $size).") OR price IN (".implode(',', $price).")";
else if(isset($size))
$sql.=" WHERE size IN (".implode(',', $size).")";
else if(isset($price))
$sql.=" WHERE (price IN (".implode(',', $price)."))";
$all_row=$db->query($sql);
The filtering succeeds either for size or price. If you want to match both of them use AND
instead of OR
in the first if
statement
Note: In your code if both variables are set in a case then you will get syntax error. Here comes the use of IF ELSEIF
chain.
Suppose both $price
and $size
variables are set. Then your $sql
variable will be affected by both of the IF
statements. Then your final generated query
will have two where clauses which will generate an error for bad syntax. Your generated query will more or less look like below:
SELECT .... WHERE size IN (....) WHERE price IN (...) ...
(BAD SYNTAX)
Related Topics
Regex for No More Than 5 Digits or Contain String
Sending Post Requests Without Waiting for Response
Sql Get an Id from a Column Where Ids Separated by Commas
How to Compare Comma Separated Ids on Left Join
Phpexcel Write Long Number in Cell
How to Use a Findby Method With Comparative Criteria
How to Set Variables in a Laravel Blade Template
Build a Tree from a Flat Array in PHP
How to Parse and Process Html/Xml in PHP
How to Prevent Xss With Html/PHP
Comparing Date from Variable to MySQL Date
How to Do File Upload Using Jquery Serialization
Regex for Only Allowing Letters, Numbers, Space, Commas, Periods
How to Restrict User from Downloading or Saving Pdf File
Convert Image to Base 64 String With Laravel
Convert Accented Characters to Their Plain Ascii Equivalents