DB result ordered by nearest matches (3 numeric criterias)

Question

I'm trying to make a system where I can input multiple values, and get all matches with a 15% tolerance from a mysql table. I've made in HTML and CSS the layout and I've managed to achieve it, but the problem is, I need to get the best match on top!

My table has 4 columns Modelo, L, A, H (Model, Length, width, height) Here's my code: HTML

        <div class="battrac">
            <label for="largo">L</label>
            <input id="largo" name="largo" onkeyup="buscar()" required></input>
            <label for="ancho">A</label>
            <input id="ancho" name="ancho" onkeyup="buscar()" required></input>
            <label for="alto">H</label>
            <input id="alto" name="alto" onkeyup="buscar()" required></input>
        </div>

JS

function buscar() {
 // if (str=="") {
 //   document.getElementById("result-trac").innerHTML="";
 // }
var l = document.getElementById("largo").value;
var a = document.getElementById("ancho").value;
var h = document.getElementById("alto").value;


 var xmlhttp=new XMLHttpRequest();
  xmlhttp.onreadystatechange=function() {
    if (this.readyState==4 && this.status==200) {
      document.getElementById("result-trac").innerHTML=this.responseText;
    }
  }
  xmlhttp.open("GET","busc.php?t="+tipo+"&l="+l+"&a="+a+"&h="+h,true);
  xmlhttp.send();
}

busc.php

<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
$link = mysqli_connect("localhost", "root", "", "test");
if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}
$l = $_GET['l'];
$a = $_GET['a'];
$h = $_GET['h'];

if($l=="" ||$a=="" ||$h==""){
echo("Por favor compelte todos los campos");
exit;
}

$sql = "SELECT * FROM test WHERE L>=($l * .85) AND L<=($l * 1.15) AND A>=($a * .85) AND A<=($a * 1.15) AND H>=($h * .85) AND H<=($h * 1.15)";
$result = $link->query($sql);

