Page 1 of 1
Working with multiple queries (syntax)
Posted: Sat Aug 23, 2003 5:39 am
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
Posted: Sat Aug 23, 2003 7:11 am
by mikeb
Hi,
should have clarified. for DB's read 'tables'. (no wonder I'm getting my syntax mixed up!)
Posted: Sat Aug 23, 2003 11:51 am
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.
Posted: Sun Aug 24, 2003 11:06 am
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.

Posted: Mon Aug 25, 2003 1:21 pm
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?
Posted: Mon Aug 25, 2003 9:05 pm
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.