PHP Common::getSqlStringFieldsArray方法代码示例

 /**
  *  uses tracker db
  */
 public function insertNew($goal)
 {
     $Generic = Factory::getGeneric($this->db);
     // pg is throwing error when empty values are given for 'FLOAT' columns
     if (empty($goal['revenue'])) {
         unset($goal['revenue']);
     }
     if (empty($goal['revenue_subtotal'])) {
         unset($goal['revenue_subtotal']);
     }
     if (empty($goal['revenue_tax'])) {
         unset($goal['revenue_tax']);
     }
     if (empty($goal['revenue_shipping'])) {
         unset($goal['revenue_shipping']);
     }
     if (empty($goal['revenue_discount'])) {
         unset($goal['revenue_discount']);
     }
     $fields = implode(', ', array_keys($goal));
     $bindFields = Common::getSqlStringFieldsArray($goal);
     $goal['idvisitor'] = $Generic->bin2db($goal['idvisitor']);
     $sql = 'INSERT INTO ' . $this->table . '( ' . $fields . ' ) ' . 'VALUES ( ' . $bindFields . ' ) ';
     $bind = array_values($goal);
     $result = $Generic->insertIgnore($sql, $bind);
     return $result;
 }

 private function insertInto($table, $row)
 {
     $columns = implode(', ', array_keys($row));
     $columnsPlaceholders = Common::getSqlStringFieldsArray($row);
     $values = array_values($row);
     Db::query("INSERT INTO " . Common::prefixTable($table) . " ({$columns}) VALUES ({$columnsPlaceholders})", $values);
 }

 /**
  * Insert constructor.
  * @param string $table
  * @param array $columnValuePairs array(columnName => columnValue)
  */
 public function __construct($table, $columnValuePairs)
 {
     $columns = implode('`, `', array_keys($columnValuePairs));
     $bind = array_values($columnValuePairs);
     $sql = sprintf('INSERT INTO `%s` (`%s`) VALUES (%s)', $table, $columns, Common::getSqlStringFieldsArray($columnValuePairs));
     parent::__construct($sql, $bind, static::ERROR_CODE_DUPLICATE_ENTRY);
 }

 public function add($visitor_info)
 {
     $fields = implode(', ', array_keys($visitor_info));
     $values = Common::getSqlStringFieldsArray($visitor_info);
     $bind = array_values($visitor_info);
     $sql = 'INSERT INTO ' . $this->table . '( ' . $fields . ') VALUES (' . $values . ')';
     $this->db->query($sql, $bind);
     return $this->db->lastInsertId();
 }

 /**
  * Performs a batch insert into a specific table by iterating through the data
  *
  * NOTE: you should use tableInsertBatch() which will fallback to this function if LOAD DATA INFILE not available
  *
  * @param string $tableName PREFIXED table name! you must call Common::prefixTable() before passing the table name
  * @param array $fields array of unquoted field names
  * @param array $values array of data to be inserted
  * @param bool $ignoreWhenDuplicate Ignore new rows that contain unique key values that duplicate old rows
  */
 public static function tableInsertBatchIterate($tableName, $fields, $values, $ignoreWhenDuplicate = true)
 {
     $fieldList = '(' . join(',', $fields) . ')';
     $ignore = $ignoreWhenDuplicate ? 'IGNORE' : '';
     foreach ($values as $row) {
         $query = "INSERT {$ignore}\n\t\t\t\t\tINTO " . $tableName . "\n\t\t\t\t\t{$fieldList}\n\t\t\t\t\tVALUES (" . Common::getSqlStringFieldsArray($row) . ")";
         Db::query($query, $row);
     }
 }

 /**
  * Returns the list of all the users
  *
  * @param string[] $userLogins List of users to select. If empty, will return all users
  * @return array the list of all the users
  */
 public function getUsers(array $userLogins)
 {
     $where = '';
     $bind = array();
     if (!empty($userLogins)) {
         $where = 'WHERE login IN (' . Common::getSqlStringFieldsArray($userLogins) . ')';
         $bind = $userLogins;
     }
     $users = $this->db->fetchAll("SELECT * FROM " . $this->table . "\n                                           {$where}\n                                           ORDER BY login ASC", $bind);
     return $users;
 }

 /**
  * Returns the list of all the users
  *
  * @param string[] $userLogins List of users to select. If empty, will return all users
  * @return array the list of all the users
  */
 public function getUsers(array $userLogins)
 {
     $where = '';
     $bind = array();
     if (!empty($userLogins)) {
         $where = 'WHERE login IN (' . Common::getSqlStringFieldsArray($userLogins) . ')';
         $bind = $userLogins;
     }
     $users = Db::get()->fetchAll("SELECT *\n                                      FROM " . Common::prefixTable("user") . "\n                                      {$where}\n                                      ORDER BY login ASC", $bind);
     return $users;
 }

 public function createConversion($conversion)
 {
     $fields = implode(", ", array_keys($conversion));
     $bindFields = Common::getSqlStringFieldsArray($conversion);
     $table = Common::prefixTable('log_conversion');
     $db = $this->getDb();
     $Generic = Factory::getGeneric($db);
     $sql = "INSERT INTO {$table} ({$fields}) VALUES ({$bindFields}) ";
     if (!empty($conversion['idvisitor'])) {
         $conversion['idvisitor'] = $Generic->bin2db($conversion['idvisitor']);
     }
     $bind = array_values($conversion);
     $result = $Generic->insertIgnore($sql, $bind);
     // If a record was inserted, we return true
     return $db->rowCount($result) > 0;
 }

 /**
  * @param $valueToMatch
  * @param $sql
  * @return array|null
  * @throws \Exception
  */
 public function getIdActionFromSegment($valueToMatch, $sql)
 {
     if (!$this->isEnabled) {
         return array('SQL' => $sql, 'bind' => $valueToMatch);
     }
     $ids = self::getIdsFromCache($valueToMatch, $sql);
     if (is_null($ids)) {
         // Too Big To Cache, issue SQL as subquery instead
         return array('SQL' => $sql, 'bind' => $valueToMatch);
     }
     if (count($ids) == 0) {
         return null;
     }
     $sql = Common::getSqlStringFieldsArray($ids);
     $bind = $ids;
     return array('SQL' => $sql, 'bind' => $bind);
 }

 /**
  * Computes the total number of unique visitors who visited at least one site in,
  * a set of sites and the number of unique visitors that visited all of the sites
  * in the set.
  *
  * Comparison is done in dates for the UTC time, not for the site specific time.
  *
  * Performance: The SQL query this method executes was tested on a Piwik instance
  *              with 13 million visits total. Computing data for 4 sites with no
  *              date limit took 13s to complete.
  *
  * @param int[] $idSites The IDs of the sites for whom unique visitor counts should be
  *                       computed.
  * @param Date $startDate The lower bound of the date range of the visits to check.
  * @param Date $endDate The upper bound of the date range of the visits to check.
  * @param Segment $segment An optional segment to apply to the visits set before aggregation.
  *                         To supply no segment, use `new Segment()`.
  * @return int[] Returns two metrics: **nb_total_visitors** and **nb_shared_visitors**.
  *
  *               **nb_total_visitors** is the total number of unique visitors who visited
  *               at least one site in the list.
  *
  *               **nb_shared_visitors** is the total number of unique visitors who visited
  *               every site in the list.
  * @throws Exception if less than 2 site IDs are supplied,
  */
 public function getCommonVisitorCount($idSites, Date $startDate, Date $endDate, Segment $segment)
 {
     Log::debug("%s::%s('%s', '%s', '%s', '%s') called", "Model\\DistinctMetricsAggregator", __FUNCTION__, $idSites, $startDate, $endDate, $segment);
     if (count($idSites) == 1) {
         throw new Exception(Piwik::translate('InterSites_PleasSupplyAtLeastTwoDifferentSites'));
     }
     $select = "config_id, COUNT(DISTINCT idsite) AS sitecount";
     $from = array('log_visit');
     $where = 'visit_last_action_time >= ? AND visit_last_action_time <= ? AND idsite IN (' . Common::getSqlStringFieldsArray($idSites) . ')';
     $orderBy = false;
     $groupBy = 'config_id';
     $startDateTime = new \DateTime($startDate->toString());
     $endDateTime = new \DateTime($endDate->toString());
     $bind = array_merge(array($startDateTime->format("Y-m-d 00:00:00"), $endDateTime->format("Y-m-d 23:59:59")), $idSites);
     $innerQuery = $segment->getSelectQuery($select, $from, $where, $bind, $orderBy, $groupBy);
     $wholeQuery = "SELECT COUNT(sitecount_by_config.config_id) AS nb_total_visitors,\n                              SUM(IF(sitecount_by_config.sitecount >= " . count($idSites) . ", 1, 0)) AS nb_shared_visitors\n                         FROM ( {$innerQuery['sql']} ) AS sitecount_by_config";
     $result = Db::fetchRow($wholeQuery, $innerQuery['bind']);
     // nb_shared_visitors can be NULL if there are no visits
     if ($result['nb_shared_visitors'] === null) {
         $result['nb_shared_visitors'] = 0;
     }
     Log::debug("%s::%s() returned '%s'", "Model\\DistinctMetricsAggregator", __FUNCTION__, $result);
     return $result;
 }

 public function getTriggeredAlerts($idSites, $login)
 {
     $idSites = array_map('intval', $idSites);
     $db = $this->getDb();
     $sql = $this->getTriggeredAlertsSelectPart() . " WHERE idsite IN (" . Common::getSqlStringFieldsArray($idSites) . ")" . " AND login = ?";
     $values = $idSites;
     $values[] = $login;
     $alerts = $db->fetchAll($sql, $values);
     $alerts = $this->completeAlerts($alerts);
     return $alerts;
 }

 public function createVisit($visit)
 {
     $fields = array_keys($visit);
     $fields = implode(", ", $fields);
     $values = Common::getSqlStringFieldsArray($visit);
     $table = Common::prefixTable('log_visit');
     $sql = "INSERT INTO {$table} ({$fields}) VALUES ({$values})";
     $bind = array_values($visit);
     $db = $this->getDb();
     $db->query($sql, $bind);
     return $db->lastInsertId();
 }

 /**
  * Helper function used by other record* methods which will INSERT or UPDATE the conversion in the DB
  *
  * @param array $conversion
  * @param array $visitInformation
  * @return bool
  */
 protected function insertNewConversion($conversion, $visitInformation)
 {
     /**
      * Triggered before persisting a new [conversion entity](/guides/persistence-and-the-mysql-backend#conversions).
      *
      * This event can be used to modify conversion information or to add new information to be persisted.
      *
      * @param array $conversion The conversion entity. Read [this](/guides/persistence-and-the-mysql-backend#conversions)
      *                          to see what it contains.
      * @param array $visitInformation The visit entity that we are tracking a conversion for. See what
      *                                information it contains [here](/guides/persistence-and-the-mysql-backend#visits).
      * @param \Piwik\Tracker\Request $request An object describing the tracking request being processed.
      */
     Piwik::postEvent('Tracker.newConversionInformation', array(&$conversion, $visitInformation, $this->request));
     $newGoalDebug = $conversion;
     $newGoalDebug['idvisitor'] = bin2hex($newGoalDebug['idvisitor']);
     Common::printDebug($newGoalDebug);
     $fields = implode(", ", array_keys($conversion));
     $bindFields = Common::getSqlStringFieldsArray($conversion);
     $sql = 'INSERT IGNORE INTO ' . Common::prefixTable('log_conversion') . "\n                ({$fields}) VALUES ({$bindFields}) ";
     $bind = array_values($conversion);
     $result = Tracker::getDatabase()->query($sql, $bind);
     // If a record was inserted, we return true
     return Tracker::getDatabase()->rowCount($result) > 0;
 }

 public function insertEcommerceItems($goal, $itemsToInsert)
 {
     $sql = 'INSERT INTO ' . $this->table . '(
                idaction_sku
              , idaction_name
              , idaction_category
              , idaction_category2
              , idaction_category3
              , idaction_category4
              , idaction_category5
              , price
              , quantity
              , deleted
              , idorder
              , idsite
              , idvisitor
              , server_time
              , idvisit)
              VALUES ';
     $sql_parts = array();
     $bind = array();
     foreach ($itemsToInsert as $item) {
         $row = array_values($this->getItemRowEnriched($goal, $item));
         $sql_parts[] = ' ( ' . Common::getSqlStringFieldsArray($row) . ' ) ';
         $bind = array_merge($bind, $row);
     }
     $sql .= implode(', ', $sql_parts);
     $this->db->query($sql, $bind);
     Common::printDebug($sql);
     Common::printDebug($bind);
 }

 private function createLogIterationQuery($logTable, $idField, $fields, $conditions, $iterationStep)
 {
     $bind = array();
     $sql = "SELECT " . implode(', ', $fields) . " FROM `" . Common::prefixTable($logTable) . "` WHERE {$idField} > ?";
     foreach ($conditions as $condition) {
         list($column, $operator, $value) = $condition;
         if (is_array($value)) {
             $sql .= " AND {$column} IN (" . Common::getSqlStringFieldsArray($value) . ")";
             $bind = array_merge($bind, $value);
         } else {
             $sql .= " AND {$column} {$operator} ?";
             $bind[] = $value;
         }
     }
     $sql .= " ORDER BY {$idField} ASC LIMIT " . (int) $iterationStep;
     return array($sql, $bind);
 }