mysql,PDO -排序和筛选结果的行数 - mysql & PDO - row number of sorted and filter results

mysql & PDO - row number of sorted and filter results

本文关键字：结果筛选 PDO 排序 mysql | 更新日期: 2023-09-27

我正在使用PDO和PHP与mysql数据库工作。我有一个在不同的建筑物工会成员的列表，我试图使它DB查询获得成员的资历号码。

数据库设置如下:

memberID
memberName
memberBuildingNumber
memberHireDate (2000-05-01 as an example)
memberPIN (Used in case of ties between hire date, lower pin is higher seniority)

我知道SQL将成员按正确的顺序排列:

SELECT * FROM  `members` ORDER BY  `members`.`memberHireDate` ASC, `members`.`memberPIN` ASC WHERE building = buildingB

(Yes I know my where statement is wrong)

我不确定如何去计数行来获得成员的位置。

例如:

1457 John Smith BuildingB 2000-05-01 4745
1875 Jane Smith BuildingB 2000-05-01 4010
1407 Johnny Good BuildingB 2010-08-01 4745

所以在上面相同的情况下，在我运行第二个查询或任何我必须做的事情之后，如果我正在寻找Jane的资历号码，我会返回1

我实际做的是在DB的成员表中添加另一列。它包含资历编号，并由脚本设置。如果有人对未来感兴趣，这里是脚本。

$dbh = new PDO('mysql:host=localhost;dbname=DBNAME', $db_user, $db_password);
$stmt = $dbh->prepare('SELECT memberBuildingNumber FROM BuildingCodes WHERE BuildingCode > 0');
$stmt->execute();
foreach ($stmt->fetchAll() as $results) {
    $memberBuildingNumber = $results['memberBuildingNumber'];
$dbh = new PDO('mysql:host=localhost;dbname=DBNAME', $db_user, $db_password);
$stmt = $dbh->prepare('SELECT * FROM members WHERE memberBuildingNumber=? ORDER BY memberHireDate ASC, memberPIN ASC');
$stmt->execute(array($memberBuildingNumber));
$seniority = 1;
$memberSeniorityLastUpdate = date("Y-m-d H:i:s");
foreach ($stmt->fetchAll() as $results) {
    $dbh = new PDO('mysql:host=localhost;dbname=DBNAME', $db_user, $db_password);
    $stmt = $dbh->prepare('UPDATE members SET memberSeniority=?, memberSeniorityLastUpdate=? WHERE memberID=?');
    $stmt->execute(array($seniority, $memberSeniorityLastUpdate, $results['memberID']));
    $seniority++;
   }
}