MySQL query help

[dont_be_hasty]

Hi,

 

Im using php and mysql as part of a web based project.

 

The website has 3 seperate inputs, which the user can enter info, then using a mysql query it will display the correct infor from the database.

 

Im using the following tables in the database.

term(id, name, term_type, acc)

gene_product(id, symbol, species_id)

association(id, term_id, gene_product_id)

gene_product_count(term_id, code, speciesdbname, species_id)

 

So the user need to be able to input the following:

• symbol
  
• term_type
  
• speciesdbname
  

 

The query will use all 3 of these and display the correct term names.

 

I started with this query which uses just the inputed symbol.

$query="SELECT name FROM term, association, gene_product WHERE association.term_id=term.id AND association.gene_product_id=gene_product.id AND symbol='$gene'";

 

I then changed it so it can use both gene name and term_type.

if($ontology == "all")
       {
$query1="SELECT name FROM term, association, gene_product WHERE association.term_id=term.id AND association.gene_product_id=gene_product.id AND symbol='$gene'";
}
else
       {
$query1="SELECT name FROM term, association, gene_product WHERE association.term_id=term.id AND association.gene_product_id=gene_product.id AND symbol='$gene' AND term_type='$ontology'";
        }

 

I now need to add the speciesdbname to the query, but im not sure how to go about it.

 

Ive tried adding this:

$query1="SELECT name FROM term, association, gene_product, gene_product_count WHERE association.term_id=term.id AND association.gene_product_id=gene_product.id AND gene_product.species_id=gene_product_count.species_id AND symbol='$gene1' AND term_type='$ontology' AND speciesdbname='$datasource'";

 

This doesnt seem to work. Ive also tried joining the gene_product_count and term table by term_id (as this both have this), but it didnt work either.

 

Anyone got any ideas of what im doing wrong?

 

[kickstart]

Hi

 

Nothing obvious, although I would split off the part where you are specifying the columns to join on out of the where clause.

 

$query1="SELECT name 
FROM term 
JOIN association ON association.term_id = term.id 
JOIN gene_product ON association.gene_product_id = gene_product.id 
JOIN gene_product_count ON gene_product.species_id = gene_product_count.species_id AND association.term_id = gene_product_count.term_id
WHERE symbol='$gene1' 
AND term_type='$ontology' 
AND speciesdbname='$datasource'";

 

Are you getting an error, or just nothing brought back?

 

All the best

 

Keith

[dont_be_hasty]

There is no error, just a blank space where it should be displaying them.

 

It was displaying values before i added speciesdbname stuff to the equation and also returns values if i select 'All', so its seems to be something with the query and that its not successfully getting the speciesdbname

[kickstart]

Hi

 

Or there isn't a matching record on the gene_product_count table.

 

All the best

 

Keith