aristeidhsP aristeidhsP - 6 months ago 23
SQL Question

Combine two separate input fields in search with php

I have two separate input fields that the one searches the surname and the other the name to search the db

<li><label>Surname: </label><input type="text" name="surname" id="search_text" required autocomplete="off" /></li>
<li><label>Name: </label><input type="text" name="name" id="name" required autocomplete="off"/></li>



<script> // first input live search
$(document).ready(function() {

$("#search_text").keyup(function () {
var txt = $(this).val();

setTimeout(function() {
if(txt.length>=3)
{
$.ajax({
url:"fetchsurname.php",
method:"post",
data:{search:txt},
dataType:"text",

success:function(data)
{
$('#searchresults').html(data);
}
});
}
else
{
$('#searchresults').html('');
}
}, 2000); // 1 sec delay to check.

}); // End of keyup function

}); // End of document.ready

</script>
<script> // second input live search
$(document).ready(function() {

$("#name").keyup(function () {
var txt = $(this).val();

setTimeout(function() {
if(txt.length>=3)
{
$.ajax({
url:"fetchname.php",
method:"post",
data:{search:txt},
dataType:"text",

success:function(data)
{
$('#searchresults').html(data);
}
});
}
else
{
$('#searchresults').html('');
}
}, 2000); // 1 sec delay to check.

}); // End of keyup function

}); // End of document.ready
</script>


That searches the db with two separate files of php fetchname and fetchsuurname


"SELECT * FROM base WHERE name LIKE '".$_POST["search"]."%'"

"SELECT * FROM base WHERE surname LIKE '".$_POST["search"]."%'"


My problem is that every field is searching its own value in the tables and not combined with the first field to show the results .

Thank you

Answer

according to your html, you should change jQuery and to following code:

<li><label>Surname: </label><input type="text" name="surname" id="search_text" required autocomplete="off" /></li>

<li><label>Name:  </label><input type="text" name="name" id="search_name" required autocomplete="off"/></li>

<script> // first input live search 
var keyupfunction = function () {
        var search_text = $("#search_text").val();  
        var search_name = $("#search_name").val();  

        setTimeout(function() { 
            if(txt.length>=3)  
            {  
                $.ajax({  
                    url:"fetchsurname.php",  
                    method:"post",  
                    data:{"surname":search_text, "name":search_name},  
                    dataType:"text",

                    success:function(data)  
                    {  
                        $('#searchresults').html(data);  
                    }  
                });  
            }  
            else  
            {  
                $('#searchresults').html('');                 
            }  
        }, 2000); // 1 sec delay to check.

    }

$(document).ready(function() {
    $("#search_text").keyup(keyupfunction); 
    $("#search_name").keyup(keyupfunction); 
});

you can use from or in your queries:

SELECT * FROM base WHERE name LIKE '".$_POST["name"]."%' AND surname LIKE '".$_POST["surname"]."%'

also you can use from Or if you do not want both of fields have value

also there are another solution which you can union 2 select for 2 different table

SELECT * FROM base WHERE name LIKE '".$_POST["name"]."%'
union all
SELECT * FROM base WHERE surname LIKE '".$_POST["surname"]."%'