Filtering MySQL database records by country using PHP backend and Vue.js frontend

Other Questions MySQL
1

Database Structure and Goal

I have a MySQL table called customers with this structure:

insert into customers (customer_id, fname, lname, email_address, nation) values (1, 'Sarah', 'Johnson', '[email protected]', 'Canada');

I need to filter records to show only specific countries like “Germany”, “Brazil”, “Australia”, etc.

Current PHP Backend Code

database.php (connection file)

<?php
$hostname = "localhost";
$username = "root";
$pass = "root";
$database = "myDatabase";

$connection = mysqli_connect($hostname, $username, $pass, $database);

if (!$connection) {
    die("Database connection error: " . mysqli_connect_error());
}
?>

api.php (data retrieval)

<?php
include "database.php";

$whereClause = "1";
if(isset($_GET['customer_id'])){
    $customerId = mysqli_real_escape_string($connection, trim($_GET['customer_id']));
    $whereClause = " customer_id=" . mysqli_real_escape_string($connection, $_GET['customer_id']);
}

$customerData = mysqli_query($connection, "select * from customers WHERE " . $whereClause);

$results = array();
while($record = mysqli_fetch_assoc($customerData)){
    $results[] = $record;
}

echo json_encode($results);
exit;
?>

Vue.js Frontend

var application = new Vue({
  el: '#application',
  data: {
    customers: "",
    customerId: 0
  },
  methods: {
    fetchAllData: function(){
      axios.get('api.php')
      .then(function (result) {
          application.customers = result.data;
      })
      .catch(function (err) {
          console.log(err);
      });
    },
    fetchById: function(){
      if(this.customerId > 0){
        axios.get('api.php', {
            params: {
                customerId: this.customerId
            }
        })
          .then(function (result) {
            application.customers = result.data;
          })
          .catch(function (err) {
              console.log(err);
          });
      }
    }
  }
});

How can I modify this setup to filter by multiple specific countries instead of fetching all records?

2

Modify your WHERE clause to handle an array of countries. In your api.php, after the customer_id check, add this:

if(isset($_GET['nation']) && !empty($_GET['nation'])){
    $nations = is_array($_GET['nation']) ? $_GET['nation'] : explode(',', $_GET['nation']);
    $placeholders = str_repeat('?,', count($nations) - 1) . '?';
    // You'll want to switch to prepared statements here for security
    $nationFilter = "nation IN ($placeholders)";
    $whereClause = ($whereClause != "1" ? $whereClause . " AND " : "") . $nationFilter;
}

For the Vue frontend, create a computed property or method that formats your selected countries into the expected format. This approach is cleaner than multiple API calls - it reduces server load and keeps things consistent.

Drop the string concatenation in your SQL though - it’s a security risk. Prepared statements are the proper way to handle this, but if you’re sticking with mysqli_query, at least escape each country value individually.

3

Quick fix - add selectedCountries: [] to your Vue data, then switch your api.php to POST instead of GET. Use something like if($_POST['countries']) { $countryList = json_decode($_POST['countries']); } and build your IN clause from there. Way cleaner than cramming everything into URL params.

4

just modify your fetchById method to accept a country array parameter. change the axios call to send countries: ['Germany', 'Brazil'] and update your php to use WHERE nation IN ('Germany', 'Brazil'). way simpler than overcomplicating it.

5

You need to add country filtering to your current parameter system. In api.php, extend your WHERE clause logic to handle a countries parameter. Pass multiple countries as a comma-separated string in the GET request, then use SQL’s IN clause for filtering.

Add this to your api.php after your existing customer_id check:

if(isset($_GET['countries']) && !empty($_GET['countries'])){
    $countries = explode(',', $_GET['countries']);
    $escapedCountries = array_map(function($country) use ($connection) {
        return "'" . mysqli_real_escape_string($connection, trim($country)) . "'";
    }, $countries);
    $whereClause .= ($whereClause != "1" ? " AND " : "") . "nation IN (" . implode(',', $escapedCountries) . ")";
}

Then add a new method in your Vue.js frontend that sends the countries parameter. I’ve used this same filtering approach in production - it scales really well when you’re filtering by multiple criteria.

6

I had the same issue on a recent project. Handling this client-side gives you way more flexibility - you won’t need to modify your PHP every time you want different filters. First, modify your api.php to accept a single country parameter: php if(isset($_GET['country']) && !empty($_GET['country'])){ $country = mysqli_real_escape_string($connection, trim($_GET['country'])); $whereClause = ($whereClause != "1" ? $whereClause . " AND " : "") . "nation = '" . $country . "'"; } Then in Vue.js, add a method that calls the API multiple times and combines the results: javascript fetchByCountries: function(countryList){ let promises = countryList.map(country => axios.get('api.php', {params: {country: country}})); Promise.all(promises).then(responses => { this.customers = responses.flatMap(response => response.data); }); } This worked better for me because you can easily add caching and handle errors per country. Performance difference is negligible unless you’re dealing with thousands of records.