PHP Versions Question posted in
You can find a list of all versions here.

mysql: Join another table and get the latest row – PHP Versions

I have 2 tables: urls and checks.

urls

id | url
-----------------------
1  | google.com
2  | amazon.com
3  | stackoverflow.com

checks

time is a timestamp column, status is text

id | url_id | status | time
------------------------------------------
1  | 1      | true   | 2021-01-11 19:20:00
2  | 2      | false  | 2021-01-12 19:20:00
3  | 2      | true   | 2021-01-13 19:20:00
4  | 1      | false  | 2021-01-14 19:20:00

How to create a query to get all urls from the table ‘urls’ and the latest entry from ‘checks’? Tables are connected by urls.id = checks.url_id. And if there is no entry in ‘checks’ for the url return ‘NULL’.

Output example:

id | url               | status | time
-----------------------------------------------------
1  | google.com        | false  | 2021-01-14 19:20:00
2  | amazon.com        | true   | 2021-01-13 19:20:00
3  | stackoverflow.com | NULL   | NULL

Now I’m using this way in php pdo (could be some typo mistakes not a real example):

$r = $db->prepare('SELECT * FROM urls');
$r->execute(array());
while ($row = $r->fetch()) {
  $r2 = $db->prepare('SELECT * FROM checks WHERE url_id = ? ORDER BY time DESC LIMIT 1');
  $r2->execute(array($row['id']));
  ....
}

Is it better and faster to get the result in 1 query instead of a cycle?

Server version:

Server: MySQL (Localhost via UNIX socket)
Server type: MariaDB
Server version: 10.1.44-MariaDB-0+deb9u1 - Debian 9.11
Protocol version: 10
Database client version: libmysql - mysqlnd 5.0.12-dev - 20150407 - $Id: b5c5906d452ec590732a93b051f3827e02749b83 $

Thanks.

Tags:

2

Answers


  1. 0 
    SELECT urls.id, urls.url, checks.status, checks.time
FROM urls
LEFT JOIN ( SELECT url_id, MAX(time) time
            FROM checks
            GROUP BY 1 ) last_times ON urls.id = last_times.url_id
LEFT JOIN checks USING (url_id, time)

    fiddle

    Login or Signup to reply.
  2. 0

    You can try with a SQL in one line, I suggest you this query:

    SELECT U.*, CK.status, CK.time FROM urls U LEFT JOIN checks CK ON CK.ID = (SELECT CK2.id FROM checks CK2 WHERE CK2.url_id=U.id ORDER BY CK2.time DESC LIMIT 1);

    In this case you have the actual urls table and join the most recent row from checks.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search