/** * This will run SQL query and return structured data * * @param string $sql * @param mixed $key * @param array $options * @return array */ public function query($sql, $key = null, $options = []) { $result = ['success' => false, 'sql' => $sql, 'error' => [], 'errno' => 0, 'num_rows' => 0, 'affected_rows' => 0, 'rows' => [], 'key' => $key, 'structure' => [], 'time' => null, 'last_insert_id' => 0]; // start time $result['time'] = debug::get_microtime(); // cache id $crypt_object = new crypt(); $cache_id = !empty($options['cache_id']) ? $options['cache_id'] : 'db_query_' . $crypt_object->hash($sql . serialize($key)); // if we cache this query if (!empty($options['cache'])) { $cache_object = new cache($this->connect_options['cache_link']); $cached_result = $cache_object->get($cache_id); if ($cached_result !== false) { return $cached_result; } } // quering regular query first if (empty($options['multi_query'])) { $resource = mysqli_query($this->db_resource, $sql); if (!$resource) { $this->error_overrides($result, mysqli_errno($this->db_resource), mysqli_error($this->db_resource)); } else { $result['affected_rows'] += mysqli_affected_rows($this->db_resource); if ($resource !== true) { $result['num_rows'] += mysqli_num_rows($resource); $result['structure'] = $this->field_structures($resource); } if ($result['num_rows'] > 0) { while ($rows = mysqli_fetch_assoc($resource)) { // casting types $data = []; foreach ($rows as $k => $v) { $data[$k] = $this->process_value_as_per_type($v, $result['structure'][$k]['type']); } // assigning keys if (!empty($key)) { array_key_set_by_key_name($result['rows'], $key, $data); } else { $result['rows'][] = $data; } } } if ($resource !== true) { mysqli_free_result($resource); } $result['success'] = true; } } else { // multi query $resource = mysqli_multi_query($this->db_resource, $sql); if (!$resource) { $result['error'][] = 'Db Link ' . $this->db_link . ': ' . mysqli_error($this->db_resource); $result['errno'] = mysqli_errno($this->db_resource); // we log this error message // todo: process log policy here error_log('Query error: ' . implode(' ', $result['error']) . ' [' . $sql . ']'); } else { $result['affected_rows'] += mysqli_affected_rows($this->db_resource); do { if ($result_multi = mysqli_store_result($this->db_resource)) { if ($result_multi) { $result['num_rows'] += $num_rows = mysqli_num_rows($result_multi); $result['structure'] = $this->field_structures($result_multi); } else { $num_rows = 0; $result['error'][] = 'Db Link ' . $this->db_link . ': Multi query error!'; $result['errno'] = 1; // we log this error message // todo: process log policy here error_log('Query error: ' . implode(' ', $result['error']) . ' [' . $sql . ']'); } if ($num_rows > 0) { while ($rows = mysqli_fetch_assoc($result_multi)) { // casting types $data = []; foreach ($rows as $k => $v) { $data[$k] = $this->process_value_as_per_type($v, $result['structure'][$k]['type']); } // assigning keys if (!empty($key)) { array_key_set_by_key_name($result['rows'], $key, $data); } else { $result['rows'][] = $data; } } } mysqli_free_result($result_multi); } } while (mysqli_more_results($this->db_resource) && mysqli_next_result($this->db_resource)); if (empty($result['error'])) { $result['success'] = true; } } } // last insert id for auto increment columns $result['last_insert_id'] = mysqli_insert_id($this->db_resource); // caching if no error if (!empty($options['cache']) && empty($result['error'])) { $cache_object->set($cache_id, $result, ['tags' => $options['cache_tags'] ?? null]); } // end time $result['time'] = debug::get_microtime() - $result['time']; // if we are debugging if (debug::$debug) { debug::$data['sql'][] = $result; } return $result; }
/** * Primary key * * @param mixed $pk * @param array $data */ function pk($pk, &$data) { if (!is_array($pk)) { $pk = [$pk]; } $result = []; if (!is_array($data)) { $data = []; } foreach ($data as $k => $v) { array_key_set_by_key_name($result, $pk, $v); } $data = $result; }
/** * This will run SQL query and return structured data * * @param string $sql * @param mixed $key * @param array $options * @return array */ public function query($sql, $key = null, $options = []) { $result = ['success' => false, 'sql' => $sql, 'error' => [], 'errno' => 0, 'num_rows' => 0, 'affected_rows' => 0, 'rows' => [], 'key' => $key, 'structure' => [], 'time' => null]; // start time $result['time'] = debug::get_microtime(); // cache id $crypt_object = new crypt(); $cache_id = !empty($options['cache_id']) ? $options['cache_id'] : 'db_query_' . $crypt_object->hash($sql . serialize($key)); // if we cache this query if (!empty($options['cache'])) { $cache_object = new cache($this->connect_options['cache_link']); $cached_result = $cache_object->get($cache_id); if ($cached_result !== false) { return $cached_result; } } // quering $resource = @pg_query($this->db_resource, $sql); $result['status'] = pg_result_status($resource); if (!$resource || $result['status'] > 4) { $last_error = pg_last_error($this->db_resource); if (empty($last_error)) { $errno = 1; $error = 'DB Link ' . $this->db_link . ': ' . 'Unspecified error!'; } else { preg_match("|ERROR:\\s(.*?):|i", $last_error, $matches); $errno = !empty($matches[1]) ? $matches[1] : 1; $error = $last_error; } $this->error_overrides($result, $errno, $error); } else { $result['affected_rows'] = pg_affected_rows($resource); $result['num_rows'] = pg_num_rows($resource); $result['structure'] = $this->field_structures($resource); if ($result['num_rows'] > 0) { while ($rows = pg_fetch_assoc($resource)) { // transforming pg arrays to php arrays and casting types foreach ($rows as $k => $v) { if ($result['structure'][$k]['type'][0] == '_') { $rows[$k] = $this->pg_parse_array($v); } else { if (in_array($result['structure'][$k]['type'], ['int2', 'int4', 'int8'])) { if (!is_null($v)) { $rows[$k] = (int) $v; } } else { if (in_array($result['structure'][$k]['type'], ['real', 'double precision'])) { if (!is_null($v)) { $rows[$k] = (double) $v; } } else { if ($result['structure'][$k]['type'] == 'bytea') { $rows[$k] = pg_unescape_bytea($v); } else { if ($result['structure'][$k]['type'] == 'jsonb') { // we must get json vallues to PHP format $rows[$k] = json_encode(json_decode($v, true)); } } } } } } // assigning keys if (!empty($key)) { array_key_set_by_key_name($result['rows'], $key, $rows); } else { $result['rows'][] = $rows; } } } pg_free_result($resource); $result['success'] = true; } // caching if no error if (!empty($options['cache']) && empty($result['error'])) { $cache_object->set($cache_id, $result, ['tags' => $options['cache_tags'] ?? []]); } // end time $result['time'] = debug::get_microtime() - $result['time']; // if we are debugging if (debug::$debug) { debug::$data['sql'][] = $result; } return $result; }