/** * Updated search method for users. Search type can also be specified with every field one is searching for * e.g $search_item['first_name'] = array('value'=> 'test', 'type'=> LIKE_SEARCH) here type defines the search type * in this case it is LIKE SEARCH (constant defined in api_constants.php) means value * will be searched field_value = '%test%' * For date of birth we can specify date range like $search_item['dob'] = array('value'=> * array('lower_limit'=>200400, 'upper_limit'=>300500), 'type'=> RANGE_SEARCH) * This method will help us to search for value given in range and will give more freedom than the * method load_info_by_search which uses only LIKE to search for values. */ public static function user_search($search_item, $user_id, $network_id = NULL, $cnt = FALSE, $show = 'ALL', $page = 0, $sort_by = 'U.created', $direction = 'DESC', $condition = NULL) { Logger::log("Enter: User::user_search"); $db = Dal::get_connection(); $data = array(); $uids = array(); $i = 0; $j = 1; $PEARObject = new PEAR(); $order_by = $sort_by . ' ' . $direction; if ($show == 'ALL' || $cnt == TRUE) { $limit = ''; } else { $start = ($page - 1) * $show; $limit = 'LIMIT ' . $start . ',' . $show; } // ---- fix by Z.Hron: We don't need to read all data to count rows! Use MySQL function COUNT() in future! $search_string = "SELECT (U.user_id) as uid, U.first_name as first_name, U.login_name as login_name, U.picture as picture, UP.field_perm as field_perm, count(U.user_id) as counts, U.created"; if ($cnt) { $search_string = "SELECT count(U.user_id) as counts, (U.user_id) as uid"; } // ---- EOF if ($network_id) { $search_string .= " FROM users AS U LEFT OUTER JOIN user_profile_data AS UP ON UP.user_id = U.user_id INNER JOIN networks_users AS NU ON U.user_id = NU.user_id AND NU.network_id = ? "; $data[] = $network_id; } else { $search_string .= " FROM users AS U LEFT OUTER JOIN user_profile_data AS UP ON UP.user_id = U.user_id "; } if (!empty($search_item['group_id'])) { $group_id = $search_item['group_id']['value']; unset($search_item['group_id']); $search_string .= " INNER JOIN {groups_users} AS GU ON U.user_id = GU.user_id AND GU.group_id = ? "; $data[] = $group_id; } if (!empty($search_item['in_relation'])) { $relation_id = $search_item['in_relation']['value']; $search_string .= " INNER JOIN {relations} AS RU\n ON U.user_id = RU.relation_id\n AND RU.user_id = ? "; $data[] = $relation_id; if (!empty($search_item['in_relation']['type'])) { $status = $search_item['in_relation']['type']; $search_string .= " AND RU.status = ? "; $data[] = $status; } unset($search_item['in_relation']); } if (!empty($condition)) { $search_string .= " WHERE {$condition} AND "; } else { $search_string .= " WHERE 1 AND "; // field_perm > 0 AND "; // removed - field_perm checked for each field in code bellow! } $search_items_count = count($search_item); if ($search_items_count > 0) { $counter = 0; foreach ($search_item as $field_name => $field_details) { $counter++; switch ($field_details['type']) { case AGE_SEARCH: //date of birth will be saved in the formay YYYY-MM-DD $search_string .= '( UP.field_name = ? AND UP.field_value BETWEEN DATE(DATE_ADD(NOW(), INTERVAL ? YEAR)) AND DATE(DATE_ADD(NOW(), INTERVAL ? YEAR)) AND UP.field_perm <> ?)'; $data[] = $field_name; $data[] = $field_details['value']['upper_limit'] * -1; $data[] = $field_details['value']['lower_limit'] * -1; $data[] = NONE; break; case GREATER_THAN: $search_string .= '( UP.field_name = ? AND UP.field_value ' . $field_details['type'] . ' ? AND UP.field_perm <> ? )'; $data[] = $field_name; $data[] = $field_details['value']; $data[] = NONE; break; case RANGE_SEARCH: $search_string .= '( UP.field_name = ? AND UP.field_value ' . $field_details['type'] . ' ? AND ? AND UP.field_perm <> ? )'; $data[] = $field_name; $data[] = $field_details['value']['lower_limit']; $data[] = $field_details['value']['upper_limit']; $data[] = NONE; break; case LIKE_SEARCH: if (!empty($field_details['ignore_perm']) && $field_details['ignore_perm'] == true) { $search_string .= '( UP.field_name = ? AND UP.field_value ' . $field_details['type'] . ' ? )'; $data[] = $field_name; $data[] = '%' . $field_details['value'] . '%'; } else { $search_string .= '( UP.field_name = ? AND UP.field_value ' . $field_details['type'] . ' ? AND UP.field_perm <> ? )'; $data[] = $field_name; $data[] = '%' . $field_details['value'] . '%'; $data[] = NONE; } break; case GLOBAL_SEARCH: if (!empty($field_details['ignore_perm']) && $field_details['ignore_perm'] == true) { $search_string .= '( UP.field_value LIKE ? )'; $data[] = '%' . $field_details['value'] . '%'; } else { $search_string .= '( UP.field_value LIKE ? AND UP.field_perm <> ? )'; $data[] = '%' . $field_details['value'] . '%'; $data[] = NONE; } break; case IN_SEARCH: $search_string .= '( UP.field_name = ? AND UP.field_value IN ( ' . $field_details['value'] . ' ) AND UP.field_perm <> ? )'; $data[] = $field_name; $data[] = NONE; break; default: $search_string .= '( UP.field_name = ? AND UP.field_value ' . $field_details['type'] . ' ? AND UP.field_perm <> ? )'; $data[] = $field_name; $data[] = $field_details['value']; $data[] = NONE; } $search_string .= $search_items_count == $counter ? ' AND ' : ' OR '; } } $search_string .= " U.is_active = ? GROUP BY uid HAVING counts >= ? ORDER BY {$order_by} {$limit}"; $data[] = ACTIVE; $data[] = $search_items_count; $res = Dal::query($search_string, $data); if ($PEARObject->isError($res)) { Logger::log(" Throwing exception DB_QUERY_FAILED | Message: {$res->getMessage}()", LOGGER_ERROR); throw new PAException(DB_QUERY_FAILED, $res->getMessage()); } if ($cnt) { // fix by Z.Hron: We don't need to read all data to count rows! Use MySQL function COUNT() in future! $u_data = $res->fetchRow(DB_FETCHMODE_OBJECT); return !empty($u_data) ? $u_data->counts : 0; // return $res->numRows(); } $uid_array = array(); if ($res->numrows() > 0) { $i = 0; while ($row = $res->fetchRow(DB_FETCHMODE_OBJECT)) { $uid_array[$i]['user_id'] = $row->uid; $uid_array[$i]['login_name'] = $row->login_name; $uid_array[$i]['picture'] = $row->picture; $uid_array[$i]['first_name'] = $row->first_name; $uid_array[$i]['field_perm'] = $row->field_perm; $uid_array[$i]['created'] = $row->created; $i++; } } // search according to perm $sql = "Select user_id from {relations} where relation_id = {$user_id}"; $res = Dal::query($sql); if ($PEARObject->isError($res)) { Logger::log(" Throwing exception DB_QUERY_FAILED | Message: {$res->getMessage}()", LOGGER_ERROR); throw new PAException(DB_QUERY_FAILED, $res->getMessage()); } if ($res->numrows() > 0) { while ($row = $res->fetchRow(DB_FETCHMODE_OBJECT)) { $user_is_friend[] = $row->user_id; } } $j = 0; $user_ids = array(); for ($i = 0; $i < count($uid_array); $i++) { if ($uid_array[$i]['field_perm'] == WITH_IN_DEGREE_1) { if (!empty($user_is_friend)) { if (in_array($uid_array[$i]['user_id'], $user_is_friend) || $user_id == $uid_array[$i]['user_id']) { $user_ids[$j]['user_id'] = $uid_array[$i]['user_id']; $user_ids[$j]['login_name'] = $uid_array[$i]['login_name']; $user_ids[$j]['first_name'] = $uid_array[$i]['first_name']; $user_ids[$j]['picture'] = $uid_array[$i]['picture']; $user_ids[$j]['created'] = $uid_array[$i]['created']; $j++; } } } else { if ($uid_array[$i]['field_perm'] == NONE) { // used when field_perm attribute ignored $user_ids[$j]['user_id'] = $uid_array[$i]['user_id']; $user_ids[$j]['login_name'] = $uid_array[$i]['login_name']; $user_ids[$j]['first_name'] = $uid_array[$i]['first_name']; $user_ids[$j]['picture'] = $uid_array[$i]['picture']; $user_ids[$j]['created'] = $uid_array[$i]['created']; $j++; } else { $user_ids[$j]['user_id'] = $uid_array[$i]['user_id']; $user_ids[$j]['login_name'] = $uid_array[$i]['login_name']; $user_ids[$j]['first_name'] = $uid_array[$i]['first_name']; $user_ids[$j]['picture'] = $uid_array[$i]['picture']; $user_ids[$j]['created'] = $uid_array[$i]['created']; $j++; } } } $users_with_data = array('users_data' => $user_ids, 'total_users' => count($user_ids)); Logger::log("Exit: User::user_search"); return $users_with_data; }
<?php require_once dirname(__FILE__) . '/../config.inc'; require_once "{$path_prefix}/db/Dal/Dal.php"; require_once "{$path_prefix}/db/Dal/DbUpdate.php"; require_once "{$path_prefix}/api/Network/Network.php"; // Re-include constants.php to make sure we have the most up to date // constants. If we are in the middle of an update and this script is // being included by web/update/run_scripts.php, we might not have all // the constants. include "{$path_prefix}/web/includes/constants.php"; // $settings_new contains the mapping of page names to modules they contain. global $settings_new; $db = Dal::get_connection(); foreach (DbUpdate::get_valid_networks() as $net_address) { set_time_limit(30); $net = Network::get_network_by_address($net_address); $table_name = 'page_default_settings'; if ($net->type != MOTHER_NETWORK_TYPE) { // 1 for home network $table_name = $net->address . '_' . $table_name; } $sql = ' TRUNCATE TABLE ' . $table_name; $res = Dal::query($sql); foreach ($settings_new as $page_id => $v1) { $page_name = $v1['page_name']; $data = $v1['data']; $settings_data = serialize($data); $is_configurable = isset($v1['is_configurable']) ? $v1['is_configurable'] : FALSE; //default value will be false is not specified $sql = "INSERT INTO {$table_name} (page_id, page_name, default_settings, is_configurable) VALUES (?, ?, ?, ?)";
public static function quote($s) { return Dal::get_connection()->escapeSimple($s); }
function main() { $this->db = Dal::get_connection(); $this->note("Doing database update"); // We use $this->db->getOne() below instead of Dal::query_one() as // the first time this script is run, the mc_db_status table will // not exist, which will fire an exception with Dal::query_one() // and break the installation. Please don't change this to // Dal::query_one()! -PP 2006-11-15 $db_status = $this->db->getOne("SELECT * FROM mc_db_status LIMIT 1"); if (!DB::isError($db_status)) { $this->note("mc_db_status table in place"); } else { $this->note("Creating mc_db_status table"); $this->query("CREATE TABLE mc_db_status (stmt_key VARCHAR(255) NOT NULL, PRIMARY KEY(stmt_key))"); } // add network column if (!$this->column_exists("mc_db_status", "network")) { $this->query("ALTER TABLE mc_db_status ADD COLUMN network VARCHAR(50) NOT NULL DEFAULT ''"); $this->query("ALTER TABLE mc_db_status DROP PRIMARY KEY"); $this->query("ALTER TABLE mc_db_status ADD PRIMARY KEY(stmt_key, network)"); } /* 'broken' col disabled for now - use $this->broken_networks instead. // make sure the network table has the 'broken' column before we get started if (!$this->column_exists("networks", "broken")) { Dal::query("ALTER TABLE networks ADD COLUMN broken BOOLEAN DEFAULT '0'"); }*/ // find networks which have their tables (i.e. skip over broken networks) $this->networks = DbUpdate::get_valid_networks(); $override = @$_GET['override']; if (!empty($override)) { try { Dal::query("INSERT INTO mc_db_status SET stmt_key=?", Array($override)); } catch (PAException $e) { echo "<p>exception trying to override: ".$e->getMessage()."</p>"; } } $this->do_updates(); if (!$this->quiet) { // $this->dump_schema(); $this->note("db update done."); } }
function main() { $this->db = Dal::get_connection(); $this->write('<table>'); $this->note('Doing database update'); // We use $this->db->getOne() below instead of Dal::query_one() as // the first time this script is run, the mc_db_status table will // not exist, which will fire an exception with Dal::query_one() // and break the installation. Please don't change this to // Dal::query_one()! -PP 2006-11-15 $db_status = $this->db->getOne('SELECT * FROM mc_db_status LIMIT 1'); if (!DB::isError($db_status)) { $this->note('mc_db_status table in place'); } else { $this->note('Creating mc_db_status table'); $this->query('CREATE TABLE mc_db_status (stmt_key VARCHAR(255) NOT NULL, PRIMARY KEY(stmt_key))'); } // add network column if (!$this->column_exists('mc_db_status', 'network')) { $this->query('ALTER TABLE mc_db_status ADD COLUMN network VARCHAR(50) NOT NULL DEFAULT \'\''); $this->query('ALTER TABLE mc_db_status DROP PRIMARY KEY'); $this->query('ALTER TABLE mc_db_status ADD PRIMARY KEY(stmt_key, network)'); } // find networks which have their tables (i.e. skip over broken networks) $this->networks = DbUpdate::get_valid_networks(); $override = @$_GET['override']; if (!empty($override)) { try { Dal::query('INSERT INTO mc_db_status SET stmt_key=?', array($override)); } catch (PAException $e) { echo '<p>exception trying to override: ', $e->getMessage(), '</p>'; } } $this->do_updates(); if (!$this->quiet) { $this->note('CORE db updates done.'); $this->write('</table>'); } }
/** * Updated search method for users. Search type can also be specified with every field one is searching for * e.g $search_item['first_name'] = array('value'=> 'test', 'type'=> LIKE_SEARCH) here type defines the search type * in this case it is LIKE SEARCH (constant defined in api_constants.php) means value * will be searched field_value = '%test%' * For date of birth we can specify date range like $search_item['dob'] = array('value'=> * array('lower_limit'=>200400, 'upper_limit'=>300500), 'type'=> RANGE_SEARCH) * This method will help us to search for value given in range and will give more freedom than the * method load_info_by_search which uses only LIKE to search for values. */ public static function user_search($search_item, $user_id, $network_id = NULL, $cnt = FALSE, $show = 'ALL', $page = 0, $sort_by = 'U.created', $direction = 'DESC') { Logger::log("Enter: User::user_search"); $db = Dal::get_connection(); $data = array(); $uids = array(); $i = 0; $j = 1; $order_by = $sort_by . ' ' . $direction; if ($show == 'ALL' || $cnt == TRUE) { $limit = ''; } else { $start = ($page - 1) * $show; $limit = 'LIMIT ' . $start . ',' . $show; } if ($network_id) { $search_string = "SELECT (U.user_id) as uid, U.first_name as first_name, U.login_name as login_name, U.picture as picture, UP.field_perm as field_perm, count(U.user_id) as counts FROM users AS U LEFT OUTER JOIN user_profile_data AS UP ON UP.user_id = U.user_id INNER JOIN networks_users AS NU ON U.user_id = NU.user_id AND NU.network_id = ? WHERE "; $data[] = $network_id; } else { $search_string = "SELECT (U.user_id) as uid, U.first_name as first_name, U.login_name as login_name, U.picture as picture, UP.field_perm as field_perm, count(U.user_id) as counts FROM users AS U LEFT OUTER JOIN user_profile_data AS UP ON UP.user_id = U.user_id WHERE "; } $search_items_count = count($search_item); if ($search_items_count > 0) { $counter = 0; foreach ($search_item as $field_name => $field_details) { $counter++; switch ($field_details['type']) { case AGE_SEARCH: //date of birth will be saved in the formay YYYY-MM-DD $search_string .= '( UP.field_name = ? AND UP.field_value BETWEEN DATE(DATE_ADD(NOW(), INTERVAL ? YEAR)) AND DATE(DATE_ADD(NOW(), INTERVAL ? YEAR)) AND UP.field_perm <> ?)'; $data[] = $field_name; $data[] = $field_details['value']['upper_limit'] * -1; $data[] = $field_details['value']['lower_limit'] * -1; $data[] = NONE; break; case GREATER_THAN: $search_string .= '( UP.field_name = ? AND UP.field_value ' . $field_details['type'] . ' ? AND UP.field_perm <> ? )'; $data[] = $field_name; $data[] = $field_details['value']; $data[] = NONE; break; case RANGE_SEARCH: $search_string .= '( UP.field_name = ? AND UP.field_value ' . $field_details['type'] . ' ? AND ? AND UP.field_perm <> ? )'; $data[] = $field_name; $data[] = $field_details['value']['lower_limit']; $data[] = $field_details['value']['upper_limit']; $data[] = NONE; break; case LIKE_SEARCH: $search_string .= '( UP.field_name = ? AND UP.field_value ' . $field_details['type'] . ' ? AND UP.field_perm <> ? )'; $data[] = $field_name; $data[] = '%' . $field_details['value'] . '%'; $data[] = NONE; break; default: $search_string .= '( UP.field_name = ? AND UP.field_value ' . $field_details['type'] . ' ? AND UP.field_perm <> ? )'; $data[] = $field_name; $data[] = $field_details['value']; $data[] = NONE; } $search_string .= $search_items_count == $counter ? ' AND ' : ' OR '; } } $search_string .= " U.is_active = ? GROUP BY uid HAVING counts = ? ORDER BY {$order_by} {$limit}"; $data[] = ACTIVE; $data[] = $search_items_count; // Logger::log(print_r($search_item,1)."\n$search_string\n".print_r($data,1), LOGGER_ACTION); $res = Dal::query($search_string, $data); if (PEAR::isError($res)) { Logger::log(" Throwing exception DB_QUERY_FAILED | Message: {$res->getMessage}()", LOGGER_ERROR); throw new PAException(DB_QUERY_FAILED, $res->getMessage()); } if ($cnt) { return $res->numRows(); } $uid_array = array(); if ($res->numrows() > 0) { $i = 0; while ($row = $res->fetchRow(DB_FETCHMODE_OBJECT)) { $uid_array[$i]['user_id'] = $row->uid; $uid_array[$i]['login_name'] = $row->login_name; $uid_array[$i]['picture'] = $row->picture; $uid_array[$i]['first_name'] = $row->first_name; $uid_array[$i]['field_perm'] = $row->field_perm; $i++; } } // search according to perm $sql = "Select user_id from {relations} where relation_id = {$user_id}"; $res = Dal::query($sql); if (PEAR::isError($res)) { Logger::log(" Throwing exception DB_QUERY_FAILED | Message: {$res->getMessage}()", LOGGER_ERROR); throw new PAException(DB_QUERY_FAILED, $res->getMessage()); } if ($res->numrows() > 0) { while ($row = $res->fetchRow(DB_FETCHMODE_OBJECT)) { $user_is_friend[] = $row->user_id; } } $j = 0; $user_ids = array(); for ($i = 0; $i < count($uid_array); $i++) { if ($uid_array[$i]['field_perm'] == WITH_IN_DEGREE_1) { if (!empty($user_is_friend)) { if (in_array($uid_array[$i]['user_id'], $user_is_friend) || $user_id == $uid_array[$i]['user_id']) { $user_ids[$j]['user_id'] = $uid_array[$i]['user_id']; $user_ids[$j]['login_name'] = $uid_array[$i]['login_name']; $user_ids[$j]['first_name'] = $uid_array[$i]['first_name']; $user_ids[$j]['picture'] = $uid_array[$i]['picture']; $j++; } } } else { if ($uid_array[$i]['field_perm'] == NONE) { // Do nothing. } else { $user_ids[$j]['user_id'] = $uid_array[$i]['user_id']; $user_ids[$j]['login_name'] = $uid_array[$i]['login_name']; $user_ids[$j]['first_name'] = $uid_array[$i]['first_name']; $user_ids[$j]['picture'] = $uid_array[$i]['picture']; $j++; } } } $users_with_data = array('users_data' => $user_ids, 'total_users' => count($user_ids)); Logger::log("Exit: User::user_search"); return $users_with_data; }