在多维数组中放置多个相似的字段 - php mysql

I want to execute an SQL query like:

我想执行一个SQL查询,如:

select 'tb1'.'f1','tb1'.'f2','tb2'.'f1' from 'tb1','tb2';

Now the problem is that i want to put it into an array in PHP like:

现在问题是我想把它放在PHP的数组中,如:

$result['tb1']['f1'], $result['tb1']['f2'], $result['tb2']['f1']...

Any idea how to achieve the above? Afaik there is no function which does the above. I was wondering the best simple way to do it. I do not want to use a query like "select .. as .. " unless necessary.

任何想法如何实现上述? Afaik没有上述功能。我想知道最好的简单方法。除非必要,否则我不想使用像“select .. as ..”之类的查询。

I do not know in advance what the fields will be, so I cannot assign them manually as suggest by the answer by benlumley.

我事先并不知道这些字段是什么,所以我不能手动分配它们作为benlumley答案的建议。

Thank you, Alec

谢谢你,亚力克

3 个解决方案

#1

You'll need to select the data as you are already doing, and then loop over it getting it into the format required, and because the fields have the same names, its easiest to use aliases or they'll just overwrite each other in the returned data (but you could use mysql_fetch_row instead, which returns a numerically indexed array).

你需要像你现在一样选择数据,然后循环它,使它变成所需的格式,并且因为字段具有相同的名称,它最容易使用别名,或者它们只会在返回数据(但你可以使用mysql_fetch_row,它返回一个数字索引数组)。

For example:

$sql = "select tb1.f1 as tb1f1,tb1.f2 as tb1f2,tb2.f1 as tb2f1 from tb1,tb2";
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result)) {
    $result['t1']['f1']=$row['tb1f1'];
    $result['t1']['f2']=$row['tb1f2'];
    $result['t2']['f1']=$row['tb2f1'];
}

(The quoting was wrong in your sql as well)

(你的sql中的引用也是错误的)

That won't handle multiple rows either, but your question sort of implies that you are only ever expecting one row?

这也不会处理多行,但你的问题有点暗示你只是期待一行?

WIthout aliases:

$sql = "select tb1.f1,tb1.f2,tb2.f1 from tb1,tb2";
$result = mysql_query($sql);
while ($row = mysql_fetch_row($result)) {
    $result['t1']['f1']=$row[0];
    $result['t1']['f2']=$row[1];
    $result['t2']['f1']=$row[2];
}

I prefer the first version unless you have a good reason to use the second, as its less likely to result in errors if you ever change the sql or add fields etc.

我更喜欢第一个版本,除非您有充分的理由使用第二个版本,因为如果您更改sql或添加字段等,它不太可能导致错误。

EDIT:

Taking the meta data idea from the response below ....

从下面的响应中获取元数据的想法....

<?php
mysql_connect('localhost', 'username', 'password');
mysql_select_db('dbname');
$result = mysql_query('select tb1.f1, tb1.f2, tb2.f1 from tb1, tb2');
$meta = array();
for ($i = 0; $i < mysql_num_fields($result); ++$i) {
  $meta[$i] = mysql_fetch_field($result, $i);
}
while ($row = mysql_fetch_row($result)) {
   foreach($row as $key=>$value) {
     $out[$meta[$key]->table][$meta[$key]->name]=$value;
   }
}

seems to do exactly what you are after - although you can still only get one row at a time.

似乎完全符合你的要求 - 尽管你仍然只能一次获得一行。

Easily updated to store multiple rows with another dimension on the array:

轻松更新以在阵列上存储具有其他维度的多个行:

Change:

$out[$meta[$key]->table][$meta[$key]->name]=$value;

To:

$out[][$meta[$key]->table][$meta[$key]->name]=$value;

#2

Since you say you can't specify column aliases, and you can't know the fields of the query beforehand, I'd suggest a solution using mysql_fetch_field() to get metadata information:

既然你说你不能指定列别名,并且你不能事先知道查询的字段,我建议使用mysql_fetch_field()来获取元数据信息的解决方案:

<?php
mysql_connect('localhost', 'username', 'password');
mysql_select_db('dbname');
$result = mysql_query('select tb1.f1, tb1.f2, tb2.f1 from tb1, tb2');
for ($i = 0; $i < mysql_num_fields($result); ++$i) {
  $meta = mysql_fetch_field($result, $i);
  print_r($meta);
}

You can extract from this metadata information the table name and column name, even when there are multiple columns of the same name in the query.

即使查询中有多个同名列,您也可以从此元数据信息中提取表名和列名。

PHP's ext/mysqli supports a similar function mysqli_stmt::result_metadata(), but you said you can't know the number of fields in the query beforehand, which makes it awkward to use mysqli_stmt::bind_result().

PHP的ext / mysqli支持类似的函数mysqli_stmt :: result_metadata(),但是你说你事先无法知道查询中的字段数,这使得使用mysqli_stmt :: bind_result()变得很尴尬。

PDO_mysql doesn't seem to support result set metadata at this time.

PDO_mysql目前似乎不支持结果集元数据。

The output from the above script is below.

上面脚本的输出如下。

stdClass Object
(
    [name] => f1
    [table] => tb1
    [def] => 
    [max_length] => 1
    [not_null] => 0
    [primary_key] => 0
    [multiple_key] => 0
    [unique_key] => 0
    [numeric] => 1
    [blob] => 0
    [type] => int
    [unsigned] => 0
    [zerofill] => 0
)
stdClass Object
(
    [name] => f2
    [table] => tb1
    [def] => 
    [max_length] => 1
    [not_null] => 0
    [primary_key] => 0
    [multiple_key] => 0
    [unique_key] => 0
    [numeric] => 1
    [blob] => 0
    [type] => int
    [unsigned] => 0
    [zerofill] => 0
)
stdClass Object
(
    [name] => f1
    [table] => tb2
    [def] => 
    [max_length] => 1
    [not_null] => 0
    [primary_key] => 0
    [multiple_key] => 0
    [unique_key] => 0
    [numeric] => 1
    [blob] => 0
    [type] => int
    [unsigned] => 0
    [zerofill] => 0
)

#3

Prefixing field names with short version of table name is a good way to achieve it without the need to create aliases in select. Of course you don't get the exact structure that you described but every field has its unique named index in result array. For example, let's assume you have table users, and the user has a name, then call this field usr_name.

使用短版本的表名前缀字段名称是实现它的好方法,而无需在select中创建别名。当然,您没有得到您描述的确切结构,但每个字段在结果数组中都有其唯一的命名索引。例如,假设您有表用户,并且用户有一个名称,然后调用此字段usr_name。

#1