Ignore certain values in MySQL

Question

Ignore certain values in MySQL

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

0

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

Can you optimize this? There are statuses from 0 to 10 (in this query I need to query only on statuses from 1 to 10 ), I wanted to ignore the 0 for query to be even faster, there some 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 else.

php mysql

asked by anonymous 30.06.2015 / 20:14

3 answers

How to make a lightbox with CSS and jQuery? List method does not return objects

score 1 · Answer 1

1

User or NOT to remove unwanted values

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

30.06.2015 / 20:21

score 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 , will not help anything because it is already filtering before.

Now if you want to get everyone since the status is different from 0 ai you do not need status=$numeroStatus . Staying:

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

score 0 · Answer 3

To optimize, you can use PDOs and create methods to execute your query. I do not get it right if you want to display the total status, or if you want to display each of the statuses. But if it is to show the total, an example follows:


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>';