On Tuesday, February 22, 2005 22:08, jsf wrote:
> This may be more of a PHP question than a MySQL question but here
> goes:
>
> I have a small database with two tables in it. It's a database of
> Botanical Gardens in the US.
>
> Table 1 contains the botanical gardens and has 8 fields:
>
> (I'm abbreviating for brevity and clarity): id, name, address, town,
> state_id, zip, phone, url
>
> Table 2 contains the states referred to in the 'state_id' field and
> itself has 3 fields
>
> id, abbreviation, name
>
> So, 'id' in the 'state' table is linked to the 'gardens' table via
> that table's 'state_id' field.
>
> Now, in pulling data out of the database to display on a web page I
> have all of my connection stuff working and the query of 'select *
> from gardens' along with this php code:
You need join the two tables:
SELECT t1.name, t1.address, t1.town, t2.abbreviation, t1.zip, t1.phone,
t1.url
FROM table1 as t1 INNER JOIN table2 as t2 ON (t1.state_id = t2.id);
Obviously you will have to modify this example since I don't have the exact
info
for your tables (such as names).
You can find more info on joins here:
http://dev.mysql.com/doc/mysql/en/join.html
Also, you can alias the columns as well, to make them easier to reference in
your code ie. t1.name as name
>
> <td align="center"><?php echo $row_Recordset1['botgard_name'];
> ?></td> <td align="center"><?php echo
> $row_Recordset1['botgard_address']; ?></td> <td
> align="center"><?php echo $row_Recordset1['botgard_town'];
> ?></td> <td align="center"><?php echo
> $row_Recordset1['state_id']; ?></td> <td align="center"><?php
> echo $row_Recordset1['botgard_zip']; ?></td> <td
> align="center"><?php echo $row_Recordset1['botgard_phone']; ?></td>
> <td align="center"><?php echo $row_Recordset1['botgard_url']; ?></td>
>
> works fine, pulling records out of the 'gardens' table and displaying
> it on a web page, but, of course, I'm seeing the 'state_id' instead of
> either the 'state_abbreviation' or the 'state_name'.
>
> I am at a complete and total loss as to how to edit my code at this
> point so that, before displaying anything, i can grab either
> 'state_abbr' or 'state_name' from the states table, properly
> associated with the 'state_id' and display the actual state
> abbreviation or state name in my web page.
>
> I'm so close, yet so far. I know if I can be shown once how this
> works, I'll be able to apply the solution again in the future.
>
> Thanks in advance for any help with this.
>
> Sincerely,
>
> Joshua
--
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
