CODEWITHSUNDEEP
phpmysqlarrays
Leandro Fabris Milani
Leandro Fabris Milani

Reputation: 53

Is it more efficient to make only one query and store it in an array or make a query every time?

I'd like to know what is more efficient, make just one query and store the result in an array and then check this array using the function in_array from PHP or make a MySQL query every time I need to check the value?

For example: I created this function to store the query in a array, so this query will be made just one time.

 private function carregaPermissoes(){
        $query = mysql_query("SELECT permissao_nome FROM sisgrupopermissoes WHERE id_grupo = ".$this->getGid().";");
        if (mysql_num_rows($query) > 0){
            $array_permissoes = array();
            while ($row = mysql_fetch_array($query)){
                array_push($array_permissoes, $row["permissao_nome"]);
            }
            return $array_permissoes;
        }
    }

Then every time I need to check it I just use this function:

public function checkPermissao($permissao){
        $this->setPermissao($permissao);
        if (in_array($this->getPermissao(), $this->getGrupoPermissao())){
            return true;
        }else{
            return false;
        }
    }

Is it a good way to do it? Or is better make a query every time I need to check it?

Upvotes: 0

Views: 122

Answers (2)

Rakesh Jakhar
Rakesh Jakhar

Reputation: 6388

You are storing the permission in an array and checking inside. What if someone/admin change the permission, at this time you have the old permission in your array, as per my understanding this is not a good practice to use array.

You can execute the query like

$query = mysql_query("SELECT permissao_nome FROM sisgrupopermissoes WHERE id_grupo = ".$this->getGid()." AND permissao_nome='".$this->getPermissao()."'");

and use mysql_num_rows to check the condition

return mysql_num_rows($query)

Upvotes: 0

ntd
ntd

Reputation: 7434

SQL queries are slow, typically the most common bottleneck after the network. Your function could be simplified into:

public function checkPermissao($permissao)
{
    // The following line smells...
    $this->setPermissao($permissao);
    return in_array($this->getPermissao(), $this->getGrupoPermissao());
}

If you are afraid of the overhead introduced by in_array (that intrinsically uses a sequential search) you can flip the haystack array and use array_key_exists instead.

Upvotes: 1

Related Questions