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

Question

I want to execute an SQL query like:

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:

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

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

Thank you, Alec

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

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)

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.

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;