php - Query Database with Where Clause depending on array values

702

You must have seen this in WordPress, how you pass arguments to function and it queries the database depending on the arguments you have passed. If the arguments are not passed. It will nautically fall back to the default arguments. Exactly same thing I want to do here in my custom function.

 function get_module( $args = NULL ){
    $defaults = array(
        "module_id" => NULL,
        "module_slug" => NULL,
        "module_parent" => NULL,
        "module_status" => "publish"
    );
    global $db;
    global $table_prefix;
    $sql = "SELECT * FROM $table_prefix" . "modules";
    $query = $db->SELECT($sql);
    return $db->FETCH_OBJECT();
}

746

Answer

Solution:

Ok, so lets do this step-by-step.

If the arguments are not passed. It will nautically fall back to the default arguments

You already have a$defaults array, now the question is how to get it "together" with$args - the solution is using . If you do merge$args and$defaults, you need to watch out that$args' elements override$defaults - not the other way round. (fiddle)

$options = array_merge($defaults, $args) // good
$options = array_merge($args, $defaults) // bad

Now the question is: Do you want to query the database for entries where the value isNULL? I don't think so, comes in handy now to delete those elements not needed. This little snippet should already do the trick:

function notNull($value) {
    return $value !== NULL;
}
$options = array_filter($options, "notNull");

Now you have an array$options which should be used in the query. To do so you need to loop over the array, as you need both key and value for each item. While doing this you can already build yourWHERE part.

$where = "";
if (count($options) > 0) {
    foreach ($options as $key=>$value) {
        if (!empty($where))
            $where .= " AND ";
        else
            $where = " WHERE ";

        $where .= sprintf("`%s` = `%s`", $key, $value);
    }
}

This will automatically convert values to string due tosprintf(), you can add additional checks (numeric values should not be quoted in MySQL I believe).

You could also use a prepared statement which will be created in the loop and set the values afterwards.

People are also looking for solutions to the problem: php - Apache mod_rewrite query strings

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question