Differences between SELECT, count, and empty to work DB data

Question

Differences between SELECT, count, and empty to work DB data

Navigation

#1 by (1 votes)

0

I, out of curiosity, liked 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 then work the data of those 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: Only use the function to select the data, and then use them in PHP using the count function to check for rows, which will make the count_rows function above less in this example), we also check if 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: In this way we do not count, but we can see if the array (rows) returned from the select_data() function is empty or not, which already causes us to work the data received:

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 whether there is any advantage / disadvantage in using any of them (knowing clearly that the latter is not meant to count, but already checks if there is information), or we can use any one according to our needs without any hindrance?

php mysql desempenho

asked by anonymous 12.06.2015 / 15:35

1 answer

ListView is not updating, Android Is there any online server where I can submit a form made in Android with JSon to test?

score 1 · Accepted Answer

In fact none of these options mentioned in the question performs well. Because you are performing the operation twice, in%% and in data manipulation. Best is to store all the 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 if , without having to search the data once to test the quantity and again to work.

PHP also already performs logical operations on 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 the following form:

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

The $dados of PDO method can return an empty array , or false on failure. Then in both cases the result will be the same. Using a function like fetchAll you get the same result, but with a little extra code.