Mysql Search From Multiple User Supplied Where Clause - Fix And Better Algorithm

I want to run search query where i have multiple where clause. and multiple depends upon the user argument. for example i mean, Search may depend on 1 column, 2 column, 3 column or

Solution 1:

Put all the conditions in an array. Then combine them with:

$condition_string = implode(' and ', $condition_array);

Solution 2:

The simple solution is to trim the "and" off at the end:

$query = substr($query, 0, strlen($query) - 3);

however a more efficient way would be to put them in a loop, like this:

$wheres = array("player_guest"=>$player, "group_guest"=>$group.....);
$query_where = "";
$i = 0;
foreach($wheresas$where=>$value){
    list($condition, $null) = explode("_",$where);
    if(isset($value)){
        $query_where .= $condition . "='" . $mysqli->real_escape_string($value)."'";
        if($i != sizeof($wheres)){
            $query_where .= " and ";
        }
    }
    $i++;
}

This is extendable for any number of conditions, and doesnt require the extra string function at the end.

Solution 3:

Notice the $defaults is needed to make sure your conditions work. A bit repetitive, but it's all due to your function declaration.

functionlistPlayer($player="player_guest",
    $group="group_guest",
    $weapon="weapon_guest",
    $point="point_guest",
    $power="level_guest",
    $status="status_guest") {

    //I'm just copying whatever is in the default parameters ;)$defaults = array(
        'player' => 'player_guest',
        'group' => 'group_guest',
        'weapon' => 'weapon_guest',
        'point' => 'point_guest',
        'power' => 'level_guest',
        'status' => 'status_guest'
    );

    //Set all user parameters into an array, easier to loop through$data = compact(array_flip($defaults));

    //Then we build conditions$conditions = array();
    foreach($dataas$k => $v) {
        if ($defaults[k] !== $v) {
            $v = $mysqli->real_escape_string($v);
            $conditions[] = "$k='$v'";
        }
    }

    //And build query$query = "SELECT pid, name FROM game WHERE ".implode(" AND ", $conditions);
    $runQuery = $mysqli->query($query);
}

Solution 4:

First of all I recommend you to look at PDO when you work with MySQL in PHP.

Such tasks are always simply solved with array maps.

functionlistPlayer($player = null, $group = null, $weapon = null, $point = null, $power = null, $status = null) {
    $defaults = array(
        'player' => 'player_guest',
        'group' => 'group_guest',
        'weapon' => 'weapon_guest',
        'point' => 'point_guest',
        'status' => 'status_guest',
    );
    $values = compact(array_keys($defaults));

    $filtered = array_filter(array_diff_assoc($values, $defaults)); //firstly filtering out defaults, then - nulls.$where = '';
    foreach($filteredas$column => $value){
        if($where){
            $where .= ' AND ';
        }
        $where .=  sprintf("`%s` = '%s'", $column, $mysqli->real_escape_string($value));
    }

    $query = "SELECT pid, name FROM game WHERE $where";
    //executing...
}