how to select two table mysql use php -

ex: have 2 table: table 1:

 ________________________________ | id | name          | born      | |--------------------------------| | 1  | david         | 1987      | | 2  | michale       | 1998      | | 3  | mary          | 1995      |  -------------------------------- 

table 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         |  --------------------------------- 

how can select , add array as:

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

i don't know why not use 1 table simple? 2 table faster query? many thanks!

you need crosstab. crosstab, rows can become columns , visa versa.

unfortunately mysql doesn't have crosstab support. need create custom query:

select user.*, dofbirth.value `d of birth`, sex.value sex   user     left join userprop dofbirth       on dofbirth.key = 'd of birth' , user.id = dofbirth.userid     left join userprop sex       on sex.key = 'sex' , user.id = `sex`.userid 

you need know columns in advance, need know keys of userprop table in advance.

---

you keys query

select distinct key userprop 

than need loop though keys in php , build crosstab query.

$result = $db->query("select distinct key userprop");  $cols = ["user.*"]; $from = "user";  while (list($key) = $result->fetch_row()) {   $table = strtolower(preg_replace('/\w/', $key));    $cols[] = "{$table}.value `" . str_replace('`', '', $key) . "`";   $from .= " left join userprop `{$table}` on `{$table}`.key = \"" . $db->real_escape_string($key) . "\" , `{$table}`.userid = user.id";  }  $query = "select " . join(', ', $cols) . " " . $from; ...