search in different fields with different inputs mysql php

Question

search in different fields with different inputs mysql php

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

Good morning, I have tried to perform a search to a database in the following way .. I have a database with x columns (suppose that for more ease, the database has only two columns, a column that day is called and another called month), what I want to do is that by means of an input search only in the day column and through another different input search only in the month column, if I want to search only in day there is no problem, if I want to search only in month there is no problem and if I want to search both day and month at the same time there is no problem. Here I attach the code that I have and it does not work :(

This is the part of the code of the page that performs the searches (where the two input are):

<title>busqueda</title>
<p>
<form name="form1" method="POST" action="busqueda.php" id="cdr">
<h2>Buscar Usuario</h2>
<input name="dia" type="text" id="busqueda">
<input name="mes" type="text" id="mesqueda">
<input type="submit" name="submit" value="Buscar"> 
</p>
</form>

This is the part of the code with which php and mysql are linked and with which the search is made to the database (I am 100% sure that $ query must be corrected, which is where the search is made in the base of data, but I do not know how to do it so that it is as I want):

<?php
error_reporting(E_ALL ^ E_NOTICE);
$dia="";
$mes="";
$dia=$_POST['dia'];
$mes=$_POST['mes'];
$con=mysqli_connect('localhost','root','');
mysqli_select_db($con,"tutorial");
if(($dia And $mes) !=""){
 $query = "SELECT * FROM entrada WHERE dia LIKE '%$dia%' and mes LIKE 
 '%$mes%'";
 $busqueda=mysqli_query($con,$query);
}
?>

And this is the part of the html code that creates the table where the results of the search will be embedded and the php that embeds these search results in the table:

<table width="805" border="1">
<tr>
    <td width="75">Día</td>
    <td width="136">Mes</td>
    <td width="225">Año</td>
</tr>

<?php
while($muestra=mysqli_fetch_array($busqueda)){
echo'<tr>';
echo'<td>'.$muestra['dia'].'</td>';
echo'<td>'.$muestra['mes'].'</td>';
echo'<td>'.$muestra['anio'].'</td>';
}
?>
</table>

Any help will be very grateful.

mysql html php

asked by julian vargas 22.03.2018 в 01:30

source

2 answers

0

The concept is Query Builder or "query builder". Basically you are building the sql of the query according to the fields that you have available.

Note1: "available" in this case is not an empty string value or has not been sent by post
Note2: use realescape by custom but how is expected that both day and month are numbers really sanitize the input data using intval
Note3: I keep the dia LIKE '%$dia%' but being numbers with a dia=$dia it would be enough

1 to search by day = A, month = B, or both matching fields (day = A and month = B)

 $dia=isset($_POST['dia'])?trim($_POST['dia']):""; // dia es lo que se recibe o cadena vacía
 $dia=isset($_POST['mes'])?trim($_POST['mes']):""; // mes es lo que se recibe o cadena vacía

 $con=mysqli_connect('localhost','root','');
 mysqli_select_db($con,"tutorial");

 $SQL="SELECT * FROM entrada WHERE ";
 if (""==$dia&&""==$mes) : // si no hay dia ni mes definidos mostramos todo 
   $where = "1"; // (cambiar a 0=1 para no mostrar nada)
 else : // dia y mes estan definidos o uno solo de los dos
   $where = "";
   $separador = ""; // la primer condicion no lleva separador

   if (""!=$dia) : // si dia está definido lo agregamos al where
     $dia = mysqli_real_escape_string($con, intval($dia));  // escapeamos el valor convertido a número
     $where .= $separador." dia LIKE '%$dia%'";
     $separador = " AND ";
   endif;

   if (""!=$mes) : // si mes está definido lo agregamos al where
     $mes = mysqli_real_escape_string($con, intval($mes)); // escapeamos el valor convertido a número
     $where .= $separador." mes LIKE '%$mes%'"; 
     //$separador = " AND "; // esta línea no es necesaria con dos columnas pero si para mas ifs si hay mas campos
   endif;

 endif;

 // completamos el SQL
 $query = $SQL.$where.";";
 $busqueda = msqli_query($con, $query);

2 to search by day = A, month = B or any of the matching fields (day = A or month = B)

change where it says $separador = " AND "; by $separador = " OR ";

answered by 01.04.2018 в 22:39

Create a percolator with elasticsearch-dsl-py Get values from a table - jquery - ajax

score 1 · Accepted Answer

Probe el query y no me marco ningun error, intenta algo asi
<?php
if(!empty($dia) && !empty($mes)){
?>
<table width="805" border="1">
<thead>
<tr>
    <td width="75">Día</td>
    <td width="136">Mes</td>
    <td width="225">Año</td>
</tr>
</thead>
<tbody>
<?php
    $s = "localhost";
    $bd = "nombreBD";
    $u = "root"; //usuario
    $p = ""; //contraseña

    $conexion = new mysqli($s, $u, $p, $bd);
    if ($conexion->connect_errno) {
        echo "no conectado";
    }
    $conexion->real_query("SELECT * FROM entrada WHERE dia LIKE '%$dia%' and mes LIKE '%$mes%'"); 
    $resultado = $conexion->use_result();
    while ($muestra = $resultado->fetch_assoc()) { ?>
    <tr>
      <td><?php echo $muestra['dia']; ?></td>
      <td><?php echo $muestra['mes']; ?></td>
    </tr>
<?php }
$resultado->close(); 
?>
 </tbody>
 </table>
<?php
}
?>