Ignore certain values in Mysql

Question

$query = "SELECT COUNT(status) FROM $nomeTabela WHERE status=$numeroStatus AND nomePessoa='$nomePessoa'";

Can you optimize this? There is status of 0 to 10 (in this query I need to query only in the status of 1 to 10), I wanted to ignore the 0 for consultation to be even faster, there are a few more tips?

foreach ($arrayNomes as $nome) {
    $status1 = retornaStatus(1,$nome,$nomeTabela);
    $status2 = retornaStatus(2,$nome,$nomeTabela);
    $GLOBALS['str'] .=
        "<tr>".
        '<td>'.$nome."</td>".
        '<td>'.$status2."</td>".
        '<td>'.$status1."</td>".
        "<tr>";

}
echo $GLOBALS['str'];

Inside the function has this query posted above all.

Answer 1

User the NOT to eliminate unwanted values

$query = "SELECT COUNT(status) 
    FROM $nomeTabela 
    WHERE status=$numeroStatus AND nomePessoa='$nomePessoa' AND status NOT IN(0)";

Answer 2

You are already filtering the status here status=$numeroStatus so if you add some other operator, example status=$numeroStatus and status <> 0, nay will help in anything because it is already filtering before.

Now if you want to catch all as long as the status is different of 0 you don’t need to have the status=$numeroStatus. Staying:

$query = "SELECT COUNT(status) FROM $nomeTabela 
          WHERE status <> 0 AND nomePessoa='$nomePessoa'";

Answer 3

To optimize, you can use PDO and create methods to execute your query. I didn’t get it right, if you want to display the status total, or if you want to display each of the status. But if to show the total, follow an example:

function query($sql, $params = array(), true)
{ 
  try {
        $conn = new PDO('mysql:host=localhost;dbname=seubanco',
        'root', 'senha123');
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $stmt = $conn->prepare($sql);
        $stmt->execute($params);
        $result = ($all) ? $stmt->fetchAll() : $stmt->fetch();
        return $result;

  } catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage(); die();
  }

}

function selectTotalStatus($nome, $tabela)
{
$sql = " SELECT COUNT(status) as TOTAL
         FROM :tabela
         WHERE status BETWEEN 1 AND 10
         AND nomePessoa = :nome";

$data_query = array(
              'tabela' =>$tabela,
              'nome'   =>$nome);


$data_result = query($sql, $data_query, $all = true);

$content = [];

    foreach ($data_result as $result) {

       $content[] = "<tr>".
            '<td>'.$result['nomePessoa']."</td>".
            '<td>'.$result['TOTAL']."</td>".
            "<tr>";
 return $content;
}

}
$content = selectTotalStatus('Luiz', 'nome_da_tabela');

echo '<table>'.implode("\n", $content).'</table>';