Marco Bagiacchi Marco Bagiacchi - 1 month ago 13
SQL Question

Form field empty in PHP search

I have a html form with textbox and combobox.



  • If I write something on textbox, the search is filtered.

  • If I write something on textbox and select an item on combobox, the
    search is filtered.

  • If I leave empty the textbox and combobox, the search takes me all results.



But,


  • If I leave empty the textbox and select an item on combobox, the search takes me all results (and not just selected item of combobox)



I would like it, if all element are empty, the search return all the results. But if the tetxbox is empty and the others are selected, bring me back not all results.

index.html (only
<form>
)

<form action="index.php" method="post">
<input type="text" name="input" placeholder="Ricerca Ordini"> <br><br>
<label for="selectmenu">Tipo:</label>
<select id="selectmenu" name="tipo">
<option>C1</option>
<option>CR</option>
<option>F1</option>
<option>FP</option>
<option>FPE</option>
<option selected="selected">All Type</option>
</select>
<label for="spinner">ID:</label>
<input id="spinner" name="id"> <br><br>
<label for="from">From</label>
<input type="text" id="from" name="from">
<label for="to">to</label>
<input type="text" id="to" name="to"> <br><br>
<input type="submit" name="search">
</form>


index.php (only important part)

<?php
define ('DBNAME',"./DinamicoWeb.mdb"); // Definisce il nome del database
define ('DBTBL',"Ordini"); // Definisce il nome della tabella
define ('PKNAME',"Id Ord"); // Definisce il nome della chiave primaria
define ('PKCOL',0); // Definisce la posizione della chiave primaria
define ('LINKPK',true); // Abilita i link alla PK per modifica-cancella

$con = new COM("ADODB.Connection");
$conStr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=".
realpath(DBNAME).";";
$con->open($conStr);

$input=$_POST['input'];
$id=$_POST['id'];
$tipo=$_POST['tipo'];
$numero=$_POST['numero'];


$sql="SELECT [Id Ord] AS [ID], [Tipo Ord] AS [Tipo], [N Ord] AS [Numero], [Data Ord] AS [Data], [Ragione Sociale], [Indirizzo], [TotImp] AS [IMPORTO TOTALE], [TotIva] AS [IMPORTO IVA] FROM [Ordini] WHERE [Indirizzo] LIKE '%$input%' OR [Ragione Sociale] LIKE '%$input%' OR [Id Ord] LIKE '$id' OR [Tipo Ord] LIKE '$tipo' OR [N Ord] LIKE '$numero'";

$rs = $con->execute($sql);


if($rs === false) {
trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $con->ErrorMsg(), E_USER_ERROR);
} else {
$rows_returned = $rs->RecordCount();
}

$numFields = $rs->Fields->count;

// Print Table...


Snippet of Index.html



<!doctype html>
<html lang="it">
<head>
<meta charset="utf-8">
<title>Ricerca Ordini</title>
<link href="jquery-ui.css" rel="stylesheet">
<link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
<script>
$( function() {
var dateFormat = "mm/dd/yy",
from = $( "#from" )
.datepicker({
defaultDate: "+1w",
changeMonth: true,
changeYear: true,
numberOfMonths: 1
})
.on( "change", function() {
to.datepicker( "option", "minDate", getDate( this ) );
}),
to = $( "#to" ).datepicker({
defaultDate: "+1w",
changeMonth: true,
changeYear: true,
numberOfMonths: 1
})
.on( "change", function() {
from.datepicker( "option", "maxDate", getDate( this ) );
});

function getDate( element ) {
var date;
try {
date = $.datepicker.parseDate( dateFormat, element.value );
} catch( error ) {
date = null;
}

return date;
}
} );
</script>
<style>
body{
font-family: "Trebuchet MS", sans-serif;
margin: 50px;
}
.demoHeaders {
margin-top: 2em;
}
#dialog-link {
padding: .4em 1em .4em 20px;
text-decoration: none;
position: relative;
}
#dialog-link span.ui-icon {
margin: 0 5px 0 0;
position: absolute;
left: .2em;
top: 50%;
margin-top: -8px;
}
#icons {
margin: 0;
padding: 0;
}
#icons li {
margin: 2px;
position: relative;
padding: 4px 0;
cursor: pointer;
float: left;
list-style: none;
}
#icons span.ui-icon {
float: left;
margin: 0 4px;
}
.fakewindowcontain .ui-widget-overlay {
position: absolute;
}
select {
width: 200px;
}
</style>

</head>

<body>
<h1>Ricerca Ordini</h1>
<form action="index.php" method="post">
<input type="text" name="input" placeholder="Ricerca Ordini"> <br><br>
<label for="selectmenu">Tipo:</label>
<select id="selectmenu" name="tipo">
<option>C1</option>
<option>CR</option>
<option>F1</option>
<option>FP</option>
<option>FPE</option>
<option selected="selected">All Type</option>
</select>
<label for="spinner">ID:</label>
<input id="spinner" name="id"> <br><br>
<label for="from">From</label>
<input type="text" id="from" name="from">
<label for="to">to</label>
<input type="text" id="to" name="to"> <br><br>
<input type="submit" name="search">
</form>

<script src="external/jquery/jquery.js"></script>
<script src="jquery-ui.js"></script>
<script>

$( "#accordion" ).accordion();



var availableTags = [
"ActionScript",
"AppleScript",
"Asp",
"BASIC",
"C",
"C++",
"Clojure",
"COBOL",
"ColdFusion",
"Erlang",
"Fortran",
"Groovy",
"Haskell",
"Java",
"JavaScript",
"Lisp",
"Perl",
"PHP",
"Python",
"Ruby",
"Scala",
"Scheme"
];
$( "#autocomplete" ).autocomplete({
source: availableTags
});



$( "#button" ).button();
$( "#button-icon" ).button({
icon: "ui-icon-gear",
showLabel: false
});



$( "#radioset" ).buttonset();



$( "#controlgroup" ).controlgroup();



$( "#tabs" ).tabs();



$( "#dialog" ).dialog({
autoOpen: false,
width: 400,
buttons: [
{
text: "Ok",
click: function() {
$( this ).dialog( "close" );
}
},
{
text: "Cancel",
click: function() {
$( this ).dialog( "close" );
}
}
]
});

// Link to open the dialog
$( "#dialog-link" ).click(function( event ) {
$( "#dialog" ).dialog( "open" );
event.preventDefault();
});



$( "#datepicker" ).datepicker({
inline: true
});



$( "#slider" ).slider({
range: true,
values: [ 17, 67 ]
});



$( "#progressbar" ).progressbar({
value: 20
});



$( "#spinner" ).spinner();



$( "#menu" ).menu();



$( "#tooltip" ).tooltip();



$( "#selectmenu" ).selectmenu();


// Hover states on the static widgets
$( "#dialog-link, #icons li" ).hover(
function() {
$( this ).addClass( "ui-state-hover" );
},
function() {
$( this ).removeClass( "ui-state-hover" );
}
);
</script>
</body>
</html>




Answer

When $input is an empty string

WHERE [Indirizzo] LIKE '%$input%' OR [Ragione Sociale] LIKE '%$input%'

means "I want select all the rows". So, first check if it's empty, and if it's not keep that from the criteria, otherwise remove them.

if (empty($input)) {
    // query without [Indirizzo] LIKE '%$input%' OR [Ragione Sociale] LIKE '%$input%'
} else {
    // query with [Indirizzo] LIKE '%$input%' OR [Ragione Sociale] LIKE '%$input%'
}