Merge two SQL queries together (syntax issue)

Question

I'm using PHP to send this query where lastConn is a datetime object.

$result = $mysqli->query("SELECT id, username, lastConn FROM users LIMIT $startIndex, 50") or die($mysqli->error);

However my goal is not to get the raw lastConn data, but the time difference between lastConn and CURRENT_TIMESTAMP. I wrote this query which does the job :

SELECT TIMESTAMPDIFF(MINUTE,lastConn,CURRENT_TIMESTAMP) AS 'duration' FROM users;

I'm now trying to merge these two queries together to get the time difference in the first query but I can't seem to find the correct syntax.

Here is how I'm retrieving the data after performing the query using PHP:

while($row = $result->fetch_assoc()){
    echo $row['id'];
    echo $row['username'];
    echo $row['lastConn']; //should echo the time difference instead of the raw lastConn value
}

How can I directly get the time difference between lastConn and CURRENT_TIMESTAMP in my first query, without having to use a second one ?

and you did try: $mysqli->query("SELECT id, username, lastConn, TIMESTAMPDIFF(MINUTE,lastConn,CURRENT_TIMESTAMP) AS 'duration' FROM users LIMIT $startIndex, 50") — matias elgart, Sep 21 '17 at 01:43
*facepalm* I didn't think I could do it this way although it seems perfectly obvious now that I see it... I need to brush up on my SQL. Can you post your answer so I can accept it ? — Hal_9100, Sep 21 '17 at 01:52

score 1 · Accepted Answer · answered Sep 21 '17 at 01:55

you could probably just add the portion of your second query to the first, like this below. you had it all working, just needed that last step!

$mysqli->query("SELECT id, username, lastConn, TIMESTAMPDIFF(MINUTE,lastConn,CURRENT_TIMESTAMP) AS 'duration' FROM users LIMIT $startIndex, 50")

hope this helps.

Merge two SQL queries together (syntax issue)

1 Answers1