php - 如何选择两个表mysql使用php

Question

例如:我有两个表:表 1:

 ________________________________
| ID | name          | born      |
|--------------------------------|
| 1  | David         | 1987      |
| 2  | Michale       | 1998      |
| 3  | Mary          | 1995      |
 --------------------------------

表2

 _________________________________
| userID | Key        | value     |
 ---------------------------------
| 2      | D of Birth | 20-07     |
| 2      | sex        | M         |
| 3      | D of Birth | 12-09     |
| 3      | sex        | FM        |
| 1      | D of Birth | 20-01     |
| 1      | sex        | M         |
 ---------------------------------

我如何选择并添加到数组中:

Array 
  [0]
     [ID] = 1
     [name] = David
     [born] = 1987
     [D o Birth] = 20-01
     [sex] = M
  [1]
     [ID] = 2
     [name] = Michale
     [born] = 1998
     [D o Birth] = 20-07
     [sex] = M
  ...

我不知道他们为什么不用一张 table 来简单？两个表查询速度更快？非常感谢!

Answer 1

你需要一个 crosstab .使用交叉表，行可以变成列，反之亦然。

不幸的是，MySQL 不支持交叉表。所以你需要创建一个自定义查询:

SELECT user.*, dofbirth.value AS `D of Birth`, sex.value AS sex
  FROM user
    LEFT JOIN userprop AS dofbirth
      ON dofbirth.key = 'D of Birth' AND user.id = dofbirth.userID
    LEFT JOIN userprop AS sex
      ON sex.key = 'sex' AND user.id = `sex`.userID

需要提前知道列，所以需要提前知道userprop表的key。

---

您可以通过查询获取所有键

SELECT DISTINCT key FROM userprop

您需要在 PHP 中遍历键并构建交叉表查询。

$result = $db->query("SELECT DISTINCT key FROM userprop");

$cols = ["user.*"];
$from = "user";

while (list($key) = $result->fetch_row()) {
  $table = strtolower(preg_replace('/\W/', $key));

  $cols[] = "{$table}.value AS `" . str_replace('`', '', $key) . "`";
  $from .= " LEFT JOIN userprop AS `{$table}` ON `{$table}`.key = \"" . $db->real_escape_string($key) . "\" AND `{$table}`.userID = user.id"; 
}

$query = "SELECT " . join(', ', $cols) . " FROM " . $from;
...