I have a db table (t1) with a list of couteries, provinces and regions. When a user saves details to a another db table (t2) it only saves the region, is there a way I can retrieve the province and country without adding it to (t2)
I could do
$sql_t2 = mysql_fetch_assoc("SELECT * FROM table2"));
$sql_reg = mysql_fetch_assoc(mysql_query("SELECT * FROM table1 WHERE name = " . $sql_2));
$sql_prov.....
$sql_country....But this seems a bit long winded
well your solution won’t work anyway because of how you have it coded, but I get the point.
What you might consider is doing a single query such as
SELECT a., b. FROM table1 a, table2 b WHERE a.region = b.region AND OtherConditions=OtherValues
Just link the tables with the common data. That’s actually what a relational database is designed to do.
I think i’ve been a bit vague with my question so i’ll ellaberate a bit, here is what i’m looking to do
DATABASE TABLE 1
Field 1 Id
Field 2 Name
Field 3 Address
Field 4 table2Id
DATEBASE 2
FIELD 1 ID
FIELD 2 PARENT (int)
FIELD 3 NAME
FIELD 4 CHILDREN (int)
[code]<?
$result = mysql_query(“SELECT * FROM TABLE 1”) or die(mysql_error());
while($row=mysql_fetch_assoc($result)) {
echo $row…
echo $row…
$result2 = mysql_query("SELECT * FROM TABLE 2 WHERE id = " . $row[‘FIELD 4’]) or die(mysql_error());
$rT2 = mysql_fetch_assoc($result2);
if(isset($rT2[‘PARENT’])) {
$rt3 = mysql_query("SELECT * FROM TABLE 2 WHERE id = " . $rT2[‘PARENT’]) or die(mysql_error());
foreach($rt3[‘parent’] as $name) {
print_r $name // I WANT IT GIVE SOMETHING LIKE TOWN, COUNTY, COUNTRY
}
}
}
?>[/code]
I know the above won’t work, I’m not looking for the answer just to be pointed in the right direction
Again I think you need to link your query…
SELECT a., b.
FROM table1 a, table2 b
WHERE a.table2id = b.id
The SELECT will grab EVERYTHING form table identified as a and everything from table identified by b.
The FROM says that table1 will be identified as a and table2 will be identified as b.
The WHERE says grab all rows where the a.table2id equals the b.id field. This is the LINKING field.
You could also look into joins (inner or outer) to do this or even subqueries, but they get more complex.
Now i’m getting somewhere
$parent = $row['cat'];
while(isset($parent)) {
$parentExists = mysql_query ("SELECT * FROM table1 WHERE id = $parent ");
if (mysql_num_rows($parentExists)>0)
{
$parentID = mysql_fetch_assoc ($parentExists);
$parent = $parentID['parent'];
echo $parentID['name'] . ', ';
}
else
{
unset ($parent);
}
}The only trouble is its bringing it in reverse, Country, State, Region instead of Region, State, Country
As per my previous post here is what I now have
$getRes = mysql_query('SELECT * FROM teble1') or die(mysql_error());
while($res=mysql_fetch_assoc($getRes)) {
$parent = $res['name'];
while(isset($parent)) {
$parentExists = mysql_query ("SELECT * FROM table2 WHERE id = $parent");
if (mysql_num_rows($parentExists)>0)
{
$parentID = mysql_fetch_array ($parentExists);
$parent = $parentID['parent'];
echo $parentID['name'] . '/';
}
else
{
unset ($parent);
}
}
}Its giving the results in reverse ie Country, State, Region instead of Region, State, Country. Does anyone have any ideas how I can get this sorted. One of you PHP guru’s out htere must have an idea
Done it!!!
$r=0;
$parent = $res['name'];
while(isset($parent)) {
$parentExists = mysql_query ("SELECT * FROM table2 WHERE id = $parent");
if (mysql_num_rows($parentExists)>0)
{
$parentID = mysql_fetch_array ($parentExists);
$parent = $parentID['parent'];
$buffer[] = $parentID['name'];
$rec=$r;
$r++;
}
else
{
// Dump the contents of the buffer
for($results = $rec; $results >= 0 ; $results--)
{
echo $buffer[$results].'/';
}
$buffer = array();
unset ($parent);
}
}
Also this can be used to add a simple breadcrumb link