..)` part of a query.
public static getSqlStringFieldsArray ( array | string $fields ) : string | ||
$fields | array | string | The names of the mysql table fields to bind, e.g. `array(fieldName1, fieldName2, fieldName3)`. _Note: The content of the array isn't important, just its length._ |
return | string | The placeholder string, e.g. `"?, ?, ?"`. |
/** * 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); }
public function add($visitor_info) { $fields = implode(', ', array_keys($visitor_info)); $values = Common::getSqlStringFieldsArray($visitor_info); $visitor_info['config_id'] = bin2hex($visitor_info['config_id']); $visitor_info['idvisitor'] = bin2hex($visitor_info['idvisitor']); $visitor_info['location_ip'] = bin2hex($visitor_info['location_ip']); // Integration/BlobReportLimitingTest.php was failing because config_device_type // had bool(false) as value which is being interpreted as empty string by postgresql. // To avoid the issue, if config_device_type is false, it is being set to null. if (isset($visitor_info['config_device_type']) && $visitor_info['config_device_type'] === false) { $visitor_info['config_device_type'] = null; } $bind = array_values($visitor_info); $sql = 'INSERT INTO ' . $this->table . '( ' . $fields . ') VALUES (' . $values . ')'; $this->db->query($sql, $bind); return $this->db->lastInsertId($this->table . '_idvisit'); }
/** * 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); }
public function insertRecord($tableName, $bindArray) { $values = Common::getSqlStringFieldsArray($bindArray); $sql = 'INSERT IGNORE INTO ' . $tableName . '( ' . implode(', ', array_keys($bindArray)) . ')' . ' VALUES ( ' . $values . ' ) '; $this->db->query($sql, array_values($bindArray)); }
/** * Returns all websites with a timezone matching one the specified timezones * * @param array $timezones * @return array * @ignore */ public function getSitesIdFromTimezones($timezones) { Piwik::checkUserHasSuperUserAccess(); $timezones = Piwik::getArrayFromApiParameter($timezones); $timezones = array_unique($timezones); $ids = Db::get()->fetchAll('SELECT idsite FROM ' . Common::prefixTable('site') . ' WHERE timezone IN (' . Common::getSqlStringFieldsArray($timezones) . ') ORDER BY idsite ASC', $timezones); $return = array(); foreach ($ids as $id) { $return[] = $id['idsite']; } return $return; }
protected function insertBatch($tableName, $fields, $values) { $fieldList = '(' . join(',', $fields) . ')'; $params = Common::getSqlStringFieldsArray($values[0]); $sql_base = 'INSERT INTO ' . $tableName . $fieldList . ' VALUES '; $count = 0; $sql_parts = array(); $bind = array(); while (list($k, $row) = each($values)) { $sql_parts[] = '(' . $params . ')'; $bind = array_merge($bind, array_values($row)); ++$count; if ($count == 100) { $sql = $sql_base . implode(",\n", $sql_parts); $this->db->query($sql, $bind); $count = 0; $sql_parts = array(); $bind = array(); } } if (count($sql_parts) > 0) { $sql = $sql_base . implode(",\n", $sql_parts); $this->db->query($sql, $bind); } }
/** * @return mixed */ protected function insertNewVisit($visit) { $fields = implode(", ", array_keys($visit)); $values = Common::getSqlStringFieldsArray($visit); $sql = "INSERT INTO " . Common::prefixTable('log_visit') . " ({$fields}) VALUES ({$values})"; $bind = array_values($visit); Tracker::getDatabase()->query($sql, $bind); $idVisit = Tracker::getDatabase()->lastInsertId(); return $idVisit; }
/** * 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 Piwik\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 function insertIgnoreBatch($tableName, $fields, $values, $ignoreWhenDuplicate = true) { $fieldList = '(' . join(',', $fields) . ')'; $params = Common::getSqlStringFieldsArray($values[0]); $sql = 'INSERT INTO ' . $tableName . $fieldList . ' VALUES (' . $params . ')'; if ($ignoreWhenDuplicate) { foreach ($values as $row) { $this->insertIgnore($sql, $row); } } else { foreach ($values as $row) { $this->db->query($sql, $row); } } }
/** * Records in the DB the association between the visit and this action. * * @param int $idVisit is the ID of the current visit in the DB table log_visit * @param $visitorIdCookie * @param int $idReferrerActionUrl is the ID of the last action done by the current visit. * @param $idReferrerActionName * @param int $timeSpentReferrerAction is the number of seconds since the last action was done. * It is directly related to idReferrerActionUrl. */ public function record($idVisit, $visitorIdCookie, $idReferrerActionUrl, $idReferrerActionName, $timeSpentReferrerAction) { $this->loadIdsFromLogActionTable(); $visitAction = array('idvisit' => $idVisit, 'idsite' => $this->request->getIdSite(), 'idvisitor' => $visitorIdCookie, 'server_time' => Tracker::getDatetimeFromTimestamp($this->request->getCurrentTimestamp()), 'idaction_url' => $this->getIdActionUrl(), 'idaction_url_ref' => $idReferrerActionUrl, 'idaction_name_ref' => $idReferrerActionName, 'time_spent_ref_action' => $timeSpentReferrerAction); // idaction_name is NULLable. we only set it when applicable if ($this->isActionHasActionName()) { $visitAction['idaction_name'] = (int) $this->getIdActionName(); } foreach ($this->actionIdsCached as $field => $idAction) { $visitAction[$field] = $idAction === false ? 0 : $idAction; } $customValue = $this->getCustomFloatValue(); if (!empty($customValue)) { $visitAction[self::DB_COLUMN_CUSTOM_FLOAT] = $customValue; } $customVariables = $this->getCustomVariables(); if (!empty($customVariables)) { Common::printDebug("Page level Custom Variables: "); Common::printDebug($customVariables); } $visitAction = array_merge($visitAction, $customVariables); $fields = implode(", ", array_keys($visitAction)); $bind = array_values($visitAction); $values = Common::getSqlStringFieldsArray($visitAction); $sql = "INSERT INTO " . Common::prefixTable('log_link_visit_action') . " ({$fields}) VALUES ({$values})"; Tracker::getDatabase()->query($sql, $bind); $this->idLinkVisitAction = Tracker::getDatabase()->lastInsertId(); $visitAction['idlink_va'] = $this->idLinkVisitAction; Common::printDebug("Inserted new action:"); Common::printDebug($visitAction); /** * Triggered after successfully persisting a [visit action entity](/guides/persistence-and-the-mysql-backend#visit-actions). * * @param Action $tracker Action The Action tracker instance. * @param array $visitAction The visit action entity that was persisted. Read * [this](/guides/persistence-and-the-mysql-backend#visit-actions) to see what it contains. */ Piwik::postEvent('Tracker.recordAction', array($trackerAction = $this, $visitAction)); }
/** * Executes and returns a query aggregating ecommerce item data (everything stored in the * **log\_conversion\_item** table) and returns a DB statement that can be used to iterate over the result * * <a name="queryEcommerceItems-result-set"></a> * **Result Set** * * Each row of the result set represents an aggregated group of ecommerce items. The following * columns are in each row of the result set: * * - **{@link Piwik\Metrics::INDEX_ECOMMERCE_ITEM_REVENUE}**: The total revenue for the group of items. * - **{@link Piwik\Metrics::INDEX_ECOMMERCE_ITEM_QUANTITY}**: The total number of items in this group. * - **{@link Piwik\Metrics::INDEX_ECOMMERCE_ITEM_PRICE}**: The total price for the group of items. * - **{@link Piwik\Metrics::INDEX_ECOMMERCE_ORDERS}**: The total number of orders this group of items * belongs to. This will be <= to the total number * of items in this group. * - **{@link Piwik\Metrics::INDEX_NB_VISITS}**: The total number of visits that caused these items to be logged. * - **ecommerceType**: Either {@link Piwik\Tracker\GoalManager::IDGOAL_CART} if the items in this group were * abandoned by a visitor, or {@link Piwik\Tracker\GoalManager::IDGOAL_ORDER} if they * were ordered by a visitor. * * **Limitations** * * Segmentation is not yet supported for this aggregation method. * * @param string $dimension One or more **log\_conversion\_item** columns to group aggregated data by. * Eg, `'idaction_sku'` or `'idaction_sku, idaction_category'`. * @return \Zend_Db_Statement A statement object that can be used to iterate through the query's * result set. See [above](#queryEcommerceItems-result-set) to learn more * about what this query selects. * @api */ public function queryEcommerceItems($dimension) { $query = $this->generateQuery(implode(', ', array("log_action.name AS label", sprintf("log_conversion_item.%s AS labelIdAction", $dimension), sprintf('%s AS `%d`', self::getSqlRevenue('SUM(log_conversion_item.quantity * log_conversion_item.price)'), Metrics::INDEX_ECOMMERCE_ITEM_REVENUE), sprintf('%s AS `%d`', self::getSqlRevenue('SUM(log_conversion_item.quantity)'), Metrics::INDEX_ECOMMERCE_ITEM_QUANTITY), sprintf('%s AS `%d`', self::getSqlRevenue('SUM(log_conversion_item.price)'), Metrics::INDEX_ECOMMERCE_ITEM_PRICE), sprintf('COUNT(distinct log_conversion_item.idorder) AS `%d`', Metrics::INDEX_ECOMMERCE_ORDERS), sprintf('COUNT(distinct log_conversion_item.idvisit) AS `%d`', Metrics::INDEX_NB_VISITS), sprintf('CASE log_conversion_item.idorder WHEN \'0\' THEN %d ELSE %d END AS ecommerceType', GoalManager::IDGOAL_CART, GoalManager::IDGOAL_ORDER))), array("log_conversion_item", array("table" => "log_action", "joinOn" => sprintf("log_conversion_item.%s = log_action.idaction", $dimension))), implode(' AND ', array('log_conversion_item.server_time >= ?', 'log_conversion_item.server_time <= ?', 'log_conversion_item.idsite IN (' . Common::getSqlStringFieldsArray($this->sites) . ')', 'log_conversion_item.deleted = 0')), sprintf("ecommerceType, log_conversion_item.%s", $dimension), false); return $this->getDb()->query($query['sql'], $query['bind']); }
/** * 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 Piwik\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 function insertIgnoreBatch($tableName, $fields, $values, $ignoreWhenDuplicate = true) { $fieldList = '(' . join(',', $fields) . ')'; $ignore = $ignoreWhenDuplicate ? 'IGNORE' : ''; $params = Common::getSqlStringFieldsArray($values[0]); foreach ($values as $row) { $query = "INSERT {$ignore} INTO {$tableName} {$fieldList} VALUES ({$params}) \n"; // In some instances $row is an associative array, to make it // compatible with positional parameters array, array_values($row) // is used. $this->db->query($query, array_values($row)); } }
/** * Queries and returns archive data using a set of archive IDs. * * @param array $archiveIds The IDs of the archives to get data from. * @param array $recordNames The names of the data to retrieve (ie, nb_visits, nb_actions, etc.) * @param string $archiveDataType The archive data type (either, 'blob' or 'numeric'). * @param bool $loadAllSubtables Whether to pre-load all subtables * @throws Exception * @return array */ public static function getArchiveData($archiveIds, $recordNames, $archiveDataType, $loadAllSubtables) { // create the SQL to select archive data $inNames = Common::getSqlStringFieldsArray($recordNames); if ($loadAllSubtables) { $name = reset($recordNames); // select blobs w/ name like "$name_[0-9]+" w/o using RLIKE $nameEnd = strlen($name) + 2; $whereNameIs = "(name = ?\n OR (name LIKE ?\n AND SUBSTRING(name, {$nameEnd}, 1) >= '0'\n AND SUBSTRING(name, {$nameEnd}, 1) <= '9') )"; $bind = array($name, $name . '%'); } else { $whereNameIs = "name IN ({$inNames})"; $bind = array_values($recordNames); } $getValuesSql = "SELECT value, name, idsite, date1, date2, ts_archived\n FROM %s\n WHERE idarchive IN (%s)\n AND " . $whereNameIs; // get data from every table we're querying $rows = array(); foreach ($archiveIds as $period => $ids) { if (empty($ids)) { throw new Exception("Unexpected: id archive not found for period '{$period}' '"); } // $period = "2009-01-04,2009-01-04", $date = Date::factory(substr($period, 0, 10)); if ($archiveDataType == 'numeric') { $table = ArchiveTableCreator::getNumericTable($date); } else { $table = ArchiveTableCreator::getBlobTable($date); } $sql = sprintf($getValuesSql, $table, implode(',', $ids)); $dataRows = Db::fetchAll($sql, $bind); foreach ($dataRows as $row) { $rows[] = $row; } } return $rows; }
/** * uses tracker db */ public function insertNew($goal) { $fields = implode(', ', array_keys($goal)); $bindFields = Common::getSqlStringFieldsArray($goal); $sql = 'INSERT IGNORE INTO ' . $this->table . '( ' . $fields . ' ) ' . 'VALUES ( ' . $bindFields . ' ) '; $bind = array_values($goal); $result = $this->db->query($sql, $bind); return $this->db->rowCount($result) > 0; }
/** * Returns all websites with a timezone matching one the specified timezones * * @param array $timezones * @return array * @ignore */ public function getSitesFromTimezones($timezones) { $query = 'SELECT idsite FROM ' . $this->table . ' WHERE timezone IN (' . Common::getSqlStringFieldsArray($timezones) . ') ORDER BY idsite ASC'; $sites = $this->db->fetchAll($query, $timezones); return $sites; }
/** * @param $idSite * @param string $table * @return array */ private function getIdSitesWhereClause($idSite, $table = 'log_visit') { $idSites = array($idSite); Piwik::postEvent('Live.API.getIdSitesString', array(&$idSites)); $idSitesBind = Common::getSqlStringFieldsArray($idSites); $whereClause = $table . ".idsite in ({$idSitesBind}) "; return array($whereClause, $idSites); }
public function deleteArchiveIds($numericTable, $blobTable, $idsToDelete) { $idsToDelete = array_values($idsToDelete); $query = "DELETE FROM %s WHERE idarchive IN (" . Common::getSqlStringFieldsArray($idsToDelete) . ")"; $queryObj = Db::query(sprintf($query, $numericTable), $idsToDelete); $deletedRows = $queryObj->rowCount(); try { $queryObj = Db::query(sprintf($query, $blobTable), $idsToDelete); $deletedRows += $queryObj->rowCount(); } catch (Exception $e) { // Individual blob tables could be missing $this->logger->debug("Unable to delete archive IDs from {blobTable}.", array('blobTable' => $blobTable, 'exception' => $e)); } return $deletedRows; }
/** * Returns the list of all the users * * @param string $userLogins Comma separated list of users to select. If not specified, will return all users * @return array the list of all the users */ public function getUsers($userLogins = '') { Piwik::checkUserHasSomeAdminAccess(); $where = ''; $bind = array(); if (!empty($userLogins)) { $userLogins = explode(',', $userLogins); $where = 'WHERE login IN (' . Common::getSqlStringFieldsArray($userLogins) . ')'; $bind = $userLogins; } $db = Db::get(); $users = $db->fetchAll("SELECT *\n\t\t\t\t\t\t\t\tFROM " . Common::prefixTable("user") . "\n\t\t\t\t\t\t\t\t{$where}\n\t\t\t\t\t\t\t\tORDER BY login ASC", $bind); // Non Super user can only access login & alias if (!Piwik::isUserIsSuperUser()) { foreach ($users as &$user) { $user = array('login' => $user['login'], 'alias' => $user['alias']); } } return $users; }