MySQL and PHP select different row

Question

I have this table

Configuration

ID   Location   Position   CheckData    UpdateDT
1    House1      Floor1     20        2020-06-24 18:12:20
2    House1      Floor1     30        2020-06-24 13:54:16
3    House1      Floor2     45        2020-06-24 10:06:34  
4    House2      Roof1      70        2020-05-12 13:27:43
5    House1      Floor1     35        2020-05-12 12:20:12

I like to make a select to get from the Configuration table only the latest updated values for each "home" and "position" e.g.

1    House1    Floor1     20        2020-06-24 18:12:20
3    House1    Floor2     45        2020-06-24 10:06:34
4    House2    Roof1      70        2020-05-12 13:27:43

i have try this, but its not working, because i dont know how to add the position on it

select * from Configuration where Location = 'house 1' order by Configuration.UpdateDT desc limit 1

and here is the php code that i have so far

$sqlsyntax = "SELECT * FROM `DevicesLocations`";
$resultfromsql = mysqli_query($connectionstring, $sqlsyntax);
while( $row = mysqli_fetch_array($resultfromsql) )
{
   echo $row['Location'] . " " . $row['Comments'];
   echo "<br />";
   $sql2 = "SELECT * from Configuration where location='";
   $sql2 = $sql2.$row['Location']."' order by UpdateDT desc limit 1";
   echo $sql2."<br>";
   $result2 = mysqli_query($connectionstring, $sql2);
   while( $row2 = mysqli_fetch_array($result2) )
   {
      echo "..........".$row2['SensorPosition'] . " " . $row2['CheckData'] ;
      echo "<br />";
   }
   echo "------------------------------<br>";
}

Output

House1    first home
...... floor1 20
House2     second home
...... Roof1 70

Desired Output

House1    first home
...... floor1 20
...... floor2 45
House2     second home
...... Roof1 70

If i use the following select, how i can include the latest date?

SELECT * FROM `Configuration` group BY Configuration.SensorPosition

you can use `WHERE` like `select * from Configuration WHERE Location = "House1" order by Configuration.UpdateDT desc limit 1` — Marty1452, Jun 24 '20 at 15:47
i add the code that i have so far. I want a list so the user can select the location he want to see the data according to the latest update he have. And for the second house? — G Kal, Jun 24 '20 at 15:52
Thank you a lot for that!!! The good think is that the user cant type any data! Data stored automatically from the device when it see moves. — G Kal, Jun 24 '20 at 15:59
It doesn't change the fact that it is still vulnerable. SQL injection is a bug in your code and it does not matter where the data comes from. — Dharman, Jun 24 '20 at 16:00
I can tell you that i am alot worried now! i will check the links you have provide me already and i will make the corrections asap.. do you have the kind to tell me what i have to change on the above code please? Thanks again! — G Kal, Jun 24 '20 at 16:03
`$sql2.$row['Location']` get the value from the first select `$row = mysqli_fetch_array($resultfromsql)` am i wrong? i have also include an output — G Kal, Jun 24 '20 at 16:08
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/216576/discussion-between-g-kal-and-dharman). — G Kal, Jun 24 '20 at 16:31
https://stackoverflow.com/questions/3619030/mysql-join-the-most-recent-row-only https://stackoverflow.com/questions/27980305/mysql-select-latest-record-only-on-left-join-table https://stackoverflow.com/questions/44291451/mysql-join-on-the-latest-row — Dharman, Jun 24 '20 at 16:31

score 0 · Answer 1 · answered Jun 24 '20 at 18:05

I think i have found the answer. so, please correct me if i am wrong

SELECT m.* from Configuration m 
   left join Configuration b on 
     m.SensorPosition = b.SensorPosition and 
     m.UpdateDT < b.UpdateDT where b.UpdateDT is null

I follow this link to get the answer

MySQL and PHP select different row

1 Answers1