Working with multiple queries (syntax)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mikeb
Forum Commoner
Posts: 60
Joined: Tue Jul 08, 2003 4:37 pm
Location: Dublin, Ireland

Working with multiple queries (syntax)

Post by mikeb »

Hi,

I'm pulling matching records from two db's and then using the results for output. I'm wondering what the syntax is for assigning the variables for output? At the moment I've got:

// Do the SQL
$sql="SELECT * FROM towns, county ORDER BY '$sort_order' ASC";
$result=mysql_query($sql,$connection)
or die(mysql_error());

and the do while contains:
...
$town_name=$row['towns.town_name'];
...
$town_county=$row['county.county_name'];
...

My problem is the correct syntax for refering to variables in the different db's (the ... just denotes other code!, the code is stripped down for clarity)

All help appreciated!

Mike
mikeb
Forum Commoner
Posts: 60
Joined: Tue Jul 08, 2003 4:37 pm
Location: Dublin, Ireland

Post by mikeb »

Hi,

should have clarified. for DB's read 'tables'. (no wonder I'm getting my syntax mixed up!)
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

Try a separate query for each table.

Also, if each town row stores an ID for county, you can use a single join query to return rows with towns & their counties together.
mikeb
Forum Commoner
Posts: 60
Joined: Tue Jul 08, 2003 4:37 pm
Location: Dublin, Ireland

Post by mikeb »

Cheers McGruff! I realise that I now need to get myself a MySQL tutorial and do a bit of hard graft. Was working in ColdFusion before PHP so joins were handled differently. I had a look at the available help on devnetworks and realised I don't know enough about the MySQL syntax. :oops:
mikeb
Forum Commoner
Posts: 60
Joined: Tue Jul 08, 2003 4:37 pm
Location: Dublin, Ireland

Post by mikeb »

Hi,

I've got the SQL down and I'm ok with the Joins, there pretty much how I was programming them. My problem is how to refer to the results from the different tables. In other words, I want to display each town in the towns database and alongside the town, the county in which it resides. PHP seems to have a problem with my syntax. I've tried a couple of variations inside a standard while loop to try and set variables to the appropriate variables within the result rows, for instance:

...
$town_name=$row['towns.town_name'];
// To set $town_name equal to town_name from the towns table

$town_county=$row['county.county_name'];
// To set $town_county equal to county from the county table
...

This seemed liked the most appropriate approach, but it's giving me errors. Have I missed something obvious?
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

If you join to tables, example:

Code: Select all

// instead of (even using same field names here)
 "select foo.user, bar.user, foo.id, bar.id ..."
 // you can...
 "select foo.user as fouser, bar.user, foo.id as fooid, bar.id ..."
 // or...
 "select foo.user as whatever, bar.user as else, foo.id as muu, bar.id as other ..."
..to help you remeber your choises. So...

Code: Select all

// Using: "select foo.user as fouser, bar.user, foo.id as fooid, bar.id ..."
while ($row = mysql_fetch_array($resultabovequery)) {
 echo 'foo.user:'. $row['foouser'];
 echo 'bar.user:'. $row['user'];  // not "as anything" = keeps original name
 echo 'foo.id:'. $row['fooid'];
 echo 'bar.id:'. $row['id']; // not "as anything" = keeps original name
}
Hope that helped.
Post Reply