How to concatenate PHP variable with MySQL column? "

Question

How to concatenate PHP variable with MySQL column? "

Navigation

#1 by (5 votes)
#2 by (-1 votes)

-1

I have the following query:

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 problem is that I have a function that comes from a class. This function requires 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 diaVencimento field of the table to mount the date. How to do this concatenation in PHP?

php mysql

asked by anonymous 30.09.2015 / 01:39

2 answers

Change the theme of a site How to sum the values of each row in an array?

score 5 · Answer 1

To concatenate two or more fields in MySQL use the 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 needed.

Hope it helps, hugs

score -1 · Answer 2

Thinking about it seems to me that I will not even need it.

A Customer has a plan and agrees to pay every day '15' for example. Then on day '7' $ for example I want to take the list of the month. As only those who have not paid and who have a grace period of 5 days after the date, would soon have to do (DiaHoje - DiaVencimento) which will give (7-15 = -8) that is less than the 5 days grace period.

In fact it's the following

The function I am using is:

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

return $diferencas;
  }

Call:

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

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

Know how many days apart they are.

But since the listing will always be done within the same month, both $diaHoje and $diaVencimento will occur in the same month, so I will not need the function just by making the simple difference between them.

Am I right in my thinking?

Well, if I'm going to take the listing for example on the 25th, the month is 09. So I'll take the date of the listing (25) and take the day of the due date (if it has not yet been paid) , this in time, otherwise it is 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;
  }
}
?>