Tips: When you see this prompt, it means that the current article has been migrated from the original emlog blog system. The publication time of the article is too long ago, and the formatting and content may not be complete. Please understand.
Query SQL for Some Common Functions
Date: 2019-4-10 Author: Ajue Views: 1462 Comments: 1

1. Scenario: Query the ranking of a player and the number of votes difference from the previous player#
Method 2 (Query the number of players with fewer votes by removing duplicates and sorting)#
$temp = DB::fetch_first("SELECT distinct total+jewel_vote+forge_vote ,COUNT(*)+1 AS RANK FROM " . DB::table('vote_competition') ." WHERE total+jewel_vote+forge_vote>" . $competition['all'] . " and aid={$aid} ORDER by forge_vote desc");
$rank = $temp['RANK'];
Unable to directly query the number of votes for the previous player
if ($temp['total+jewel_vote+forge_vote']) {
// The number of votes difference from the first player is queried here
$up = $temp['total+jewel_vote+forge_vote']-$competition['all'];
}
Method 1 (Define variables for cumulative sorting)#
$temp = DB::fetch_all("SELECT a.cid,a.total,a.forge_vote,a.jewel_vote,(@rowNum:=@rowNum+1) AS rank FROM pre_vote_competition AS a, (SELECT (@rowNum :=0) ) b WHERE aid={$aid} ORDER BY (a.total+a.forge_vote+a.jewel_vote) DESC ");
foreach ($temp as $key => $value) {
if ($value['cid'] == $cid) {
// Current player's ranking
$rank = $value['rank'];
if ($up) {
$up = $up - ($value['total'] + $value['forge_vote'] + $value['jewel_vote']);
}
break;
}
// Number of votes difference from the previous player
$up = $value['total'] + $value['forge_vote'] + $value['jewel_vote'];
}
2. Scenario: Query the previous and next articles of a post, supporting missing numbers#
$sql = "
SELECT
*
FROM
" . DB::table($this->_table) . "
WHERE
aid IN (
SELECT
CASE
WHEN SIGN(aid - {$id}) > 0 THEN MIN(aid)
WHEN SIGN(aid - {$id}) < 0 THEN MAX(aid)
END AS aid
FROM
pre_exe_article
WHERE
aid <> {$id}
GROUP BY
SIGN(aid - {$id})
ORDER BY
SIGN(aid - {$id})
)
ORDER BY
aid ASC";
return DB::fetch_all($sql);
3. Batch update multiple fields of multiple records in MySQL#
The MySQL update statement is very simple, updating a field of a record, usually written like this:#
UPDATE mytable SET myfield = 'value' WHERE id = '1';
If updating the same field to the same value, MySQL is also very simple, just modify the where clause:#
UPDATE mytable SET myfield = 'value' WHERE id in (1,2,3);
But if updating multiple records to different values, many people may write it like this:#
foreach ($display_order as $id => $ordinal) {
$sql = "UPDATE categories SET display_order = $ordinal WHERE id = $id";
mysql_query($sql);
}
That is, updating one record at a time in a loop.
Updating one record at a time is inefficient and can easily cause blocking.
So, can we update multiple records with one SQL statement?
MySQL does not provide a direct method for batch updating, but it can be achieved with a little trick.
UPDATE mytable SET
myfield = CASE id
WHEN 1 THEN '3'
WHEN 2 THEN '4'
WHEN 3 THEN '5'
END
WHERE id IN (1,2,3)
This SQL statement means updating the display_order field:
- If id=1, then the value of display_order is 3,
- If id=2, then the value of display_order is 4,
- If id=3, then the value of display_order is 5.
The where part here does not affect the execution of the code, but it improves the efficiency of SQL execution.
Ensure that the SQL statement only executes the necessary number of rows to be modified. Here, only 3 rows are updated, and the where clause ensures that only 3 rows are executed.
Updating multiple values#
UPDATE categories SET
display_order = CASE id
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 5
END,
title = CASE id
WHEN 1 THEN 'New Title 1'
WHEN 2 THEN 'New Title 2'
WHEN 3 THEN 'New Title 3'
END
WHERE id IN (1,2,3)
Encapsulate into a PHP function, pass in the corresponding data, and generate SQL with one click#
/**
* Batch update function
* @param $data array Data to be updated, in a two-dimensional array format
* @param array $params array Conditions where the values are the same, in a key-value pair one-dimensional array format
* @param string $table array Table
* @param string $field string Condition where the values are different, default is id
* @return bool|string
*/
function batchUpdate($data, $field, $table ,$params = [])
{
if (!is_array($data) || !$field || !$table || !is_array($params)) {
return false;
}
$updates = parseUpdate($data, $field);
$where = parseParams($params);
// Get all values in the $field column as keys, add single quotes to the values, and save them in the $fields array
// The array_column() function requires PHP 5.5.0+, if it is lower than this version, you can implement it yourself,
// Reference: http://php.net/manual/en/function.array-column.php#118831
$fields = array_column($data, $field);
$fields = implode(',', array_map(function($value) {
return "'".$value."'";
}, $fields));
$sql = sprintf("UPDATE `%s` SET %s WHERE `%s` IN (%s) %s", $table, $updates, $field, $fields, $where);
return $sql;
}
/**
* Convert a two-dimensional array into CASE WHEN THEN batch update conditions
* @param $data array Two-dimensional array
* @param $field string Column name
* @return string SQL statement
*/
function parseUpdate($data, $field)
{
$sql = '';
$keys = array_keys(current($data));
foreach ($keys as $column) {
$sql .= sprintf("`%s` = CASE `%s` \n", $column, $field);
foreach ($data as $line) {
$sql .= sprintf("WHEN '%s' THEN '%s' \n", $line[$field], $line[$column]);
}
$sql .= "END,";
}
return rtrim($sql, ',');
}
/**
* Parse where conditions
* @param $params
* @return array|string
*/
function parseParams($params)
{
$where = [];
foreach ($params as $key => $value) {
$where[] = sprintf("`%s` = '%s'", $key, $value);
}
return $where ? ' AND ' . implode(' AND ', $where) : '';
}
Batch replace strings#
UPDATE `emlog_blog` SET `content` = replace (`content`,'ws2.sinaimg.cn','cdn.sinaimg.cn.52ecy.cn') WHERE `content` LIKE '%ws2.sinaimg.cn%'
User Comments:
Headline News 2 years ago (2019-04-11)
The article is very good, I really like it.