# 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["method"] == "requestBusinessFood") { requestBusinessFood(); } function requestBusinessFood() {$categoryID = $_POST["category"];$host = 'localhost';
$user = 'root';$pass = '';
$db = 'fooddatabase';$conn = mysqli_connect($host,$user, $pass,$db);
$sql = "SELECT * FROM foodtablebusiness WHERE category = " .$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',
category: category},
type: 'post'
});
}

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

function populateList(responseData) {
console.log(responseData);
}
• Use mysqli_real_escape_string. – Mantykora 7 May 28 '20 at 6:34

## 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());

Sadly, the PHP part is neither efficient nor optimal.

Apart from already mentioned issues, your function does too much work, which makes it non-reusable and which bloats your code in general. Remember the rule of thumb: each unit of code should mind its own business. While your function interacts with a client through $_POST and echo, connects to a database, queries a database, formats the results. You have split it into several parts 1. Create a separate file to connect with a database and just include it in every script that needs a database connection. 2. Create a function that performs a certain SQL query and return the results, so it can be reused for any other kind of request. 3. Write a code that handles a particular request from a client So the code should be <?php require 'mysqli.php'; if($_POST["method"] == "requestBusinessFood")
{
$rows = requestBusinessFood($mysqli, $_POST["category"]); echo json_encode($rows);
}

function requestBusinessFood($mysqli,$categoryID)
{
$sql = "SELECT * FROM foodtablebusiness WHERE category = ?";$stmt = $mysqli->prepare($sql);
$stmt->bind_param("s",$categoryID);
$stmt->execute();$result = $stmt->get_result(); return$result->fetch_all(MYSQLI_ASSOC);
}