Show Correct SQL Criteria based on User Selection

[msdbsql]

I am using the following statement:
$result = mysql_query("SELECT DISTINCT products_to_injuries.product_id, products.product_id, products.product_name, products.gender, products.product_name, products.tci,products.vci,images.product_image, injury.injury_name FROM ((products_to_injuries INNER JOIN products ON products_to_injuries.product_id = products.product_id) INNER JOIN images ON products.product_id = images.product_id) INNER JOIN injury ON products_to_injuries.injury_id = injury.injury_id WHERE (((products.gender)=\"$gender\") AND ((injury.injury_id)=$injury)) AND ((images.gender)=\"$gender\")", $dbh);



The user wants the following criteria to show on the screen above the listed results:

Gender
Injury Name

However the problem is that the wrong injury name appears.

The injury table has two fields:
injury_id
injury_name

the table has ten rows

Example of data in table
id name
1 break
2 scratch
3 dent

Navigation:
The user form has a link to each ID the user can select from.
Based on the selection a results.php is executed that will show the selected criteria and results.

The user wants the correct selected criteria to show.

Do I need to modify the query? Or edit another statement to make sure that the correct name shows up with the correct ID.
As it is now, If the user selects an injury with ID=1 the name that shows in the criteria does not match.

 

[sylve]

I'm not if i can answer this correctly because there is some info missing. You have 4 tables: injury, products, products_to_injury and image.

Forgetting image for now, products_to_injury links the two others together. BUT can a product have more then one injury and can a injury apply to more then one product?

I'm going to assume that injury ID = 1 could apply to more then one product. If i'm wrong please correct me The following query excludes the image table.

$result = mysql_query("
SELECT 
injury.injury_name, 
products.product_id, 
products.product_name, 
products.gender, 
products.tci,
products.vci
FROM 
	injury,
	products_to_injuries,
	products
WHERE 
	products_to_injury.injury_id = injury.injury_id 
AND
	products_to_injury.product_id = products.products_id
AND
	products.gender='$gender' 
AND 
	injury.injury_id=$injury
AND 
	images.gender='$gender'
", $dbh);

Also, do you want the injury to show if it has no products attached? If this doesn't help you, please post again with more details on your tables. You might also get a better response in the MySQL section.

 

[msdbsql]

I'm not if i can answer this correctly because there is some info missing. You have 4 tables: injury, products, products_to_injury and image.

Forgetting image for now, products_to_injury links the two others together. BUT can a product have more then one injury and can a injury apply to more then one product?
YES - on both counts. A product can have more than one
injury and an injury can apply to more than one product.


I'm going to assume that injury ID = 1 could apply to more then one product. If i'm wrong please correct me The following query excludes the image table.

You are correct.

Also, do you want the injury to show if it has no products attached? If this doesn't help you, please post again with more details on your tables. You might also get a better response in the MySQL section.

Every injury has some products associated with it.

I may take your suggestion regarding posting this to mysql. I guess I thought this was more of a PHP question. thanks