Geek Answers Handbook

MySQL search from several users provided where the suggestion is a fix and the best algorithm

I want to run a search query where I have some suggestions where. and somewhat depend on the user argument.

For example, I mean that the search may depend on a column of 1 column, 2 columns, 3 columns, or 6 in my case, and I do not want to run the instruction if-elseif-elsewith all probability of the column. So, I just created a function below, but I am stuck with andwhich comes in between the case multiple column search. Below is my code: -

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

    $lePlayer = (isset($player) && $player != "player_guest") ?
            'player= '.$mysqli->real_escape_string($player).' and' : '';

    $leGroup = (isset($group) && $group != "group_guest") ?
            'group= '.$mysqli->real_escape_string($group).' and' : '';

    $leWeapon = (isset($weapon) && $weapon != "weapon_guest") ?
            'weapon= '.$mysqli->real_escape_string($weapon).' and' : '';

    $lePoint = (isset($point) && $point != "point_guest") ?
            'point= '.$mysqli->real_escape_string($point).' and' : '';

    $lePower = (isset($power) && $power != "level_guest") ?
            'level= '.$mysqli->real_escape_string($power).' and' : '';

    $leStatus = (isset($status) && $status != "status_guest") ?
            'status= '.$mysqli->real_escape_string($status).' and' : '';

    $query = "Select pid, name from game where {$lePlayer} {$leGroup} {$leWeapon} {$lePoint} {$lePower} {$leStatus} ";

    $runQuery = $mysqli->query($query);
}

but the problem is in the end and. If I use them, then I have an additional one andat the end, and if I will not use them, there will be an error again.

- .

: , , - . Barmar

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

    $lePlayer = (isset($player) && $player != "player_guest") ?
            'player= '.$mysqli->real_escape_string($player) : '' ;

    $leGroup = (isset($group) && $group != "group_guest") ?
            'group= '.$mysqli->real_escape_string($group) : '' ;

    $leWeapon = (isset($weapon) && $weapon != "weapon_guest") ?
            'weapon= '.$mysqli->real_escape_string($weapon) : '' ;

    $lePoint = (isset($point) && $point != "point_guest") ?
            'point= '.$mysqli->real_escape_string($point) : '' ;

    $lePower = (isset($power) && $power != "level_guest") ?
            'level= '.$mysqli->real_escape_string($power) : '' ;

    $leStatus = (isset($status) && $status != "status_guest") ?
            'status= '.$mysqli->real_escape_string($status) : '' ;

    $condition_array = ( $lePlayer , $leGroup , $leWeapon , $lePoint , $lePower , $leStatus)

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

    $query = "Select pid, name from game where ".$condition_stirng;

    $runQuery = $mysqli->query($query);

    }

Update:

- , , SQL Injection. POC http://www.worldofhacker.com/2013/09/interesting-sql-vulnerable-code-even.html

0
4

. :

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

"" :

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

, :

$wheres = array("player_guest"=>$player, "group_guest"=>$group.....);
$query_where = "";
$i = 0;
foreach($wheres as $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++;
}

.

0

Please note what is $defaultsneeded to make sure your conditions work. It repeats somewhat, but all this is connected with the declaration of the function.

function listPlayer(
    $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($data as $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);
}
0
source

First of all, I recommend that you look at PDO when working with MySQL in PHP.

Such tasks are always simply solved using arrays of arrays.

function listPlayer($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($filtered as $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...
}
0
source

More articles:


All Articles