Echo the latest row from the table that belongs to a user

Question

I'm attempting to introduce a system in which the user's latest security information is printed from database.

Context: MYSQL and PHP

The database looks a little like this...

id  username   dateTime            ipAddress  country   region      city
1   salieri    05/09/2021 10:41     1.1.1.1     GB      England     London
2   salieri    06/09/2021 10:23     1.1.1.1     GB      England     Hackney

How would I make it so that the highest ID (or latest date time) for a particular user is echo'd?

I'm currently using this code but it will only pull the first value for that user from the database-- doing quite the opposite of what I would like.

//security data
$securityQuery = "SELECT * FROM logInAttempts WHERE username='{$_SESSION['username']}' ";

$securityResults = mysqli_query($connection, $securityQuery);
if(mysqli_num_fields($securityResults) > 1) {
   $securityRes =  mysqli_fetch_array($securityResults);
}

A couple of things, first when selecting highest or latest use "ORDER BY ID DESC" or "ORDER BY ID ASC" etc...<- for example in your sql to get the highest or lowest by order... And as for the values - mysqli_fetch_array <- this returns only one row... what if you have more than one? if you are only looking for one row than the ORDER BY ID DESC or ASC should do the trick and return only the last of first one... — Shlomtzion, Sep 05 '21 at 10:34
as @brombeer wisely said... why not use this instead: while ($row = mysql_fetch_assoc($result)) { $users[$row['id']] = $row["username"]; $rows[$row['id']] = $row; } <- illustrating how you can work with the received values... — Shlomtzion, Sep 05 '21 at 10:39
@Shlomtzion Sorry for removing my comment, figured the query wasn't the issue but not using a `while` loop. ;) — brombeer, Sep 05 '21 at 10:43
**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, Sep 05 '21 at 11:16

score 0 · Answer 1 · answered Sep 05 '21 at 10:37

You canuuse ORDER BY AND LIMIT to 1

 $securityQuery = "SELECT * FROM logInAttempts WHERE username=
? ORDER BY dateTime DESC LIMIT 1";

Still use prepared statements with parameters in all your queries you use to add variables of any kind to a query see How can I prevent SQL injection in PHP?

Echo the latest row from the table that belongs to a user

1 Answers1