php - 要列出的其他表格

Question

我的上一个问题已得到解答，是从两个表构建列表。

SELECT CONCAT( P.LastName, ', ', P.FirstName ) AS Name, MP.MembershipID, P.PersonID
FROM `person` P
INNER JOIN `membershipperson` MP ON MP.PersonID = P.PersonID
ORDER BY MP.MembershipID DESC 

现在我需要帮助向列表添加其他信息。

1. 我需要一个显示 LastName、FirstName、PersonID、MemberID、LastActiveYear、MemberSince、Approved、LockedOut 的列表。

2. 如果 LastActiveYear < 2013 年，sql 代码将 Approved 位设置为 0。我可能想为此使用 php mysqli 页面，以便我可以更改按要求年份。

以下是表格示例；

person  
PersonID      LastName    FirstName   
----------+------------+------------+---------
1212            Barr       Foo            
888             To         Go              
1415            From       Is             


Membershipperson 
MemberShipID      PersonID
-------------+------------  
 2250          1212
 1150           888
 3500          1415

users  
PersonID      Username    IsApproved   LockedOut
----------+-------------+-----------+-----------
1212           2250          1            1
888            1150          1            0
1415           3500          0            1

membership
MemberShipId             LastActiveYear
---------------+------------------------
    ####       +          2012

更正并添加了表格。

Answer 1

假设您将使用 php mysqli

//Obviously substitute for you mysql credentials
$mysqli_link = mysqli_connect("localhost","root","pas") or die(mysqli_error($mysqli_link));
//again choose the correct right db
mysqli_select_db($mysqli_link,"database") or die (mysqli_error($mysqli_link));


$query = "SELECT CONCAT( P.LastName, ', ', P.FirstName ) AS Name, MP.MembershipID, ms.LastActiveYear, P.PersonID as personId
FROM `person` P
INNER JOIN `membershipperson` MP ON MP.PersonID = P.PersonID
LEFT JOIN `user` ON P.PersonID=users.PersonID
LEFT JOIN `membership` ms ON MP.MemberShipID=ms.MemberShipID
WHERE users.Approved=1
AND ms.LastActiveYear < 2013
ORDER BY MP.MembershipID DESC";

$result = mysqli_query($mysqli_link, $query);

//To see results
$data = array();
$ids = array();
while ($row = mysqli_fetch_assoc($result)) {
    $data[] = $row;
    $ids[] = $row['personId'];

}

echo'<pre>';
print_r($data);
die('</pre>'); // this will stop the rest of the page from loading

//loop through the results and store the `PersonID`'s in an array called `$ids` (or whatever you want to call it)

$ids = implode(',', $ids);
$ids = substr($ids, 0, -1);
$query = "UPDATE users SET Approved=0 WHERE PersonID IN ($ids)";
mysqli_query($mysqli_link, $query);

希望这有帮助! :)