Query sql for some common functions

1. Scenario: Query a player’s ranking and how many votes away from the previous one Method 2 (remove reordering and query the number less than yourself)

$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 votes of the previous contestant if ($temp[’total+jewel_vote+forge_vote’]) { //What is queried here is the number of votes different from the first contestant. $up = $temp[’total+jewel_vote+forge_vote’]-$competition[‘all’]; }

Method 1 (defining 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 own ranking $rank = $value[‘rank’]; if ($up) { $up = $up - ($value[’total’] + $value[‘forge_vote’] + $value[‘jewel_vote’]); } break; } //The difference in votes from the previous contestant $up = $value[’total’] + $value[‘forge_vote’] + $value[‘jewel_vote’]; }

2. Scenario: Query the previous and next articles of an article, supporting account break

$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 MySQL records The mysql update statement is very simple. It updates a certain field of a piece of data. It is usually written like this:

UPDATE mytable SET myfield = ‘value’ WHERE id = ‘1’;

If you update the same field to the same value, mysql is also very simple, just modify where:

UPDATE mytable SET myfield = ‘value’ WHERE id in (1,2,3);

If you update multiple pieces of data to different values, many people may write like this:

foreach ($display_order as $id => $ordinal) { $sql = “UPDATE categories SET display_order = $ordinal WHERE id = $id”; mysql_query($sql); }

That is, looping through the update records one by one.
One record is updated once, which has poor performance and can easily cause blocking.

So can batch updates be implemented with one SQL statement?
MySQL does not provide a direct method to implement batch updates, but it can be achieved with a few tricks.

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)

The meaning of this sql is to update the display_order field:

If id=1, the value of display_order is 3,
If id=2, the value of display_order is 4,
If id=3, the value of display_order is 5.

That is, conditional statements are written together.

The where part here does not affect the execution of the code, but will improve the efficiency of sql execution.

Ensure that the sql statement only executes the number of rows that need to be modified. Only 3 rows of data are updated here, and the where clause ensures that only 3 rows of data are executed.

UPDATE MULTI VALUES

UPDATE categories SET display_order = CASE id WHEN 1 THEN 3 WHEN 2 THEN 4 WHEN 3 THEN 5 END, title=CASEid 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 it 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, two-dimensional array format * @param array $params array The condition that the values are the same, the one-dimensional array corresponding to the key value * @param string $table array table * @param string $field string conditions with different values, the 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 the values of the $field column with the key name, add single quotes around the value, and save it in the $fields array
//The array\_column() function requires PHP5.5.0+. If it is smaller than this version, you can implement it yourself.
// Reference address: http://php.net/manual/zh/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 two-dimensional array into batch update conditions of CASE WHEN THEN * @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 condition * @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%’