0

Hi all,

Im pretty ok with PHP and have recently been breaking down a project using foreign keys to identify various bits of information.

This is my problem.

I have an items table, which is made up of ID, country and area.

It previously has the actual word of the country and area.

I have broken it down so it now the id from the country table.

What i want to know how to do is when i request that item to be listed, i want it to reference the country table to find the name, as at present it just shows the number.

Please can somebody give me an example? Even just a small one.

This aint homework! its 10am on a monday morning :-/

4
Contributors
13
Replies
14
Views
7 Years
Discussion Span
Last Post by pritaeas
0

You can use two tables in your sql's from clause, and connect them with a where, like this:

select * from items,countries where items.country=countries.id
0

Create a table with ID, country
and your second table should be like areas, countryId

In your drop down display countries Names and give the value as CountryId,

On selecting the country, send that country Id to fectch the areas from the second table.

select secondtable.areas from firsttable, secondtable where secondtable.countryId = selectedValueFromDropDown(nothing but a countryId)

0

Hi,

Thanks to you both for responding. What would i echo out?

0

ok, i tried the query in phpmyadmin, and it works fine, but the php script outputs a blank screen. It has to be my syntax

$sql = "select * from tbl_item,tbl_category where tbl_item.category_id =tbl_category.category_name";
$result = mysql_query($sql);
//$row=mysql_fetch_array($result);
	//echo $sql;
	//echo $result;

while ($row = mysql_fetch_array($result)) { 
	echo "<br>";
	echo "<a href=\"showcat.php?id=" . $row['tbl_category.category_id'] . "\"/>" . $row['tbl_item.category'] . "</a>";
}
0

Hmmmmm i did try that but im still getting a blank screen

echo "<a href=\"showcat.php?id=" . $row['category_id'] . "\"/>" . $row['category'] . "</a>";
0

For that query im expecting it to output "home" and "car" as a hyperlink

0

ok, i got it working with the code below. only thing now is its stopped passing the variable

$sql = "SELECT DISTINCT category_name from tbl_item,tbl_category WHERE tbl_item.category_id = tbl_category.category_id";
$result = mysql_query($sql);
//$row=mysql_fetch_array($result);
	//echo $sql;
	//echo $result;

while ($row = mysql_fetch_array($result)) { 
	echo "<br>";
	echo "<a href=\"showcat.php?id=" . $row['category_id'] . "\"/>" . $row['category_name'] . "</a>";
}
0

ok, i got it working with the code below. only thing now is its stopped passing the variable

$sql = "SELECT DISTINCT category_name from tbl_item,tbl_category WHERE tbl_item.category_id = tbl_category.category_id";
$result = mysql_query($sql);
//$row=mysql_fetch_array($result);
	//echo $sql;
	//echo $result;

while ($row = mysql_fetch_array($result)) { 
	echo "<br>";
	echo "<a href=\"showcat.php?id=" . $row['category_id'] . "\"/>" . $row['category_name'] . "</a>";
}

It seems you are not fetching category_id in your query...

0

Ow, if it's that it's because category_id is used in both tables, so it can't determine which one you need. Instead of specyfying "select *" select only the columns you really need.

"\"/>" is escaped correctly...

Edited by pritaeas: n/a

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.