mysql_fetch_array and multitable queries

[thehuby]

I have a query that looks at two tables and pulls all the data back:

SELECT * FROM venues, status WHERE status_id = fk_status_id

I then have a class to hold the information I want. To initialise it I
get the data from the results set as per normal. The issue I have is
that two fields have the field name 'name'. In SQL terms I would refer
to them like 'tablename.colu mnname'. How do I achieve this using a
mysql fetch array?

The code I am using below brings back empty/null items for the
'tablename.colu mnname'.

$row = mysql_fetch_arr ay( $rs );
$vn = new venue();
$vn->params_init( $row['venue_id'],
$row['venues.name'], /*HERE*/
$row['address1'],
$row['address2'],
$row['address3'],
$row['town_city'],
$row['county'],
$row['postcode'],
$row['other_details'],
$row['url'],
$row['status.name'], /*HERE */
$row['copy_of_id']
);

In this instance I am developing the database so I can get round it by
changing the field names, but in the future I may not be able to.

If possible I want to avoid using two queries to get the information I
require.

Regards,

Rick Huby

https://www.e-connected.com

[Marcel]

Re: mysql_fetch_arr ay and multitable queries


"thehuby" <thehuby@gmail. com> schreef in bericht
news:1129212591 .055397.284260@ z14g2000cwz.goo glegroups.com.. .[color=blue]
>I have a query that looks at two tables and pulls all the data back:
>
> SELECT * FROM venues, status WHERE status_id = fk_status_id
>
> I then have a class to hold the information I want. To initialise it I
> get the data from the results set as per normal. The issue I have is
> that two fields have the field name 'name'. In SQL terms I would refer
> to them like 'tablename.colu mnname'. How do I achieve this using a
> mysql fetch array?
>[/color]



$SQL = "SELECT tablename.colum nname AS name1,tablename 2.columnname AS name2
FROM ............";

$RESULT = mysql_query($SQ L);

$RECORD = mysql_fetch_arr ay($RESULT)

$name1 = $RECORD['name1'];
$name2 = $RECORD['name2'];

[ZeldorBlat]

Re: mysql_fetch_arr ay and multitable queries

select * is fairly dangerous -- only because you don't know what you
are (or aren't getting). If you explicitly list out your columns you
can alias them and get to them that way:

SELECT v.venue_id,
v.name venue_name,
v.address1,
...
v.url,
s.name status_name
FROM venues v, status s
WHERE s.status_id = v.fk_status_id

Here I called venue.name venue_name and status.name status_name. So in
your row, you can do $row['venue_name'] and $row['status_name'].

[thehuby]

Re: mysql_fetch_arr ay and multitable queries

Thank you both very much - that really helps me.

Regards,

Rick