Search the Community

i have two relational tables with aliases 'ua' and 'se'. most data is pulled from the ua table in this query. but, if a record exists in the se table for this user (user ID is compared in both tables to find a match) we want the value in the 'status' column of the se table. if there is no matching author ID in the se table, we will provide a default value of something like 'No Match'. im not sure of a good way to go about this. the problem that arises with my query below is that if there is no record in the se table with a matching author.id, we dont get the values from the ua table either. sql query: $query = " SELECT ua.id, ua.security_key, ua.creation, ua.last_login, ua.f_name, ua.l_name, ua.email, ua.title, ua.org_name, ua.org_size, ua.manage_num, ua.manage_direct_num, ua.phone, se.status FROM user_accts AS ua, sessions AS se WHERE (" . $search_filter . " >= ? AND " . $search_filter . " <= ?) AND (se.status < ? AND se.author_id = ua.id) ORDER BY " . $search_filter . " DESC "; if( $stmt = $db_connect->prepare($query) ) { $ts_start = $obj_u_data->get_start_ts(); $ts_end = $obj_u_data->get_end_ts(); $session_done = C_SESSION_DONE; $stmt->bind_param( "iii", $ts_start, $ts_end, $session_done ); $stmt->execute(); $stmt->bind_result($author_id, $security_key, $creation_db, $last_login_ts, $f_name, $l_name, $email, $title, $org_name, $org_size, $manage_num, $manage_dir_num, $phone, $session_status); $obj_arr_status = new arr_status_types(); while ($stmt->fetch()) { $last_login = date('m/d/Y', $last_login_ts); $creation = date('m/d/Y', strtotime($creation_db)); $status_label = $obj_arr_status->format_status( $session_status ); ?> <tr> <td><?php echo $creation; ?></td> <td><?php echo $security_key; ?></td> <td><?php echo $email; ?></td> <td><?php echo $l_name; ?></td> <td><?php echo $f_name; ?></td> <td><?php echo $phone; ?></td> <td><?php echo $title; ?></td> <td><?php echo $org_name; ?></td> <td><?php echo $org_size; ?></td> <td><?php echo $manage_num; ?></td> <td><?php echo $manage_dir_num; ?></td> <td><?php echo $last_login; ?></td> <td><?php echo $status_label; ?></td> </tr> <?php } $stmt->close(); } // close if( $stmt = $db_connect->prepare($query) )