MySql从查询结果确定行偏移量 [英] MySql Determine A Row Offset From a Query Result
问题描述
需要通过使用排序表中的列条目的值来确定行的偏移量的数值.
need to determine the numerical value of the offset of a row by using the value of a column entry in a sorted table.
鉴于，我知道感兴趣行中的唯一用户名($ username).
例如$username = "SnowWhite";
Given, I know the unique username ($username) in the row of interest.
e.g. $username = "SnowWhite";
给出:可以确保$ username在数据库中并且将出现在随后的初始查询的结果中.这是我的初始查询(有效):
Given: it is guaranteed that the $username is in the database and will be in the results of my initial query that follows. Here is my initial query (that works):
$query = "SELECT ALL username,ticket_number, queue_time FROM members WHERE queue_time !=0 ORDER BY queue_time";
$result = mysql_query($query);
有效的方法是对$result数组进行暴力破解php搜索$username的值.
What is working is a brute force php search of the $result array for the value of $username.
我想用一个查询(或上述查询的重铸)替换php搜索逻辑，该查询将用户名列值$username的行偏移量的整数值存储在$result中，例如$offset.
I would like to replace the php search logic with a query (or a recasting of the above query) that stores the integer value of the row offset within $result for the username column's value of $username into a variable, for example, $offset.
例如，如果SnowWhite在$result的第三行中，我希望使用$offset == 2(假设行索引偏移量从0开始).
For example, if SnowWhite is in the third row of $result I expect the $offset == 2 (assuming a row index offset starting from 0).
最终(可行)，我将所选行的'ticket_number'值更新为== $offset+1.通过:
Ultimately (this works) I will update the 'ticket_number' value of the selected row to be == $offset+1. by:
$query="update members set ticket_number='$offset+1' where username='$username'";
mysql_query($query);
当前工作逻辑
$query = "SELECT ALL username,ticket_number, queue_time FROM members WHERE queue_time !=0 ORDER BY queue_time";
$result = mysql_query($query);
$i = 0;
while ($row = mysql_fetch_array($result)) {
if ($row[username] == $userinfo) //-- compare usernames
{
++$i;
$query="update members set ticket_number='$i+1' where username='$userinfo'";
mysql_query($query);
break;
}
++$i;
}
mysql_free_result($result);
用户名ticket_number queue_time Doc 0 0 1st 3不在排序结果中 脾气暴躁0 0 快乐0 0 困1111以下为ni排序结果 害羞2222 打喷嚏0 333 白雪公主 ??? 444这是当前用户(分配??? = 4) 涂料0 555 邪恶女王0 666
username ticket_number queue_time Doc 0 0 The 1st 3 not in sorted result Grumpy 0 0 Happy 0 0 Sleepy 1 111 The following are ni sorted result Bashful 2 222 Sneezy 0 333 SnowWhite ??? 444 This is the current user (assign ???=4) Dopey 0 555 EvilQueen 0 666
推荐答案
以您的表格示例为例:
username ticket_number queue_time
Doc 0 0 The 1st 3 not in sorted result
Grumpy 0 0
Happy 0 0
Sleepy 1 111 The following are in sorted result
Bashful 2 222
Sneezy 0 333
SnowWhite ??? 444 This is the current user (assign ???=4)
Dopey 0 555
EvilQueen 0 666
如何将值4分配给用户名SnowWhite的ticket_number列，其中4是在初始行的排序选择中该行的排名:
how to assign the value 4 to the column ticket_number of the username SnowWhite, 4 being the rank of the row in a sorted selection of the initial rows:
首先获得排序结果:
$result = mysql_query("SELECT * FROM table WHERE [here the condition for your sorted array]");
// don't forget to remove the []. they don't go there.
while ($row = mysql_fetch_assoc($result))
{$array[] = $row;}
这应该使您像:
[0] [username] [Sleepy]
[ticket_number] [1]
[queue_time] [111]
[1] [username] [Bashful]
[ticket_number] [2]
[queue_time] [222]
[2] [username] [Sneezy]
[ticket_number] [0]
[queue_time] [333]
[3] [username] [SnowWhite]
[ticket_number] [NULL]
[queue_time] [444]
然后遍历数组
foreach ($array as $number => $row)
{if ($row[username] == $userinfo)
{$result = mysql_query('UPDATE table SET ticket_numer=' . ($number+1) . ' WHERE username=' . $userinfo);}}
与用户名SnowWhite对应的行将获得($ number +1)(3 +1)作为ticket_number
the row corresponding to username SnowWhite will get ($number + 1) (3 + 1) as ticket_number
这篇关于MySql从查询结果确定行偏移量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