if ($result->num_rows > 0) {
echo('<table style="width:100%">
              <tr>
                <th>Modelo</th>
                <th>L</th>
                <th>A</th>
                <th>H</th>
              </tr>');

  while($row = $result->fetch_assoc()) {
    echo("
<tr>
<td>".$row['Producto']."</td>
<td>".$row['L']."</td>
<td>".$row['A']."</td>
<td>".$row['H']."</td>
</tr>
        ");
}
echo('</table>');
}else{echo($link->connect_error);}

?>

In this picture, the nearest match is BAT 2, but BAT 1 is showing up first.

Some IDs and Classes are in Spanish, but if you need any other information, let me know! EDIT: What I need to do, is to get the nearest match on top. See in the picture BAT 2 has more near numbers than BAT 1 dora's, but BAT 1 is on top. I want BAT 2 to appear on top Here's a fiddle with 4 rows http://sqlfiddle.com/#!9/6e705f So if I need, for example, a product that has L=95, A=95 and H=95, I would like to have BAT 3 show up first, and then BAT 2 and BAT 1. But not BAT 4 because is not in the 15% tolerance in all values

Please see [Why should I provide an MCRE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Dec 18 '20 at 17:26
@FelippeDuarte, What I mean is, that I have rows that match best the numbers I input! In the image you can see what I mean. — Federico Scalise Giussani, Dec 18 '20 at 17:28
@Strawberry, this would be the data I have in my table: http://sqlfiddle.com/#!9/6e705f So if I need, for example, a product that has L=95, A=95 and H=95, I would like to have BAT 3 show up first, and then BAT 2 and BAT 1. But not BAT 4 because is not in the 15% tolerance in all values. — Federico Scalise Giussani, Dec 18 '20 at 17:36
@FelippeDuarte, that would be the row that has the nearest values to the input! — Federico Scalise Giussani, Dec 18 '20 at 17:37
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Dec 18 '20 at 17:43
@Dharman! I'm the only one going to use this, so it's not quite important to secure the script. But thanks for the recommendation!! — Federico Scalise Giussani, Dec 18 '20 at 17:57
It doesn't matter. Even you can accidently break the site because of this. Please do not neglect this. There is absolutely no reason why you should not be using prepared statements. Please learn to do it right from the start and it will save you from a lot of problems later on. — Dharman, Dec 18 '20 at 18:00
@Dharman, I will take your advice! When I solve this problem I'll convert everything to prepared statements!! — Federico Scalise Giussani, Dec 18 '20 at 18:06
Please edit your question accordingly - this info is too valuable to be buried in comments — Strawberry, Dec 18 '20 at 18:08
Your query looks good, but I don't understand the rules for prioritizing the results. — Strawberry, Dec 18 '20 at 18:20
This query will give me the results in the order that are saved. I don't want that! I need it to get the result with the nearest values to the input on top, and then the rest. Thanks — Federico Scalise Giussani, Dec 18 '20 at 18:31
So if you query for 90-90-90 and have 85-85-85, 95-95-95 and 85-100-90 results, which is nearest? — Felippe Duarte, Dec 18 '20 at 21:00
@FelipeDuarte, in that case, they would all work! The order wouldn't matter there! — Federico Scalise Giussani, Dec 18 '20 at 21:56
I removed the `JavaScript` and `jQuery` tags for the question. — Louys Patrice Bessette, Dec 18 '20 at 22:16

Louys Patrice Bessette · Accepted Answer · 2020-12-18T22:23:28.087

Let's first fix the SQL-injection issue you have. In busc.php you already use mysqli_connect, which is good.

The problem is about passing the GET values directly in the SQL query. So you need a "prepared statement". Here is a detailled tutorial.

But before that... And beyond the SQL-injection issue, you certainly will want to ensure the values are numeric to properly query the DB. So I suggest you apply this logic to the 3 GET variables:

if (isset($_GET['l']) && is_numeric($_GET['l'])) {
  $l= (int) $_GET['l'];
} else {
  $l= 100;  // Default value if unset or non-numeric
}

Now try this mysqli prepared statement which will result in a $rows associative array:
And notice:

1. The usage of BETWEEN here which is a nice alternative to the comparisions you used (make things clearer).
1. The ? placeholders to bind values via the parametric engine of prepared statements.

$query = 'SELECT * FROM test WHERE 
         L BETWEEN (? * .85) AND (? * 1.15) AND 
         A BETWEEN (? * .85) AND (? * 1.15) AND 
         H BETWEEN (? * .85) AND (? * 1.15)';

$stmt = $link->prepare($query);
$stmt->bind_param("iiiiii", $l, $l, $a, $a, $h, $h);  // iiiiii means 6 integers
$stmt->execute();

$rows = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();

Here is a SQLFiddle about that BETWEEN usage.

So now, the DB results is fetched and the connection is closed. You have a $rows associative array which is not sorted as you want yet... There is some additional work to be done on that array and that was your main question.

Your main question:

To each "row" of that $rows... Add an additionnal array element to store sum of the absolute the difference of each values. That will be used to order the rows in table.

for($i=0;$i<sizeOf($rows);$i++){
  $L = abs($rows[$i]['L']-$l);
  $A = abs($rows[$i]['A']-$a);
  $H = abs($rows[$i]['H']-$h);
  $diff = $L + $A + $H;
  $rows[$i]["diff"] = $diff;
}

For a $row[$i] like ["L"=>90, "H"=>100, "A"=>95], and the values sent being 100 for all 3 $l, $a and $h variables, the $diff would be 15.

So now you will sort $rows based on the $diff values... Thanks to that SO anwser about how to filter an array based on a sub-array value.

// Sorting sub array SO answer: https://stackoverflow.com/a/2477524/2159528
usort($rows, function ($a, $b) {
    return $a['diff'] <=> $b['diff'];
});

And finally... Echo the table:

echo('
  <table style="width:100%">
    <tr>
    <th>Modelo</th>
    <th>L</th>
    <th>A</th>
    <th>H</th>
    <th>diff</th>  <!-- Added for debugging... Don't show it! -->
  </tr>
');

for($i=0;$i<sizeOf($rows);$i++){  // Loop the "ordered" rows
  echo('
    <tr>
      <td>'.$rows[$i]['Producto'].'</td>
      <td>'.$rows[$i]['L'].'</td>
      <td>'.$rows[$i]['A'].'</td>
      <td>'.$rows[$i]['H'].'</td>
      <td>'.$rows[$i]['diff'].'</td>  <!-- Added for debugging... Don't show it! -->
    </tr>
  ');
}

echo '</table>';

I made a PhpFiddle (hit F9 or the RUN button) showing the table would be ordered based on $diff using that sorting (with a fake associative array).

Thanks a lot! I'm not at home rn, but I'll try it as soon as possible and update you! — Federico Scalise Giussani, Dec 18 '20 at 21:53

DB result ordered by nearest matches (3 numeric criterias)

1 Answers1

Your main question: