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... — 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; } — 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