Check with IF and INNER JOIN

Question

Guys I have these queries in the database

<?php 
    $consulta = mysql_query("SELECT * FROM mesas LIMIT 50");
    if (mysql_num_rows($consulta)==true) {
       while($lnmesas = mysql_fetch_array($consulta)){ 
       $consultainterna = mysql_query ("SELECT * FROM mesas INNER JOIN pedidos");
?> 

I need to make a comparison of a record in the database in which the column mesas table pedidos is equal to column id table mesas. If the result is positive it gives a echo with a value, otherwise he will give a echo otherwise.

I figured that way it would work:

if($lnmesas['pedidos.mesas'] == $lnmesas['mesas.id']){
    echo "success":
}else{
    echo "danger";
}

But unfortunately it didn’t work. Can anyone explain to me the correct way to do this, I’m beginner, I don’t have much experience yet.

here is the structure of my tables...

Answer 1

Try using MYSQL on, below is an example:

SELECT * FROM mesas m LEFT JOIN pedidos p ON p.mesas = m.id

Then just check with PHP

<?php

     $consulta = mysql_query("SELECT * FROM mesas m LEFT JOIN pedidos p ON p.mesas = m.id LIMIT 50") or die (mysql_error());

     while($dados = mysql_fetch_assoc($consulta)) {
          if(!empty($dados['p.id'])) {
              echo "success";
          } else {
              echo "danger";
          }
     }

Also consider studying PDO USE, MySQL_* is already an obsolete php function.

Answer 2

Hello, you should post the schema of your database.

But come on. Supposing in your database pedidos.mesas and mesas.id are the same kind, so we’d do:

SELECT IF(
          /*Condição, se não for NULL é porque há pedido*/
          NOT ISNULL(p.mesas),

          /*Se verdadeiro retornará 'success'*/
          'success',

          /*Se falso retornará 'danger'*/
          'danger'
         ) as retorno_coluna,
  m.*, /*Também retorna as colunas da tabela mesas*/
  p.*  /*Também retorna as colunas da tabela pedidos*/
FROM mesas m
/*Left Join, visto que você quer recuperar
  a lista de mesas independente do pedido*/
LEFT JOIN pedidos p ON m.id = p.mesas

Well the above query should work. But if there are columns with the same names in both tables, you, instead of returning p.* and m.*, you must return by name each column you will need. See the result in phpMyAdmin:

So combining everything with PHP is like this:

    <?php
        $consulta = mysql_query("SELECT IF(NOT ISNULL(p.mesas), 'success', 'danger') as retorno_coluna, m.*, p.* FROM mesas m LEFT JOIN pedidos p ON m.id = p.mesas");
        if (mysql_num_rows($consulta)==true)
          {
            while($lnmesas = mysql_fetch_array($consulta))
              {?>         
        <div class="col-sm-4 col-md-2">
          <div class="ls-box">
          <div class="ls-alert-<?php echo $lnmesas['retorno_coluna']; ?>">
            <h6 class="ls-title-1"><?php  echo $lnmesas['retorno_coluna']; ?></h6>
              <strong><?php echo $lnmesas['id']  ?></strong>
          </div>
        <a href="index.php?mod=info-mesa&mesaid=<?php echo $lnmesas['id'] ?>">
          <button class="ls-btn-primary ls-ico-cog">Gerenciar Mesa</button><a>  
          </div>
        </div><?php
               }
          }
        else
          {?>
       <center>
       <div class="ls-alert-warning"><strong>OPS! </strong> Não existe nenhuma mesa com pedidos, cheque se você realmente lançou seu pedido clicando na aba pedidos aqui do lado</div>
       </center><?php
          }
    ?>