Differences between SELECT, Count and Empty to work DB data

Asked

Viewed 114 times

0

I, out of curiosity would like to know the differences between 3 seemingly equal things to count (Rows, lines) and get data from a table, in Mysql/PHP:

1st: Return the number of Rows first, and only then work the data of these Rows, EX with PDO:

function count_rows() {
    $sql = ("SELECT * FROM `{$table}`");
    $data = $this->_db->prepare($sql);

    $data->execute();
    $data_rows = $data->rowCount();
    return $data_rows;
}

function select_data() {
    $sql = ("SELECT * FROM `{$table}`");
    $data = $this->_db->prepare($sql);

    $data->execute();
    return $data->fetchAll(PDO::FETCH_OBJ);
}


if(count_rows() > 0) {
    //trabalhar dados
    echo select_data[0]->id;

}

2nd: Just use the function to select the data, and then use it in PHP using the function count to check for data (Rows), which will make the count_rows function above dispensable (at least in this example), we also check whether the returned array (Rows) is empty:

function select_data() {
    $sql = ("SELECT * FROM `{$table}`");
    $data = $this->_db->prepare($sql);

    $data->execute();
    return $data->fetchAll(PDO::FETCH_OBJ);
}

if(count(select_data()) > 0) {
    //trabalhar dados
    echo select_data()[0]->id;

}

3rd: This way we don’t count, but we can see if the array (Rows) returned from the function select_data() is empty or not, which already makes it possible to work the received data:

function select_data() {
    $sql = ("SELECT * FROM `{$table}`");
    $data = $this->_db->prepare($sql);

    $data->execute();
    return $data->fetchAll(PDO::FETCH_OBJ);
}

if(!empty(select_data())) {
    //trabalhar dados
    echo select_data()[0]->id;

}

What I wanted to know with this question is if there is any advantage/disadvantage in the use of any of them (knowing clearly that the latter is not used to count, but already checks if there is information), or we can use any one according to our needs without any hindrance?

php mysql performance

  • Hello, I didn’t understand it very well, but advantages/disadvantages it will depend on how your script works and what it will do, for example if you do a query in db and this returns empty, and soon after you have a loop, it will probably display some error, then it is better to process the data before leaving by making a loop where there is no data. but could you explain your question better? now I’m curious.

1 answer

1


In fact none of these options mentioned in the question has a good performance. Because it is performing the operation twice, in the if and in data manipulation. It is better to store the entire result in a variable and work on it:

function select_data() {
    $sql = ("SELECT * FROM `{$table}`");
    $data = $this->_db->prepare($sql);

    $data->execute();
    return $data->fetchAll(PDO::FETCH_OBJ);
}


if(count($dados = select_data()) > 0) {
    //trabalhar dados
    echo $dados[0]->id;
}

So the query will be executed only once, and you will have all the values in the variable $dados, without having to fetch the data once to test the quantity and again to work.

PHP also already performs logical operations over values, for example:

Array('Hello World') == TRUE
Array()              == FALSE
NULL                 == FALSE
0                    == FALSE
1                    == TRUE
'0'                  == FALSE
'1'                  == TRUE

Based on this we can then use as follows:

if($dados = select_data()) {
    //trabalhar dados
    echo $dados[0]->id;
}

The method fetchAll of the PDO can return a array empty, or false in case of failure. Then in both cases the result will be equal. Using a function like count you get the same result, but with a little more code.

Browser other questions tagged php mysql performance

You are not signed in. Login or sign up in order to post.