Php and MySQL Filter

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 use NULLIF
or CASE

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



Leave a reply



Submit