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
Working with multiple queries (syntax)
Moderator: General Moderators
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?
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?
If you join to tables, example:
..to help you remeber your choises. So...
Hope that helped.
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 ..."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
}