Getting data from database through Ajax post request Is the method used below to get data from a database efficient and optimal? The data is in a MySQL database, and in my server I have a PHP file with the following code to return some information:
if($_POST[&quot;method&quot;] == &quot;requestBusinessFood&quot;) {
    requestBusinessFood();
} 

function requestBusinessFood() {
    $categoryID = $_POST[&quot;category&quot;];

    $host = 'localhost';
    $user = 'root';
    $pass = '';
    $db = 'fooddatabase';

    $conn = mysqli_connect($host, $user, $pass, $db); 
        $sql = &quot;SELECT * FROM `foodtablebusiness` WHERE category = &quot; . $categoryID;

        $result = mysqli_query($conn, $sql);
        $rows = array();
        while($r = mysqli_fetch_assoc($result)) {
         $rows[] = $r;
        }

        echo json_encode($rows);
}

On the webpage, I have a js file to retrieve the information in the following way:
  function createAJAXRequestToPopulateList(category) {
    return $.ajax({ url: '../server.php',
                    data: {method: 'requestBusinessFood',
                           category: category},
                    type: 'post'
    });
  }

function addActivityItem(){
    var selector = document.getElementById(&quot;categorySelector&quot;);
    ajaxRequest = createAJAXRequestToPopulateList(selector.options[selector.selectedIndex].value);
    ajaxRequest.done(populateList);
}

function populateList(responseData) {
    console.log(responseData);
}
PHP

SQL injection vulnerabilities

This code is wide-open to SQL injection attacks. User input should be sanitized as supplied to the query using parameters (e.g. with mysqli_prepare() and bind_param()).

Indentation

The indentation is somewhat consistent but then in some places it increases without a block-level change - e.g. in the PHP code most lines are indented with four spaces, which is very common, and then when $sql is declared it increases to eight spaces.


$conn = mysqli_connect($host, $user, $pass, $db); 
      $sql = "SELECT * FROM `foodtablebusiness` WHERE category = " . $categoryID; 



Fetching results

Instead of using a while loop with mysqli_fetch_assoc() just to push into an array, use mysqli_fetch_all() to get an array with one call.

Selecting fields

Additionally, the SQL query selects all fields - i.e. with SELECT *. Instead of selecting *, specify the field names needed in order to exclude any fields not needed in the front end.

Javascript

Variable scope

In this line:


ajaxRequest = createAJAXRequestToPopulateList(selector.options[selector.selectedIndex].value);



it makes a global variable ajaxRequest because there is no var, let or const keyword before it. Use const to limit the scope to the function addActivityItem.

Utilizing jQuery

It appears that jQuery is used, given the usage of $.ajax() (though if that is wrong then the following won't work). Presuming that is the case, then the other code can be simplified using jQuery utilities.

For example:


var selector = document.getElementById("categorySelector");
ajaxRequest = createAJAXRequestToPopulateList(selector.options[selector.selectedIndex].value);



can be simplified to the following using the .val() method:

const selector = $('#categorySelector');
const ajaxRequest = createAJAXRequestToPopulateList(selector.val());