How to concatenate PHP variable with Mysql column?"

Question

I have the following appointment:

function retornaClientesDevedores ($mesReferencia, $dias) {

 require_once "PhpUtil.php"; 
 $PhpUtil = new PhpUtil();  

 $string = "SELECT idClientes, nome FROM clientes WHERE idClientes IN ( 
     SELECT idClientes FROM planosclientes WHERE idPlanosClientes IN (
     SELECT distinct(idPlanoClientes) FROM pagamentos WHERE mesReferencia NOT IN ('".$mesReferencia."') 
      ) and ".$PhpUtil->diferencaDatas(date('Y-m-d'), date('Y-m')."-diaVencimento'".)->days." > ".$dias."
      UNION
     SELECT idPlanosClientes FROM planosclientes WHERE idPlanosClientes NOT IN                                               (SELECT distinct(idPlanoClientes) FROM pagamentos)
                                             ) ";         
    }

My difficulty is that I have a function that comes from a class. This function asks for two dates. One current and one that I need to get the current date without the day, so date('Y-m') and concatenate with a field diaVencimento table to mount the date. How to do this concatenation in PHP?

Answer 1

To concatenate two or more fields in Mysql use the function CONCAT as follows:

$sql = "SELECT CONCAT('".date('Y-m')."', '-', diaVencimento) AS data FROM pagamentos";

or

$sql = "SELECT CONCAT('".date('Y-m-')."', diaVencimento) AS data FROM pagamentos";

Apply as your need.

I hope it helps, hugs

Answer 2

On second thought, I guess I won’t even need this.

A Customer has a plan and undertakes to pay every day '15' for example. So on day '7'$ for example I want to take the list of the month. As only enter those who have not yet paid and who has a grace 5 days after the date, soon Would do (diaHoje - Diaexpiration) what will give (7-15 = -8) which is less than the 5 days of need.

As a matter of fact,

The function I’m using is:

  function diferencaDatas ($dataInicio, $dataFim) {
$dataInicio = new DateTime($dataInicio);
$dataFim  = new DateTime($dataFim);
$diferencas = $dataInicio->diff($dataFim);

return $diferencas;
  }

Calling for:

$dataHoje = date('Y-m-d');
$dataVencimento = date('Y-m')."-dataVencimento ";

$diasDiferenca = diferencaDatas ($dataHoje , $dataVencimento ) ->days;

Know how many days difference is.

But as the listing will always be made within the same month, so much $diaHoje as to the $diaVencimento will occur in the same month, so I won’t need the function just by making the simple difference between them.

I’m right in my thinking?

For if I’m going to take the listing for example on the 25th, the month is 09. Soon, I will take the listing date (25) and take the due day (if it has not yet been paid) if you give up 5, this on time, otherwise, this in debt.

corrected class.

<?php
 class Relatorios {

private $conexao;

public function __construct ($_conexao) { 
    $this->conexao = $_conexao;
}
/*
     //COMENTÁRIO DA FUNÇÃO ABAIXO
//SELECIONAR OS ID'S E OS NOMES DOS CLIENTES
$string = "SELECT idClientes, nome FROM clientes WHERE idClientes IN ( 
                   //SELECIONAR ID'S DOS CLIENTES QUE TEM PLANOS E QUE A DIA DE VENCIMENTO É MENOR QUE A O DIA ATUAL
SELECT idClientes FROM planosclientes WHERE idPlanosClientes IN (
   //MAS NÃO PAGARAM A PARCELA DO MES CORRENTE 
SELECT distinct(idPlanoClientes) FROM pagamentos WHERE mesReferencia NOT IN ('".$mesReferencia."') 
                  ) and DATEDIFF (curdate(), CONCAT('".date('Y-m')."','-', diaVencimento)) > ".$dias."
  //UNIÃO COM
UNION
    //SELECIONAR ID'S DOS CLIENTES QUE TEM PLANOS E QUE NÃO PAGARAM NENHUMA PARCELA
SELECT idPlanosClientes FROM planosclientes WHERE idPlanosClientes NOT IN                                               (SELECT distinct(idPlanoClientes) FROM pagamentos)
  ) ";         
*/

 function retornaClientesDevedores ($mesReferencia, $dias) {

$retorno = array();

$string = "SELECT DISTINCT(idClientes), nome FROM clientes WHERE idClientes IN ( 
              SELECT idClientes FROM planosclientes WHERE idPlanosClientes IN (
SELECT distinct(idPlanoClientes) FROM pagamentos WHERE mesReferencia NOT IN ('".$mesReferencia."') 
) and DATEDIFF (curdate(), CONCAT('".date('Y-m')."','-', diaVencimento)) > ".$dias."
UNION
SELECT idPlanosClientes FROM planosclientes WHERE idPlanosClientes NOT IN                                               (SELECT distinct(idPlanoClientes) FROM pagamentos)
  ) ORDER BY nome";        

$dados =  $this->conexao->query($string);   

if($dados->num_rows == 0) {
$retorno = null;
}
else {
$contador = 0;
while ( list ($idClientes, $nomeClientes) = $dados->fetch_row())   {
$retorno[$contador][0] = $idClientes;
$retorno[$contador][1] = $nomeClientes;
$contador++;
}
}

      return $retorno;
  }
}
?>