public function __construct() { $conn = new Connection(); $conn->setConnectionParams('127.0.0.1', 9307); $this->conn = $conn; SphinxQL::create($this->conn)->query('TRUNCATE RTINDEX rt')->execute(); }
public function search_products($search_string) { $sphinxQL = new Connection(); $sphinxQL->setConnectionParams(SPHINX_IP, SPHINX_PORT); $match_search_string = $search_string; $select = "`id_m_c_products`, `in_stock` AS order_in_stock, weight()"; $this->load->model('warehouse/mwarehouses'); if ($wh_id = $this->mwarehouses->get_shop_wh_id()) { $select = "`id_m_c_products`, IF (qty, in_stock, 0) AS order_in_stock, weight()"; } $search_query = "SELECT " . $select . "\n\t\tFROM `" . self::SPHINX_INDEX . "`\n\t\tWHERE `id_users` = " . $this->id_users . " AND `status` = 1 AND MATCH('(" . $match_search_string . ") | (@sku *" . $match_search_string . "*)')\n\t\tGROUP BY `id_m_c_products`\n\t\tORDER BY `order_in_stock` DESC, weight() DESC\n\t\tLIMIT 0, 2000\n\t\tOPTION max_matches = 2000,\n\t\tfield_weights = (name = 100, short_description = 30, full_description = 20, seo_title = 50, seo_description = 30, seo_keywords = 100, sku = 200)"; $sphinx_result = $sphinxQL->query($search_query); $search_count = count($sphinx_result); $this->load->model('catalogue/mproducts'); $products_settings = $this->mproducts->get_settings(); $search_limit = $products_settings['products_count_to_page']; list($pages, $limit_data) = $this->set_limit($search_count, $search_limit, $search_string); $products_array = array(); $products_id = array(); $seo = array('seo_title' => $match_search_string, 'seo_description' => $match_search_string); $this->template->set_TDK($seo); if ($search_count > 0) { for ($i = $limit_data[0]; $i < $limit_data[1]; $i++) { $products_id[$sphinx_result[$i]['id_m_c_products']] = $sphinx_result[$i]['id_m_c_products']; } $products_temp = $this->mproducts->get_search_products_collection($products_id); foreach ($products_id as $key => $ms) { if (isset($products_temp['products'][$key])) { $products_array[] = $products_temp['products'][$key]; } } } return array('products' => $products_array, 'pages' => $pages, 'search_keywords' => $match_search_string); }
public function sphinx_update_pr_qty_array($wh_id, $pr_array) { $this->db->select("`" . self::ID_WH . "`, `i_s_wh`")->from("`" . self::WH . "`")->where("`" . self::ID_WH . "`", $wh_id)->where("`" . self::ID_USERS . "`", $this->id_users)->limit(1); $result = $this->db->get()->row_array(); if ($result['i_s_wh'] == 1) { $sphinxQL = new Connection(); $sphinxQL->setConnectionParams(SPHINX_IP, SPHINX_PORT); foreach ($pr_array as $pr_id => $ms) { $sphinxQL->query("UPDATE `" . self::SPHINX_INDEX . "` SET\n\t\t\t\t\t`qty` = " . $ms['qty'] . "\n\t\t\t\t\tWHERE `" . self::ID_PR . "` = " . $pr_id); } } }
/** * Create and connect to Sphinx */ public function getSphinx() { $conn = new Connection(); $conn->setConnectionParams($this->Config->Sphinx->Host, $this->Config->Sphinx->Port); return SphinxQL::create($conn); }
public function change_pr_aditional_param($id, $field, $value = 1) { if (is_array($id)) { $this->db->where("`" . self::ID_USERS . "`", $this->id_users)->where_in("`" . self::ID_PR . "`", $id); } else { $this->db->where("`" . self::ID_USERS . "`", $this->id_users)->where("`" . self::ID_PR . "`", $id); } $this->db->set("`" . $field . "`", $value)->update(self::PR); if ($field == 'new') { if (is_array($id)) { $this->db->where("`" . self::ID_USERS . "`", $this->id_users)->where_in("`" . self::ID_PR . "`", $id); } else { $this->db->where("`" . self::ID_USERS . "`", $this->id_users)->where("`" . self::ID_PR . "`", $id); } $this->db->set("`" . $field . "`", $value)->update(self::PR_CAT); } if ($field == 'in_stock' || $field == 'new' || $field == 'sale' || $field == 'bestseller' || $field == 'status') { $sphinxQL = new Connection(); $sphinxQL->setConnectionParams(SPHINX_IP, SPHINX_PORT); $sphinx_query = "UPDATE `" . self::SPHINX_INDEX . "` SET\n \t\t\t`" . $field . "` = " . $value . "\n \t\t"; if (is_array($id)) { $sphinx_query .= "WHERE `" . self::ID_PR . "` IN(" . implode(',', $id) . ")"; } else { $sphinx_query .= "WHERE `" . self::ID_PR . "` = " . $id; } $sphinxQL->query($sphinx_query); } }
public function sphinx_import_dev() { $sphinx_conn = new Connection(); $sphinx_conn->setConnectionParams('127.0.0.1', 3312); $this->db->select("`id_users`, `warehouse`")->from("`users`")->order_by("id_users"); $users = $this->db->get()->result_array(); foreach ($users as $usr) { $wh_select_part = "0 AS qty, "; $wh_join_part = ""; if ($usr['warehouse'] == 1) { $this->db->select("`id_wh`")->from("`wh`")->where("`id_users`", $usr['id_users'])->where("`i_s_wh`", 1)->limit(1); $wh = $this->db->get()->row_array(); if (count($wh) > 0) { $wh_select_part = 'IF(WH_PR.`qty`, WH_PR.`qty`, 0) AS qty, '; $wh_join_part = 'LEFT JOIN `wh_products` AS WH_PR ON WH_PR.`id_m_c_products` = B.`id_m_c_products` && WH_PR.`id_wh` = ' . $wh['id_wh']; } } /*$start = 33000; $step = 1000; $end = $start + $step; $this->db->select("count(*) AS COUNT") ->from("m_c_products"); $count = $this->db->get()->row_array(); //$count = $count['COUNT']; $count = 70000; $for_count = ceil($count/$step);*/ $sql_query = ' SELECT B.`id_m_c_products_description` as id, A.`id_m_c_products`, A.`sku`, A.`status`, A.`in_stock`, A.`new`, A.`bestseller`, A.`sale`, ' . $wh_select_part . 'A.`id_users`, B.`id_langs`, UNIX_TIMESTAMP(A.`create_date`) AS create_date , (SELECT `price` FROM `m_c_products_price` WHERE `id_m_c_products` = B.`id_m_c_products` ORDER BY `id_m_c_products` LIMIT 1) AS price, B.`name` , B.`short_description` , B.`full_description` , B.`seo_title` , B.`seo_description` , B.`seo_keywords` , GROUP_CONCAT( DISTINCT(CAST( PNC.`id_m_c_categories` AS CHAR )) ORDER BY PNC.`id_m_c_categories` SEPARATOR "," ) AS id_m_c_categories, GROUP_CONCAT( DISTINCT(CAST( PNT.`id_m_c_products_types` AS CHAR )) ORDER BY PNT.`id_m_c_products_types` SEPARATOR "," ) AS id_m_c_products_types, GROUP_CONCAT( DISTINCT(CAST( PNT.`id_m_c_products_properties` AS CHAR )) ORDER BY PNT.`id_m_c_products_properties` SEPARATOR "," ) AS id_m_c_products_properties, GROUP_CONCAT( DISTINCT(CAST( PNA.`id_m_c_products_attributes` AS CHAR )) ORDER BY PNA.`id_m_c_products_attributes` SEPARATOR "," ) AS id_m_c_products_attributes, GROUP_CONCAT( DISTINCT(CAST( PNA.`id_m_c_products_attributes_options` AS CHAR )) ORDER BY PNA.`id_m_c_products_attributes_options` SEPARATOR "," ) AS id_m_c_products_attributes_options FROM `m_c_products_description` AS B INNER JOIN `m_c_products` AS A ON A.`id_m_c_products` = B.`id_m_c_products` && A.`id_users` = ' . $usr['id_users'] . ' ' . $wh_join_part . ' LEFT JOIN `m_c_productsNcategories` AS PNC ON PNC.`id_m_c_products` = B.`id_m_c_products` LEFT JOIN `m_c_productsNtypes` AS PNT ON PNT.`id_m_c_products` = B.`id_m_c_products` LEFT JOIN `m_c_productsNattributes` AS PNA ON PNA.`id_m_c_products` = B.`id_m_c_products` GROUP BY (B.`id_m_c_products_description`);'; $insert_part = ""; $DB = $this->db->query($sql_query); $DB_res = $DB->result_array(); if (count($DB_res) > 0) { foreach ($DB_res as $ms) { $insert_part .= "(" . $ms['id'] . ", " . $ms['id_m_c_products'] . ", '" . $ms['sku'] . "', " . $ms['status'] . ", " . $ms['in_stock'] . ", " . $ms['new'] . ", " . $ms['bestseller'] . ", " . $ms['sale'] . ", " . $ms['qty'] . ", " . $ms['id_users'] . ", " . $ms['id_langs'] . ", " . $ms['create_date'] . ",\n\t\t\t\t\t" . floatval($ms['price']) . ",\n\t\t\t\t\t'" . addslashes(trim(htmlspecialchars(str_replace(' ', '', strip_tags($ms['name'])), ENT_COMPAT, 'UTF-8'))) . "',\n\t\t\t\t\t'" . addslashes(trim(htmlspecialchars(str_replace(' ', '', strip_tags($ms['short_description'])), ENT_COMPAT, 'UTF-8'))) . "',\n\t\t\t\t\t'" . addslashes(trim(htmlspecialchars(str_replace(' ', '', strip_tags($ms['full_description'])), ENT_COMPAT, 'UTF-8'))) . "',\n\t\t\t\t\t'" . addslashes($ms['seo_title']) . "',\n\t\t\t\t\t'" . addslashes($ms['seo_description']) . "',\n\t\t\t\t\t'" . addslashes($ms['seo_keywords']) . "',\n\t\t\t\t\t(" . $ms['id_m_c_categories'] . "), (" . $ms['id_m_c_products_types'] . "), (" . $ms['id_m_c_products_properties'] . "), (" . $ms['id_m_c_products_attributes'] . "), (" . $ms['id_m_c_products_attributes_options'] . ")),"; } $insert_part = substr($insert_part, 0, strlen($insert_part) - 1); echo $usr['id_users'] . "<BR>"; $sphinx_query = "INSERT INTO `gbc_users_m_c_products_index_rt` (`id`, `id_m_c_products`, `sku`, `status`, `in_stock`, `new`, `bestseller`, `sale`, `qty`, `id_users`, `id_langs`, `create_date`, `price`, `name`, `short_description`, `full_description`, `seo_title`, `seo_description`, `seo_keywords`, `id_m_c_categories`, `id_m_c_products_types`, `id_m_c_products_properties`, `id_m_c_products_attributes`, `id_m_c_products_attributes_options`) VALUES"; $sphinx_query .= $insert_part; //echo $sphinx_query; exit; $result = $sphinx_conn->query($sphinx_query); } } $result = $sphinx_conn->query("SELECT count(*) FROM `gbc_users_m_c_products_index_rt` OPTION max_matches=50000"); echo var_dump($result); }