How to search for the selected value in MySQL / PHP and HTML?

Question

How to search for the selected value in MySQL / PHP and HTML?

Navigation

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

1

Good afternoon!

How do you make a SELECT that returns the result only for the value filled in form HTML ? For example, if someone completes the TRAVEL field that returns all values that contain the completed trip. Or if someone completes the CNPJ, they return all the amounts with the CNPJ filled out. But also, if you fill in both fields, return the values that contain the TRIP and the CNPJ informed.

<html xmlns="http://www.w3.org/1999/xhtml">
<head>	                
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>VERIFICACAO DE CARGA</title>
<style type="text/css">

</style>
</head>
<body bgcolor="#FFFFF0">
<?php
$stat=null;
if (isset($_POST["status"])){
	$stat = $_POST ["status"];
}
?>
<form id="localizacao" name="localizacao" method="post" action="temp.php" onsubmit="return true;">
  <table width="100%" border="1">
      <tr><div align="center"></div></tr>
	  <tr><th colspan="5" align="center" valign="top"><h2>Pesquisa</h2></th>
	  </tr>
    <tr>
      <td>CNPJ:</td>
      <td width="835"><input name="CNPJ" type="text" id="CNPJ" size="20" maxlength="14" />
        <span class="style1">*</span> <span class="style3">somente n&uacute;meros</span></td>				
    </tr> 
	<tr>
	<td width="156">Selecione o STATUS:</td>
		<td><select name="status" id="status">
		    <option value="0"<?=($stat == '0')?'selected':''?>>Recebido</option>
  			<option value="1"<?=($stat == '1')?'selected':''?>>Em trânsito</option>
  			<option value="2"<?=($stat == '2')?'selected':''?>>Encerrado</option>
  			</select></td>
	</tr>
	<tr>
	<td>
	VIAGEM:
	</td>
	<td width="835"><input name="VIAGEM" type="text" id="VIAGEM" size="20" maxlength="14" />
	</td>
	</tr>
    <tr>
	  <td colspan="2"><p>
        <input name="pesquisar" type="submit" id="pesquisar" value="Pesquisar" /> 
        <br />
          <input name="limpar" type="reset" id="limpar" value="Limpar!" />
          <br />
          </p>
      </td>
    </tr>
  </table>
</form>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script><!--ListacadadocumentodeacordocomoCNPJeoSTATUS--><?php//PegaovalordoCNPJedostatusescolhidonapáginaanterior$CNPJ=null;$STATUS=null;$VIAGEM=null;if(isset($_POST["CNPJ"])){
		$CNPJ = $_POST ["CNPJ"];
	}
	if (isset($_POST["status"])){
		$STATUS = $_POST ["status"];
	}	
    if (isset($_POST["VIAGEM"])){
		$VIAGEM = $_POST ["VIAGEM"];
	}	
	
	include("conexao.php");
	//Faz a consulta de acordo com o CNPJ e o status	
	
	$sql_dtc = mysqli_query($cx, "SELECT * FROM DTC WHERE DTC_CGC='$CNPJ' AND DTC_STATUS='$STATUS' AND DTC_NUMVGA='$VIAGEM'");
	
	echo '<table width="100%" border="1">';
	echo '<thead><tr>';
	echo '<th align="center">CNPJ</th>';
	echo '<th align="center">Data rec</th>';
	echo '<th align="center">Tipo Doc</th>';
	echo '<th>N&ordm; Doc</th>';
	echo '<th>Quant. Vol</th>';
	echo '<th>Processo</th>';
	echo '<th>Loc. Ent.</th>';
	echo '<th>Fornec</th>';
	echo '<th>Status</th>';
	echo '</tr></thead>';
	
	echo '<tbody>';
	while($aux = mysqli_fetch_assoc($sql_dtc)) { 
		echo '<tr>';
		echo '<td>'.$aux["DTC_CGC"].'</td>';
		echo '<td>'.$aux["DTC_DATREC"].'</td>';
		echo '<td>'.$aux["DTC_TIPDOC"].'</td>';
		echo '<td>'.$aux["DTC_NUMNFC"].'</td>';
		echo '<td>'.$aux["DTC_QTDVOL"].'</td>';
		echo '<td>'.$aux["DTC_PROCES"].'</td>';
		echo '<td>'.$aux["DTC_CDRDES"].'</td>';
		echo '<td>'.$aux["DTC_FORNEC"].'</td>';
		if ($aux["DTC_STATUS"]==0){
			echo '<td>'.'Recebido'.'</td>';
		}
		elseif ($aux["DTC_STATUS"]==1){
			echo '<td>'.'Em trânsito'.'</td>';
		}
		else{
			echo '<td>'.'Encerrado'.'</td>';
		}
		//echo '<td align=center><a href=edita.php?cnpj='.$aux['DTC_CGC'].'&tipdoc='.$aux['DTC_TIPDOC'].'&doc='.$aux['DTC_NUMNFC'].'><img src=img/editar.png></a></td>';			
		echo '</tr>';
		}
	echo '</tbody></table>';


?>	
</body>
</html>

php mysql html

asked by anonymous 16.07.2018 / 19:01

2 answers

friendly url rewrite How to perform an "IF" by SQL

score 1 · Answer 1

I do not know the Mysqli API very well, but I would concatenate SQL according to the query and add in another variable the parameters that I will use for the query. Then it would execute with bind_param to ensure the security of the query.

It should look like this:

$sql = 'SELECT * FROM DTC WHERE ';

$parametros = [];

$sql = "SELECT * FROM DTC WHERE 1 = 1";

if (isset($_POST["CNPJ"])) {

    $sql .= " AND DTC_CGC=?";

    $parametros[] = $_POST["CNPJ"];
}
if (isset($_POST["status"])){

    $sql .= " AND DTC_STATUS=?";

    $parametros[] = $_POST["status"];
}   
if (isset($_POST["VIAGEM"])){

    $sql .= " AND DTC_NUMVGA=?";

    $parametros[] = $_POST["VIAGEM"];
}   

$stmt = mysqli_prepare($cx, $sql) or die(mysqli_error($cx));

call_user_func_array([$stmt, 'bind_param'], $parametros);

mysqli_stmt_execute($stmt) or die(mysqli_error($cx));

while($aux = mysqli_fetch_assoc($stmt)) { 
    //...
}

score -3 · Answer 2

In case of your example the name of your page must be temp.php.

Because of the way you are doing it will make the request to own page.

I took into consideration that the status will always have value according to the example.

Make the changes below that if the rest is correct you will do what you need.

$CNPJ=null;

$STATUS=null;
$VIAGEM=null;       
if (isset($_POST["CNPJ"]){
    $CNPJ = $_POST ["CNPJ"];
}
if (isset($_POST["status"])){
    $STATUS = $_POST ["status"];
}   
if (isset($_POST["VIAGEM"])){
    $VIAGEM = $_POST ["VIAGEM"];
}   

$STATUS = $cx->real_escape_string($STATUS);

$condicao = '';

if( $CNPJ != '' AND strlen($CNPJ) == 14 )
{
  $CNPJ = $cx->real_escape_string($CNPJ);
  $condicao = " AND DTC_CGC = '$CNPJ'";     
}

if( $VIAGEM != '' )
{
  $VIAGEM = $cx->real_escape_string($VIAGEM);
  $condicao .= " AND DTC_NUMVGA='$VIAGEM'";     
}

$sql_dtc = mysqli_query($cx, "SELECT * FROM DTC WHERE DTC_STATUS='$STATUS' $condicao ");

I hope I have helped.