时间戳

首页 > 解决方案 > 在 Sql Query 中包含动态数组索引

php - 在 Sql Query 中包含动态数组索引

问题描述

我有这样的查询语句-

$result4 = mysqli_query($connection3, "UPDATE Users SET SortedOrder= (((SortedOrder+$currentTokenArray[2])+(SortedOrder+$currentTokenArray[3]))/2) where WaitLessNumber=$partner_id AND TokenNumber in (".implode(',',$sequenceArray).")");

我想要做的是使索引$currentTokenArray[2]和$currentTokenArray[3]动态像$currentTokenArray[$i+2]和$currentTokenArray[$i+3]分别在哪里$i将采用连续值，如 1,2,3,4,5,...

所以在第一次迭代中它会计算

SortedOrder= (((SortedOrder+$currentTokenArray[2])+(SortedOrder+$currentTokenArray[3]))/2)

，在第二次迭代中，它将计算

SortedOrder= (((SortedOrder+$currentTokenArray[3])+(SortedOrder+$currentTokenArray[4]))/2)

等等

有没有办法做到这一点？

注意：连续序列 1,2,3,4,... 也可在上述查询中给出的表的 TokenNumber 列中使用。

标签： phpmysqlmysqli

解决方案

这是实现预期结果的非常粗略的大纲。当我写这篇文章时，我做了很多假设，这可能被证明是错误的。代码可能需要调整。

例如，您必须使用实际的真实数据进行连接。 SortedOrder, currentTokenArray,partner_id和$sequenceArray变量必须来自某个地方。

此外，最大的假设currentTokenArray是全局固定长度的数组。代码必须针对 dynamic 进行调整currentTokenArray。

我试图发表尽可能多的评论。

<?php

// Update. A better error reporting setup

// Turn on displaying errors
ini_set('display_errors', 1);
// Set reporting to everything.
ini_set('error_reporting', E_ALL);
// Make Mysqli trow exceptions instead of warnings.
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

/** I assume SortedOrder has to come from somewhere also, for testing purposes it's set to 0.
    However this is not really correct, SortedOrder can be defined anywhere,
    probably somewhere at global scope, but that is an assumption.
    The functions are not really needed, but I will leave them because it might be easier to understand
    where arrays come from and what sizes they are.
    Also you can always replace the redundant generation with some actual code for getting values
*/
function getToken() {
  $token = array(); // Creating example array for holding the data

  // Filling the array with some data
  for($i=0; $i<10; ++$i) {
    array_push($token, $i); //Here we add an element to the end of the array.
  }
  return $token;
}
function getSequenceArr() {
  $sequenceArray = array(); // Creating example array for holding the data

  // Filling the array with some data
  for($i = 30; $i < 40; ++$i) {
    array_push($sequenceArray, $i); //Here we add an element to the end of the array
  }
  return $sequenceArray;
}

$SortedOrder = 0;

/** In reality this should also be somewhere else. At the include file, or even the config file.
    For testing purposes.
*/
// Connect to database, use actual data for host, username, password and database name
$conn = mysqli_connect("127.0.0.1", "username", "pass", "database");

// I assume token has to come from somewhere
$currentTokenArray = getToken();

// sequenceArray has also to come from somewhere.
$sequenceArray = getSequenceArr();

// We only have to implode array once. Result is a string.
$imploded = implode(", ", $currentTokenArray);
echo "$imploded</br>";

// partner_id comes from somewhere, for example purposes it is set to one. I am assuming partner_id is number
$partner_id = 1;

// Make placeholders for numbers
$in    = str_repeat('?,', count($currentTokenArray) - 1) . '?';
$types = str_repeat('d', count($currentTokenArray));
// This is where param binding comes in. We put ? to places where binding will occur. Explanation sucks, but I suck at good explaining.
// Docs shed more light on it. As it was pointed out we only need to do it once
$query = "UPDATE Users SET SortedOrder = ? where WaitLessNumber = ? AND TokenNumber IN ($in)";

$stmt = $conn->prepare($query); // Preparing the query

// We can't iterate using foreach as we have to stop before the last element in the array.
for($i = 0; $i < count($currentTokenArray)-2; $i++) {
  $token = $currentTokenArray[$i]; // Get current token. I am assuming here token is a number as it is used in calculations.
  $SortedOrder = ((($SortedOrder+$currentTokenArray[$i])+($SortedOrder+$currentTokenArray[$i+1]))/2);
  echo "$SortedOrder</br>";

  // Binding values. Operator ... is arcument unpacking
  $stmt->bind_param('dd'.$types, $SortedOrder, $partner_id, ...$currentTokenArray);
  $stmt->execute();
  printf("%d Rows updated.</br>", $stmt->affected_rows); // Check how much rows were updated;
}

$stmt->close(); // Close statement

$conn->close(); // Close connection

存储的实现SortedOrder也有点糟糕。该值仅在脚本运行时存在。在脚本未运行时保留它但这是最简单的方法。

我想保留SortedOrder两个运行脚本之间的值，或者将其保存到数据库甚至纯文本文件

推荐阅读