获取此人的分数，并且与属于相同状态的人的分数更好。mysqli php
获取此人的分数，并且与属于相同状态的人的分数更好。mysqli php
提问于 2015-08-27 16:20:10
首先，我想用一个例子来说明我想要完成的任务。然后，我将展示到目前为止编写的代码，但代码只处理问题的第一部分。
在本例中，“name”是Elsa。因此，最初，$name和$state分别等于'Elsa‘和'Washington’
$name = $_POST["name"]; ///Is equal to 'Elsa'
$state = $_POST["state"]; ///Is equal to 'Washington'表的名称是' table‘
+-----+------------+--------+------------+------+
| ids | Player | score | State | Age |
+-----+------------+--------+------------+------+
| 1 | Mike | 1067 | Arizona | 30 |
| 2 | Lit | 970 | Washington | 25 |
| 3 | Sara Leman | 53 | California | 17 |
| 4 | Ted | 782 | Washington | 22 |
| 5 | Kralem | 589 | Florida | 36 |
| 6 | MrDud | 953 | Washington | 17 |
| 7 | Gregs | 752 | Arizona | 38 |
| 8 | Batman | 67 | New York | 16 |
| 9 | Fox | 673 | Washington | 34 |
| 10 | Hans | 500 | Washington | 23 |
| 11 | Elsa | 552 | Washington | 28 |
| 12 | Britta | 400 | Washington | 31 |
+-----+------------+--------+------------+------+//1.选择Elsa所在州的每个人，即华盛顿
+-----+------------+--------+------------+
| ids | Player | score | State |
+-----+------------+--------+------------+
| 2 | Lit | 970 | Washington |
| 4 | Ted | 782 | Washington |
| 6 | MrDud | 953 | Washington |
| 9 | Fox | 673 | Washington |
| 10 | Hans | 500 | Washington |
| 11 | Elsa | 552 | Washington |
| 12 | Britta | 400 | Washington |
+-----+------------+--------+------------+//2.按分数排序
+-----+------------+--------+
| ids | Player | score |
+-----+------------+--------+
| 2 | Lit | 970 |
| 6 | MrDud | 953 |
| 4 | Ted | 782 |
| 9 | Fox | 673 |
| 11 | Elsa | 552 |
| 10 | Hans | 500 |
| 12 | Britta | 400 |
+-----+------------+--------+/这是我的代码当前带给我的距离
/这是我需要解决的最后一个棘手的部分//3.选择Elsa分数或更高的球员，这意味着布瑞塔消失了
+-----+------------+--------+
| ids | Player | score |
+-----+------------+--------+
| 2 | Lit | 970 |
| 6 | MrDud | 953 |
| 4 | Ted | 782 |
| 9 | Fox | 673 |
| 11 | Elsa | 552 |
+-----+------------+--------+//完成。我真正感兴趣的是下面的表格
+------------+--------+
| Player | score |
+------------+--------+
| Lit | 970 |
| MrDud | 953 |
| Ted | 782 |
| Fox | 673 |
| Elsa | 552 |
+------------+--------+以下是代码
<?php
$con=mysqli_connect("mysql_host","mysql_user","mysql_password","mysql_database");
$name = $_POST["name"];
$state = $_POST["state"];
$statement = mysqli_prepare($con, "SELECT * FROM table WHERE state = ? ORDER BY score");
mysqli_stmt_bind_param($statement, "s", $state);
mysqli_stmt_execute($statement);
mysqli_stmt_store_result($statement);
mysqli_stmt_bind_result($statement, $name, $score);
$result_array=array();
while(mysqli_stmt_fetch($statement)){
//Here I want to create the array such that I can turn it into Json format below
}
echo json_encode($result_array);
mysqli_close($con);
exit;
?>回答 2
Stack Overflow用户
发布于 2015-08-27 16:32:43
如果您想要得分低于Elsa的用户，则可以在查询中对其进行排序，如下所示
SELECT * FROM table WHERE state = ? AND score >= (SELECT score FROM table WHERE name = 'Elsa') ORDER BY score当然，我建议您在表单中发送她的ID，而不是使用名称来标识Elsa。
Stack Overflow用户
发布于 2015-08-27 16:44:32
select t1.Player,t1.score
from table t1,
(select state, score from table where state = ? and player = ? ) t
where t1.state=t.state and t1.score >= t.score
order by score desc页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/32244182
复制相关文章
相似问题
腾讯云开发者
