MySQL multi-search with jQuery/AJAX

Question

So I have designed a form that allows the user to input Name, region, age and language. I've also made a PHP script that gets the data that they input, however if the user leaves a field empty or they started to write in name and deleted it the database returns every field because it matches every record in the database. Is there a way to dis-include empty fields? If someone could do name and region I should be able to figure out the rest. I have the jQuery and AJAX set up so that if a keypress is done in any of the form, it performs this searchFunction. I'm just wondering if there is a more efficient way of doing the MySQL than what I'm doing. After looking at the first answer and trying to implement it, I'm not sure on how to update the MySQL if the variable is not set unless I do 20+ if else statements.

ajax:

$(function() {
                $("#searchform").bind('submit',function() {
                    var name = $('#name').val();
                    var region =$('#region').val();
                    $.get('scripts/searchFunction.php',{name:name, region:region}, function(data){
                        $("#show").html(data);
                    });
                    return false;
                });
                $("#searchform").keyup(function() {
                    var name = $('#name').val();
                    var region =$('#region').val();
                    $.get('scripts/searchFunction.php',{name:name, region:region}, function(data){
                        $("#show").html(data);
                    });
                    return false;
                });
            });

searchFunction.php

<?php
include 'connection.php';
session_start();
$name = "%" . $_GET['name'] . "%";
$region = "%" . $_GET['region'] . "%";
$dbuname;
$dbfname;
$dblname;
$dbage;
$dbregion;
$dblanguage;

$stmt = $conn->prepare('SELECT Username, First_Name, Last_Name, Age, Region, Language FROM '. $table .' WHERE First_Name LIKE ? OR Region LIKE ?;');
$stmt->bind_param('ss',$name, $region);
$stmt->execute();
$stmt->bind_result($dbuname, $dbfname, $dblname, $dbage, $dbregion, $dblanguage);
while($stmt->fetch()){
    echo '<div class="row">';
    echo '<div class="col-md-4">';
    if(file_exists("../img/accounts/" .$dbuname . "/Profile.jpg")){
        echo '<img src="img/accounts/'. $dbuname .'/Profile.jpg" class="img-fixed"/>';
    }else if(file_exists('../img/accounts/' .$dbuname . '/Profile.png"')){
        echo '<img src="img/accounts/'. $dbuname .'/Profile.png"/>';
    }else{
        echo '<img src="img/default-img/default.png" class="img-fixed"/>';    
    }

    echo '</div>';    

    echo '<div class="col-md-7 col-sm-3 col-sm-offset-1">';
    echo '<p>First Name: ' . $dbfname . '</p>';
    echo '<p>Last Name: ' . $dblname . '</p>';
    if(isset($_SESSION['Loggedin'])){
        if($_SESSION['Loggedin'] == 1){
            echo '<p>Age: ' . $dbage . '</p>';
            echo '<p>Region: ' . $dbregion . '</p>'; 
            echo '<p>Language: ' . $dblanguage . '</p>';
        }
    } 
    echo '</div>';    
    echo '</div>';    
    echo '<hr class="section-spacer"/>';  
    echo '<br>';
}
?>

Darshan Mehta · Accepted Answer · 2017-01-26 21:41:49Z

1

You can add a validation before ajax call and check if user has entered anything (other than a space), e.g.:

var str = $('#field').val();
if(val && val.trim().length > 0){
  //method call
}

This way, function won't be called if input is null or contains whitespaces only.

Update

You need to modify the php code to create and run the query dynamically based on passed parameters, e.g:

$stmt;
if (!empty($name) and !empty($region)) {
    $stmt = $conn->prepare('SELECT Username, First_Name, Last_Name, Age, Region, Language FROM '. $table .' WHERE First_Name LIKE ? OR Region LIKE ?;');
    $stmt->bind_param('ss',$name, $region);
}else if (!empty($name)) {
    $stmt = $conn->prepare('SELECT Username, First_Name, Last_Name, Age, Region, Language FROM '. $table .' WHERE First_Name LIKE ?;');
    $stmt->bind_param('ss',$name);
}else{
    $stmt = $conn->prepare('SELECT Username, First_Name, Last_Name, Age, Region, Language FROM '. $table .' WHERE Region LIKE ?;');
    $stmt->bind_param('ss',$region);
}

edited Jan 26, 2017 at 21:41

answered Jan 26, 2017 at 19:20

Darshan Mehta

31k14 gold badges75 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Chromatic Over a year ago

I've added the Ajax from my code, would it be able to work in the example above? Because I have two functions, one for submit and one for keyup

Darshan Mehta Over a year ago

Yes, you need to wrap the get function call inside if condition like the one I showed in the example.

Chromatic Over a year ago

Would it be more efficient to use $.get or switch it out and use an array with $.ajax and use data:data?

Darshan Mehta Over a year ago

Yes, it'll be good to extract out the code and move it to a separate function.

Chromatic Over a year ago

Okay, I got the jist now. Thank you for the answer :)

|

Collectives™ on Stack Overflow

MySQL multi-search with jQuery/AJAX

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related