Consultation system

Asked

Viewed 55 times

1

I have this form to consult only for a period of time or only by the client’s name or to consult for a period of time and a specific client:

<form method="POST" action="">
    <strong>Data Início:</strong> <input type="date" name="inicio" placeholder="Data Início">
    <strong>Data Fim:</strong> <input type="date" name="fim" placeholder="Data Fim">
    <strong>Utente:</strong> <input type="text" name="nome" placeholder="Utente">   
    <input type="submit" value="Consultar">
</form>

Code:

$inicio = $_POST['inicio']; 
$fim = $_POST['fim'];   
$nome= $_POST['nome'];

$result_cursos = "SELECT centrodb.utentes.codigoutente,
centrodb.utentes.nome,
centrodb.utentes.descricaovalencia,
centrodb.registoFisioterapia.`DataRegisto`,
centrodb.registoFisioterapia.`Data`,
Inicio,
Fim,
centrodb.colaboradores.Nome AS Colaborador,
TimeDiff(TIME_FORMAT(Fim,'%H:%i'), TIME_FORMAT(Inicio,'%H:%i')) AS `Horas Consumidas`

FROM centrodb.registoFisioterapia LEFT OUTER JOIN centrodb.utentes

ON centrodb.utentes.Id = centrodb.registoFisioterapia.utente

LEFT OUTER JOIN colaboradores

ON centrodb.colaboradores.codigo = centrodb.registoFisioterapia.Nome WHERE centrodb.registoFisioterapia.`Data` >= '$inicio' OR centrodb.registoFisioterapia.`Data` <= '$fim' OR centrodb.utentes.nome LIKE '%$nome%'";

$resultado_cursos = mysqli_query($conn, $result_cursos);

$tabela3 .= '<div style="float: center" table align="center">';
$tabela3 .= '<table border="5">';

$tabela3 .= '<tr>';

$tabela3 .='<thead>';

$tabela3 .= '<tr>';

$tabela3 .= '<th>Nº Utente</th>';

$tabela3 .= '<th>Utente</th>';

$tabela3 .= '<th>Valência</th>';

$tabela3 .= '<th>Data Registo</th>';

$tabela3 .= '<th>Data</th>';

$tabela3 .= '<th>Hora Início</th>';

$tabela3 .= '<th>Hora Fim</th>';

$tabela3 .= '<th>Fisioterapeuta</th>';

$tabela3 .= '<th>Horas Consumidas</th>';

$tabela3 .= '</tr>';

$tabela3 .='</thead>'; 

$tabela3 .='<tbody>';

if (empty($resultado_cursos)) {
    echo "Nenhum registro encontrado.";
}

while ($rows_cursos = mysqli_fetch_array($resultado_cursos)) {
$tabela3 .= '<tr>';

$tabela3 .= '<td>'.$rows_cursos['codigoutente'].'</td>';

$tabela3 .= '<td>'.$rows_cursos['nome'].'</td>';

$tabela3 .= '<td>'.$rows_cursos['descricaovalencia'].'</td>';

$tabela3 .= '<td>'.$rows_cursos['DataRegisto'].'</td>';

$tabela3 .= '<td>'.$rows_cursos['Data'].'</td>';

$tabela3 .= '<td>'.$rows_cursos['Inicio'].'</td>';

$tabela3 .= '<td>'.$rows_cursos['Fim'].'</td>';

$tabela3 .= '<td>'.$rows_cursos['Colaborador'].'</td>';

$tabela3 .= '<td>'.$rows_cursos['Horas Consumidas'].'</td>';

$tabela3 .= '</tr>'; 
}

$tabela3 .= '</tr>';

$tabela3 .='</tbody>'; 

$tabela3 .= '</table>';

$tabela3 .= '</div>';

echo $tabela3;

?>

When I search only for the start date and end date returns the correct data, but if I search only by the client name or then search by the time interval and client name does not return me the correct data, keeps the data that are initially.

php html mysql

  • no query returns values? nor filling everything?

  • 1

    The query in mysql returns values...this means that there is no problem in the query, but then when I mount the system in php does not work... I intend to filter either just for a period of time, or just by the customer’s name or else do both filters simultaneously. It won’t have anything to do with the where?

  • It has to do with null values ne, when you run the WHERE centrodb.registoFisioterapia.'Data' >= '$inicio', for example, but did not fulfill the date, it does not return anything, because a condition failed

  • Another thing, this field Data, just like in the example I just quoted, it needs quotation marks?

  • 1

    It’s already returning, there was an error in the while, it was while ($rows_cursos = mysql_fetch_array($resultado_cursos)) {, but it has to be like this while ($rows_cursos = mysqli_fetch_array($resultado_cursos)) {. But still I have the problem because I can only filter by date, the filter by the customer name or by the time interval and customer name do not work.

  • 1

    Variable $nome, last OR, was defined where ?

  • 1

    Okay, there was another mistake... I can already filter individually, both by the time interval and by the client’s name, but I still can’t filter both simultaneously, by the time interval and by the client’s name

  • Let’s go continue this discussion in chat.

  • is because if a condition OR for valida, it will already run this query, to have the 3 together would have to be AND, checks if all are filled and creates another sql with AND

Show 4 more comments

1 answer

1


Creating the conditioning for the $nome relating to the dates:

$inicio = $_POST['inicio'];
$fim = $_POST['fim'];
$nome = $_POST['nome'];


if((empty($inicio))||(empty($fim))&&(empty($nome))){
    $condicao = 'OR';
}else{
    $condicao = 'AND';
}

$result_cursos = "SELECT
centrodb.utentes.codigoutente,
centrodb.utentes.nome,
centrodb.utentes.descricaovalencia,
centrodb.registoFisioterapia.`DataRegisto`,
centrodb.registoFisioterapia.`Data`,
Inicio,
Fim,
centrodb.colaboradores.Nome AS Colaborador,
TimeDiff(TIME_FORMAT(Fim,'%H:%i'), TIME_FORMAT(Inicio,'%H:%i')) AS `Horas Consumidas`

FROM centrodb.registoFisioterapia LEFT OUTER JOIN centrodb.utentes

ON centrodb.utentes.Id = centrodb.registoFisioterapia.utente

LEFT OUTER JOIN colaboradores

ON centrodb.colaboradores.codigo = centrodb.registoFisioterapia.Nome WHERE (centrodb.registoFisioterapia.`Data` >= '$inicio' AND centrodb.registoFisioterapia.`Data` <= '$fim') $condicao centrodb.utentes.nome LIKE '%$nome%'";

Browser other questions tagged php html mysql

You are not signed in. Login or sign up in order to post.