Place multiple similar fields in multi-dimensional array - php mysql

Question

Hi,

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;

Answer 2

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.

Answer 3

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:

<?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().

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

---

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
)