CODEWITHSUNDEEP
phpjquerymysqlajax
Chromatic
Chromatic

Reputation: 87

MySQL multi-search with jQuery/AJAX

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>';
}
?>

Upvotes: 0

Views: 322

Answers (1)

Darshan Mehta
Darshan Mehta

Reputation: 30819

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);
}

Upvotes: 1

Related Questions