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

Asked

Viewed 89 times

1

Good afternoon!

How to make one SELECTreturn the result only to the value completed in the form HTML? For example, if someone fills in the TRIP field that returns all the values that contain the completed trip. Or if someone fills in the CNPJ, return all values with the CNPJ filled in. But also, if you fill in the two 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>
	<!-- Lista cada documento de acordo com o CNPJ e o STATUS-->		
<?php 
	//Pega o valor do CNPJ e do status escolhido na página anterior
	$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 html mysql

2 answers

1

I don’t really know the API of Mysqli, but I would concatenate SQL according to the query and add in another variable the parameters I will use for the query. Then run with a bind_param to ensure the security of consultation.

It should look something 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)) { 
    //...
}

  • This code is giving the error: "Uncaught Error: Call to a Member Function execute() on null in rastreamento.php:210 Stack trace: #0 {main} thrown in rastreamento.php on line 210.

  • @Marvin I edited colleague Wallace’s code, see if it works or if it returns any specific error.

  • @Guilhermenascimento, the code returned the Warning’s: "mysqli_prepare() expects Exactly 2 Parameters, 1 Given in rastreamento.php on line 195" and "mysqli_error() expects Exactly 1 Parameter, 0 Given in rastreamento.php on line 195". Didn’t work either.

  • 1

    @Marvin adjusted the answer, change it $stmt = mysqli_prepare($sql) or die(mysqli_error()); for $stmt = mysqli_prepare($cx, $sql) or die(mysqli_error());

  • @Marvin was an AND that was left over there, removed the unnecessary part, see the answer now.

  • @Guilhermenascimento, now gives this Warning and does not work: "mysqli_error() expects Exactly 1 Parameter, 0 Given in rastreamento.php on line 228".

  • @Marvin ready, set in code ;)

  • @Guilhermenascimento, is giving the Warning’s: "Parameter 2 to mysqli_stmt::bind_param() expected to be a Reference, value Given in rastreamento.php on line 230", "Warning: Parameter 3 to mysqli_stmt::bind_param() expected to be a Reference, value Given in rastreamento.php on line 230" and "Warning: mysqli_stmt::bind_param(): Invalid type or no types specified in rastreamento.php on line 230". It is on that line: call_user_func_array([$stmt, 'bind_param'], $parametros);.

  • @Marvin I’m gonna test here

Show 4 more comments

-3

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

Because the way you’re doing will make the request to own page.

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

Make the modifications below that if the rest is correct will do the 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’ve helped.

  • 2

    -1 . It’s very easy to make an SQL Injection there, huh.

Browser other questions tagged php html mysql

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