Check with IF and INNER JOIN

Question

Check with IF and INNER JOIN

Navigation

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

0

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 where the mesas column of the pedidos table is equal to the id column of the mesas table. If the result is positive it gives a echo with a value, otherwise it gives a echo with another value.

I combed this way would work:

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

But unfortunately it did not work. Can someone explain to me the correct way to do this, I'm a beginner, I do not have much experience yet.

Here is the structure of my tables ...

php mysql

asked by anonymous 02.11.2015 / 06:22

2 answers

0

Hello, you should post the schema of your database.

But come on. Assuming that in your database pedidos.mesas and mesas.id are same type, then we would 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.* , should return by name each column you'll need. See the result in phpMyAdmin:

SoputtingeverythingtogetherwithPHPlookslikethis:

<?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
          }
    ?>

02.11.2015 / 14:20

How to get a string arraylist value, for an array of File How to render a jsf component through Bean?

score 2 · Accepted Answer

Try using MYSQL's on, below 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";
          }
     }

Consider also studying the PDO's USE, MySQL_ * is already an obsolete function of php.