Trouble joining three MySQL tables

[rskuse]

I am using Dreamweaver MX and I am having trouble linking three MySQL tables. My code is as follows:

if (isset($HTTP_GET_VARS['subcat_id'])) {
$colname_SubCategory = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['subcat_id'] : addslashes($HTTP_GET_VARS['subcat_id']);
}
mysql_select_db($database_iserver, $iserver);
$query_SubCategory = sprintf("SELECT * FROM category, subcats, topics WHERE category.cat_id = subcats.cat_id AND subcats.subcat_id = topics.subcat_id AND %s = subcat_id", $colname_SubCategory);

The trouble i'm having is that the same subcat is being returned for each subcat_id (where subcat is a field in my subcats table).

That is to say that...
../subcat.php?subcat_id=1
../subcat.php?subcat_id=2
../subcat.php?subcat_id=3
are all returning the same subcat from the database.

Everything else seems to be working fine. Can anyone tell me where I'm going wrong?

Thankyou in advance...

Rachel

 

[sleipnir214]

It looks to me like your query isn't specifying the actual row to return. It looks to me, rather, that you're specifying a column to match to some other column.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[DRJ478]

It also looks as if MySQL should complain about the statement as there is an ambiguos identifier subcat_id - it could be in topics or subcats.

Do you have error checking on the mysql_query()?

 

[rskuse]

Sorry, I did have an ambiguous identifier. It is actually subcats.subcat_id.

How do I specify the actual row to return?

 

[sleipnir214]

You may be, I don't know.

It's just that I don't see you performing something like:

AND somecolumn = somespecificvalue

in the WHERE clause of your query.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[DRJ478]

Just for clarity:
Wouldn't you compare the database column to the entered value rather than vice versa? I think that's what sleipnir is pointing out.

%s = subcats.subcat_id # the passed value is first
subcats.subcat_id = %s # that looks better

 

[sleipnir214]

Perhaps that's it. Maybe I was just thrown off by the weird construct.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[rskuse]

I get the same result....

I really do appreciate your help, I'm still learning php, MySQL and 'joining tables' is confusing me just a tad.

 

[DRJ478]

Echo out the SQL statement and paste it into your favorite MySQL admin tool (PHPMyAdmin etc.)
What is the result there?

Also:
Write the pseudo-op code for the SQL. That will help clarify what it is you want to do.