PHP比较列值并相应地编辑数据库 [英] PHP Compare column values and edit database accordingly
问题描述
我是PHP的新手，并且在某个时候遇到了困难。我尝试查找解决方案，但是并没有找到我真正需要的。
I am a newbie to PHP and I am stuck at a certain point. I tried looking up a solution for it however, I didn't find exactly what I need.
我的目标是创建一个排行榜，在其中显示值显示降序以及排名和分数。此外，它还应该显示是否存在平局。
My goal is to create a leaderboard, in which the values are displayed in descending order plus the rank and score are displayed. Furthermore, it should also display whether or not a tie is present.
数据库应如下所示：
+---------+------+----------------+-------+------+
| user_id | name | email | score | tied |
+---------+------+----------------+-------+------+
| 1 | SB | sb@gmail.com | 1 | 0 |
+---------+------+----------------+-------+------+
| 2 | AS | as@web.de | 2 | 0 |
+---------+------+----------------+-------+------+
| 3 | BR | br@yahoo.com | 5 | 1 |
+---------+------+----------------+-------+------+
| 4 | PJ | pj@gmail.com | 5 | 1 |
+---------+------+----------------+-------+------+
输出的表应如下所示：
+------+-------------+-------+------+
| rank | participant | score | tied |
+------+-------------+-------+------+
| 1 | BR | 5 | Yes |
+------+-------------+-------+------+
| 2 | PJ | 5 | Yes |
+------+-------------+-------+------+
| 3 | AS | 2 | No |
+------+-------------+-------+------+
| 4 | SB | 1 | No |
+------+-------------+-------+------+
我设法按正确的顺序显示了排名，参与者和分数。但是，我无法按照我希望的方式使用绑列。每当两行（不同）具有相同的值时，它应该更改该值。
I managed to display the rank, participant and the score in the right order. However, I can't bring the tied column to work in the way I want it to. It should change the value, whenever two rows (don't) have the same value.
该表是通过创建< table> ; 和< thead> 在通常的html中，但< tbody> 是
The table is constructed by creating the <table> and the <thead> in usual html but the <tbody> is created by requiring a php file that creates the table content dynamically.
正如可以在createTable代码中看到的那样，我试图通过将当前行与上一行进行比较来解决此问题。一。但是，这种方法最终导致我遇到语法错误。我的想法是，我不能在SQL查询中使用php变量，而且我的知识还不足以自己解决问题。我也没有通过研究找到解决方案。
As one can see in the createTable code I tried to solve this problem by comparing the current row to the previous one. However, this approach only ended in me getting a syntax error. My thought on that would be that I cannot use a php variable in a SQL Query, moreover my knowledge doesn't exceed far enough to fix the problem myself. I didn't find a solution for that by researching as well.
我对这种方法的另一个担心是，它不会根据所有价值检查所有价值。它只会检查上一个，因此不会将第一个与第三个进行比较。
My other concern with that approach would be that it doesn't check all values against all values. It only checks one to the previous one, so it doesn't compare the first one with the third one for example.
我的问题是如何完成任务
My question would be how I could accomplish the task with my approach or, if my approach was completely wrong, how I could come to a solution on another route.
<table class="table table-hover" id="test">
<thead>
<tr>
<th>Rank</th>
<th>Participant</th>
<th>Score</th>
<th>Tied</th>
</tr>
</thead>
<tbody>
<?php
require("./php/createTable.php");
?>
</tbody>
</table>
createTable.php
createTable.php
<?php
// Connection
$conn = new mysqli('localhost', 'root', '', 'ax');
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL Query
$sql = "SELECT * FROM names ORDER BY score DESC";
$result = $conn->query("$sql");
// Initalizing of variables
$count = 1;
$previous = '';
while($row = mysqli_fetch_array($result)) {
$current = $row['score'];
$index = $result['user_id']
if ($current == $previous) {
$update = "UPDATE names SET tied=0 WHERE user_id=$index";
$conn->query($update);
}
$previous = $current;
?>
<tr>
<td>
<?php
echo $count;
$count++;
?>
</td>
<td><?php echo $row['name'];?></td>
<td><?php echo $row['score'];?></td>
<td>
<?php
if ($row['tied'] == 0) {
echo 'No';
} else{
echo 'Yes';
}
?>
</td>
</tr>
<?php
}
?>
推荐答案
所以我自己解决了自己的问题，
So I solved my question by myself, by coming up with a different approach.
首先，我删除了这一部分：
First of all I deleted this part:
$current = $row['score'];
$index = $result['user_id']
if ($current == $previous) {
$update = "UPDATE names SET tied=0 WHERE user_id=$index";
$conn->query($update);
}
$previous = $current;
和上一个变量。
我的新方法将整个表保存在一个新数组中，使用 array_count_values（）方法获取重复的值，继续执行通过 array_keys（）方法获取密钥，并通过SQL查询更新数据库。
My new approach saves the whole table in a new array, gets the duplicate values with the array_count_values() method, proceeds to get the keys with the array_keys() method and updates the database via a SQL Query.
这是代码对于更改的部分：
This is the code for the changed part:
// SQL Query
$sql = "SELECT * FROM names ORDER BY score DESC";
$result = $conn->query("$sql");
$query = "SELECT * FROM names ORDER BY score DESC";
$sol = $conn->query("$query");
// initalizing of variables
$count = 1;
$data = array();
// inputs table into an array
while($rows = mysqli_fetch_array($sol)) {
$data[$rows['user_id']] = $rows['score'];
}
// -- Tied Column Sort --
// counts duplicates
$cnt_array = array_count_values($data);
// sets true (1) or false (0) in helper-array ($dup)
$dup = array();
foreach($cnt_array as $key=>$val){
if($val == 1){
$dup[$key] = 0;
}
else{
$dup[$key] = 1;
}
}
// gets keys of duplicates (array_keys()) and updates database accordingly ($update query)
foreach($dup as $key => $val){
if ($val == 1) {
$temp = array_keys($data, $key);
foreach($temp as $k => $v){
$update = "UPDATE names SET tied=1 WHERE user_id=$v";
$conn->query($update);
}
} else{
$temp = array_keys($data, $k);
foreach($temp as $k => $v){
$update = "UPDATE names SET tied=0 WHERE user_id=$v";
$conn->query($update);
}
}
}
感谢大家的回答和帮助我找到解决方案。
Thank you all for answering and helping me get to the solution.
这篇关于PHP比较列值并相应地编辑数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
