Compare two fields between different tables

Question

Compare two fields between different tables

Navigation

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

0

I have a big problem. It is the following I have the Script below:

<?php
$contarperfilclientecontar = "0";
$sqlxml = "SELECT id
                  ,vvenda
             FROM imoveis
            WHERE cod = '1042'
              AND status = '2'
          ";
$rsqlxml = mysql_query($sqlxml) or die("Banco XML não abre!");

while ($rowxml = mysql_fetch_array($rsqlxml))
    {
    $vartestevalor = $rowxml['vvenda'];
    $sqlxmlcliente = "SELECT id
                        FROM clientes
                       WHERE cliente = '1042'
                         AND disponibilidade < '$vartestevalor'
                     ";
    $rsqlxmlcliente = mysql_query($sqlxmlcliente) or die("Banco XML não abre!");
    while ($rowxmlcliente = mysql_fetch_array($rsqlxmlcliente))
        {
        $contarperfilclientecontar = $contarperfilclientecontar + 1;
        }
    }

I want the IMOVEIS table to check all properties with the VVENDA field that have MENORES values as the DISPONIBILIDADE field of the client table and list the number of records found.

Only the numbers do not beat!

Can anyone help me in this mystery?

php sql

asked by anonymous 08.03.2016 / 11:58

2 answers

How to format a string with 3 houses? Pass parameter window modal bootstrap

score 2 · Answer 1

I think you can kill everything in a query only:

SELECT imoveis.id
      ,imoveis.vvenda
  FROM imoveis
  LEFT JOIN clientes
    ON clientes.cliente = imoveis.cod
 WHERE imoveis.cod = '1042'
   AND imoveis.status = '2'
   AND clientes.disponibilidade >= imoveis.vvenda

score 0 · Answer 2

Using the @NilsonUehara query, just to "list the number of records found"

SELECT imoveis.id,
       imoveis.vvenda,
       count(*) total
  FROM imoveis
  LEFT JOIN clientes
    ON clientes.cliente = imoveis.cod
 WHERE imoveis.cod = '1042'
   AND imoveis.status = '2'
   AND clientes.disponibilidade >= imoveis.vvenda
 GROUP BY imoveis.id,
          imoveis.vvenda