Problem description: Finding online status of therapist users based on last interaction in Session
table. Cannot keep last interaction date on User
table but it should be separated on Session
table. But how can I join one row for each user with User
table information and session data of Session
table for user’s last interaction for each record?
Description of figure 1: Listing of therapists from User table with showing their online status based on their last interaction from Session table
Description of figure2 (User): User table is the first table and email is its primary key. It need to be joined with second column, that is Session table’s StartDate field as one row for each record. (Pink backgrounded StartDate
column is not on User
table. User
table columns plus the pink backgrounded StartDate
column is desired result)
The hardest part of the problem is email
is basis of join field and only one in User
table but has many in the Session
table because it is not primary there.
Description of figure3 (Session): Email field duplicates as user interacts in the program. They are kind of logs of key presses changing personal information e.g.
I cannot created a qualified query to solve my problem so I created a query for table1 and second query in the loop that is not right way of doing the job. It caused performance issues. I need to get help joining two different queries at once.
The first query filters results based on primary key email
from User table. The second query gets latest session date on from Session table within the loop.
$marker_list
is the first query and $sql_online
is for the second query which is nested.
<?php $latitude = filter_input(INPUT_GET, "latitude", FILTER_SANITIZE_SPECIAL_CHARS); $longitude = filter_input(INPUT_GET, "longitude", FILTER_SANITIZE_SPECIAL_CHARS); $start = filter_input(INPUT_GET, "start", FILTER_SANITIZE_SPECIAL_CHARS); $email = filter_input(INPUT_GET, "email", FILTER_SANITIZE_EMAIL); include_once "./connect-db.php"; if ($start == 'undefined') { $start = 0; } else { $start = $start * 10; } $distance = 100; //First query: lists therapists from User table $marker_list = "SELECT `User`.`uid`, `User`.`latitude`, `User`.`longitude`, `User`.`name`, `User`.`phone_verified`, `User`.`email`, ( 3959 * acos(cos(radians(" . $latitude . ")) * cos(radians(latitude)) * cos(radians(longitude) - radians(" . $longitude . ")) + sin(radians(" . $latitude . ")) * sin(radians(latitude ))) ) AS `distance` FROM `massage`.`User` WHERE rol= 'terapist' AND list_on_maps= '1' AND email_valid='1' HAVING distance < $distance ORDER BY distance LIMIT $start, 7"; if ($results = mysqli_query($conn, $marker_list)) { $sizeofquery = mysqli_num_rows($results); $counter = 0; $maps = array(); while ($row = mysqli_fetch_row($results)) { $maps[$counter] = new stdClass(); $maps[$counter]->index = $row[0]; $maps[$counter]->latlng = new stdClass(); $maps[$counter]->latlng->latitude = floatval($row[1]); $maps[$counter]->latlng->longitude = floatval($row[2]); $maps[$counter]->title = $row[3]; $maps[$counter]->phone_verified = $row[4]; //Second query gets their user interaction from Session table $sql_online= "SELECT `StartDate` FROM `massage`.`Session` WHERE `email`='$row[5]' ORDER BY `Session`.`StartDate` DESC LIMIT 1"; $result_nested= mysqli_query($conn, $sql_online); $row_online= mysqli_fetch_array($result_nested); $delta= (strtotime("now") - strtotime($row_online['StartDate'])); if ($delta < 300 ) { $maps[$counter]->online = "y"; } else if ( $delta < 600){ $maps[$counter]->online = "t"; } else { $maps[$counter]->online = "g"; } $counter++; } $maps->counter = $row['counter']; $mapsJSON = json_encode($maps); } //Third Query: Inserts last interaction as listing therapists as on many pages. It is not an important part of the question. $sql_session = "INSERT INTO `massage`.`Session` (`StartDate`, `email`, `Interaction`) VALUES (current_timestamp(), '$email', 'dolistmaps');"; mysqli_query($conn, $sql_session); header('Content-type:application/json;charset=utf-8'); echo $mapsJSON; mysqli_free_result($results); mysqli_close($conn);
So please help me creating a single query which accomplishes joining two different tables on the common field email
on both tables.
Advertisement
Answer
You can use MAX
to find the latest StartDate
from Session
for each email in a derived table and JOIN
that to the user table to get the results you need:
SELECT `User`.`uid`, `User`.`latitude`, `User`.`longitude`, `User`.`name`, `User`.`phone_verified`, `User`.`email`, ( 3959 * acos(cos(radians(" . $latitude . ")) * cos(radians(latitude)) * cos(radians(longitude) - radians(" . $longitude . ")) + sin(radians(" . $latitude . ")) * sin(radians(latitude ))) ) AS `distance`, s.StartDate FROM `massage`.`User` JOIN ( SELECT email, MAX(StartDate) AS StartDate FROM `massage.Session` GROUP BY email ) s ON s.email = `User`.email WHERE rol= 'terapist' AND list_on_maps= '1' AND email_valid='1' HAVING distance < $distance ORDER BY distance LIMIT $start, 7