PHP – most efficient way to organise MySQL queries to create a conditional form

So I am creating a conditional form, where the options populate depending on prior selections.

For example, consider a basic example:

Country = [list]

Town = [list]

Street = [list]

One would want to query their database to find all the towns that correspond to the selected country.

They would then want to query their database to find all the streets that correspond to the selected town once a town was selected.

However, I am not sure the best way to organise MySQL calls for this.

For example, I create a function that triggers through an XMLrequest such as:

function getTowns($country){

global $conn;

$sql = "SELECT distinct town from example_table where country=".$country;
$rs = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($rs);

return $row;
}

I can then populate the towns option using the returned row.

However, I am not sure if this is the most efficient, since I think I need have to have another function to select the streets once the town is selected:

function getStreets($town){

global $conn;

$sql = "SELECT distinct street from example_table where town=".$town;
$rs = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($rs);

return $row;
}

With many inputs in a form, this will result in many many functions so this leads me to question the efficiency of my original idea.

Hence I ask: is there a more efficient way to get data from MySQL for a conditional form with multiple options?

Ideally, I’m thinking is it possible to get all the towns from the database and their associated streets at once, so that once a town is selected, the options in street are automatically populated without further need to query the database.

Hope this makes sense, I am quite new to programming and stack overflow so hope this is ok.

Answer

Solution to this problem would be a subjective in nature. approach to solve this problem can differ from one programmer to other. From my end if should depend on the data size of your table.

If its a small set of data then your approach to load the combine data would be more effective. you can store it in JSON and then process it using JS functions instead of making an AJAX call every time on selection of town. This will save your API call as well as SQL query.

If the data size if a bigger then the previous approach can increase the load time of the page so its better to create multiple function at server side and make AJAX call on every selection.

If you are using second approach don’t pass the input value directly to query that will open a vulnerability of SQL injection.