Help cutting down on queries / merging multiple queries into one

[Ben_Johnson]

Sorry for the long post, but any help you can give me would be greatly appreciated. Thank you.....

I need to reduce to as little queries as possible, for obvious reasons. I use ADODB to access my databases, so I'm not very familiar with the given mysql php functions, so I'll just write out the queries and explain how they will be used.

So here is an elementary query example:

Query #1, this will only return 1 record, an associative array, with the db field as the key
$record = select * from table_a where id=1

Query #2, this will return multiple records as an array
$record['whatever'] = select * from table_b where cross_reference=$record['id']

So I would end up what a single array like:
$record['field1_from_table_a'] = value;
$record['field2_from_table_a'] = value;
$record['field3_from_table_a'] = value;
$record['field4_from_table_a'] = value;
etc.
$record['whatever'] = array(0 => array('field1'=>'value','field2'=>'value',etc.), 1=>array('field1'=>'value','field2'=>'value',etc.), etc.);

Any way to do this as one query? The following query doesnt work but it may help clarify what I'm trying to ask.

select a.*, b.* as whatever from table_a a, table_b b where a.id=b.cross_reference

Logically thats what I'm trying to do.

Thanks again!

[DrDoc]

Yes, you can join the tables for your query :)

SELECT b.* FROM table_a a INNER JOIN table_b b ON a.id = b.cross_reference WHERE a.id=1

[Ben_Johnson]

Right, but let's say table_a had a field called name and so did table_b, would it be possible to store table_a's fields in a different array key than table_b?

[coopster]

Yes. There is a fancy schmancy SQL term for that -- ambiguity. The column names are ambiguous. You use the table name as a qualifier and you can use an ALIAS for the column names.

SELECT 
 a.column_name AS column_nameA, 
 b.column_name AS colimn_nameB, 
 ...

[Ben_Johnson]

Does this still apply to a.*, a.field1 as alias?

Is that acceptable?