如何选择两个表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
  ...

我不知道他们为什么不用一张桌子做简单的事情？两个表是更快的查询？非常感谢！

Answer 1

你需要一个交叉表。通过交叉表，行可以变成列，而visa则相反。

不幸的是，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表的键。

您可以通过查询获得所有密钥。

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;
...