thesmart1
11-27-2008, 06:22 PM
I have the following query to list information for shows. It also gets from a phpBB table the username of the "user_id" of the person who posted the show. It works fine unless there is no row in the phpBB users table for the "user_id" from the shows table. So if a user is deleted, this will prevent all shows he/she may have posted from being listed by this query.
Query in PHP:
$showsResult = $db->sql_query("SELECT " . TABLE_SHOWS . ".id show_id, " . TABLE_SHOWS . ".user_id user_id, " . TABLE_SHOWS . ".name_eng name_eng, " . TABLE_SHOWS . ".name_jap name_jap, " . TABLE_SHOWS . ".tag tag, " . TABLE_SHOWS . ".add_time add_time, " . TABLE_SHOWS . ".avg_rating avg_rating, " . TABLE_SHOWS . ".comments comments, " . $phpbb_tbl_pre . "users.username user_name " .
"FROM " . TABLE_SHOWS . ", " . $phpbb_tbl_pre . "users ".
"WHERE " . $phpbb_tbl_pre . "users.user_id = " . TABLE_SHOWS . ".user_id " . @$where .
"ORDER BY " . TABLE_SHOWS . "." . $sortby_name[$sortby] . " " . strtoupper($orders[$showsOrder]) . " LIMIT " . $showsOffset . ", " . $perpage);I also tried this for the third line of the query (adding another statement to where to match null user_ids), but it didn't return the results where no row existed in phpBB's users table.
"WHERE (" . $phpbb_tbl_pre . "users.user_id = " . TABLE_SHOWS . ".user_id OR " . $phpbb_tbl_pre . "users.user_id = NULL) " . @$where .$sortby_name[$sortby] is a column name in TABLE_SHOWS to sort by. $where is an optional parameter to narrow results (a 'like' match for the first character of the "name_eng" column). $orders[$showsOrder] is either "asc" or "desc". $showsOffset is just an offset determined by the page number of the results.
So my question is: is there a way to also include in the results rows where there is no row in phpBB's users table to match the "user_id" in TABLE_SHOWS?
Query in PHP:
$showsResult = $db->sql_query("SELECT " . TABLE_SHOWS . ".id show_id, " . TABLE_SHOWS . ".user_id user_id, " . TABLE_SHOWS . ".name_eng name_eng, " . TABLE_SHOWS . ".name_jap name_jap, " . TABLE_SHOWS . ".tag tag, " . TABLE_SHOWS . ".add_time add_time, " . TABLE_SHOWS . ".avg_rating avg_rating, " . TABLE_SHOWS . ".comments comments, " . $phpbb_tbl_pre . "users.username user_name " .
"FROM " . TABLE_SHOWS . ", " . $phpbb_tbl_pre . "users ".
"WHERE " . $phpbb_tbl_pre . "users.user_id = " . TABLE_SHOWS . ".user_id " . @$where .
"ORDER BY " . TABLE_SHOWS . "." . $sortby_name[$sortby] . " " . strtoupper($orders[$showsOrder]) . " LIMIT " . $showsOffset . ", " . $perpage);I also tried this for the third line of the query (adding another statement to where to match null user_ids), but it didn't return the results where no row existed in phpBB's users table.
"WHERE (" . $phpbb_tbl_pre . "users.user_id = " . TABLE_SHOWS . ".user_id OR " . $phpbb_tbl_pre . "users.user_id = NULL) " . @$where .$sortby_name[$sortby] is a column name in TABLE_SHOWS to sort by. $where is an optional parameter to narrow results (a 'like' match for the first character of the "name_eng" column). $orders[$showsOrder] is either "asc" or "desc". $showsOffset is just an offset determined by the page number of the results.
So my question is: is there a way to also include in the results rows where there is no row in phpBB's users table to match the "user_id" in TABLE_SHOWS?