PHP and mysql 2 table references

[whiteyoh]

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 :-/

[pritaeas]

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

[saiprem]

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)

[whiteyoh]

Hi,

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

[pritaeas]

You can echo out the country column, after you got the mysql data row. Show your code.

[whiteyoh]

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>";
}

[pritaeas]

Change $row into $row and $row into $row

[whiteyoh]

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

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

[whiteyoh]

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

[whiteyoh]

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>";
}

[pritaeas]

Not sure what you mean.

[saiprem]

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...

[JRM]

too many double quotes

row['tbl_category.category_id'] . "\"/>" .

[pritaeas]

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...