How to make an empty select to pull all results from column

Asked

Viewed 1,625 times

2

I’m trying to make an empty select, which if no option selected pulls all the results, using the AND in the SELECT form forces me to choose some option, how can I resolve this? Follows the codes:

<form action="index2.php" method="post" >
<input type="hidden" name="submitted" value="true">

<label for="">Nome:
    <input type="text" name="nome" />
</label>

<label for="">Tipo:
    <select name="tipo" id="">
        <option value=""></option>
        <option value="P">Personagem</option>
        <option value="R">Reino</option>
        <option value="I">Item</option>
        <option value="A">Ação</option>
    </select>
</label>

<label for="">Vida: 
    <input type="text" name="vida">
</label>

<label for="">Força: 
    <input type="text" name="forca">
</label>

<input type="submit">

</form>

--

$nome = $_POST['nome'];
$tipo = $_POST['tipo'];
$vida = $_POST['vida'];
$forca = $_POST['forca'];
$query = "SELECT * 
FROM WoH 
WHERE NOME LIKE '%".$nome."%' 
AND `TIPO` = '$tipo' 
AND `VD` = '$vida'
";

The search does not work because there is no "" line in my TYPE column. If I take the empty option, leaving the character option as default, it only looks for the characters, how can I do so that somehow when the user wants to search the cards in ALL types, the default option brings me this result?

php mysql mysqli

  • Unrelated to the question problem (already answered by @Jorgeb.), your query gives opportunity to SQL Injection. Then take a look at this question: http://answall.com/questions/3864/

  • I have the following function in this file, which I did not paste here. Function escape($string){ Return htmlentities(Trim($string), ENT_QUOTES, 'UTF-8'); } This resolves, not?

  • 1

    This is not exactly the case, even htmlentities serve to generate the display for use in HTML, and not in the database. For example, htmlentities converts < for &lt;, and so it goes. The ideal would be to use Binding, as described in the link. The way you did, even "gives a good disguise", but gets a little confused. mysqli does this automatically for you, just use stmt bind_param: http://www.php.net/manual/en/mysqli-stmt.bind-param.php

2 answers

2


You can do it through PHP this way:

$nome = $_POST['nome'];
$tipo = $_POST['tipo'];
$vida = $_POST['vida'];
$forca = $_POST['forca'];

$query = "SELECT * 
FROM WoH 
WHERE NOME LIKE '%".$nome."%' AND `VD` = '$vida'";
if($tipo!="")
{
   $query .= " AND `TIPO` = '$tipo'"; //junção sql do tipo no fim da query.
}

  • If I need to use this blank value scheme to pull everything with various text type input’s, would it be recommended to use this same form by doing several if’s? Like the value of LIFE and STRENGTH also need to work the same way, only considering if something is typed in the input.

  • Yeah, if it’s like in this case you can do it anyway, if you just want to use that condition in case input be filled. Always be careful with the spaces between SQL conditions both if the if condition is satisfied or if it is not, it usually gives some problems.

2

Never insert POST/GET parameters directly into the SQL string. This allows SQL Injection. You can use mysql_real_escape_string to handle the string so that it is safe and then mount the final query:

$nome = mysql_real_escape_string($_POST['nome']);
$tipo = mysql_real_escape_string($_POST['tipo']);
$vida = mysql_real_escape_string($_POST['vida']);
$forca = mysql_real_escape_string($_POST['forca']);

$query = 'SELECT * FROM WoH '.
         'WHERE NOME LIKE "%'.$nome.'%" '.
         (empty($vida)?'':'AND VD = "'.$vida.'" '.
         (empty($tipo)?'':'AND TIPO = "'.$tipo.'" ';

Heed: mysql_real_escape_string was deprecated in PHP 5.5. The best solution is to use Prepared statements.

Browser other questions tagged php mysql mysqli

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