[SOLVED] How to query two databases???

[BioSeekerChick]

Hi,

I'm trying to execute a query that selects three tables from two databases (two are in one database and the other table is in the second database).  I'm not sure how to go about this, but I believe that my syntax is correct.

 

here is my query:

$query= "SELECT * FROM bioseeker_static.gene_accession g, bioseeker_static.genomes_proteins p, smidstatic.smid_hits h WHERE g.geneid=$geneid AND p.gi=g.protein_gi AND p.redund_gi= h.gi";

 

now i need an execute statement that combines the two databases "bioseeker_static" and "smidstatic"

 

is this even possible or would i be better off creating two queries?

 

any help would be awesome.

 

Thanks!

[Wildbug]

What do you mean that you need an "execute statement?"  Your query looks fine; does it get you what you want?  Have you tested it yet?

 

Could also be written:

SELECT * FROM bioseeker_static.gene_accession g JOIN bioseeker_static.genomes_proteins p ON p.gi=g.protein_gi JOIN smidstatic.smid_hits h ON p.redund_gi= h.gi WHERE g.geneid=$geneid;

 

To execute the query:

$result = mysql_query($query);
if ($result && mysql_num_rows($result) {
while ($rows = mysql_fetch_row($result)) { /* do stuff here */ }
} else { echo "No results found."; }

[BioSeekerChick]

it doesnt seem to work.  the error i get states that fetch_assoc() is a non-member.

 

here's the line of code that is referenced in my error:

while($row= $result1->fetch_assoc()) {

if ($row["genomic_acc"]== $genomic_acc && $contigpos-$row["genomic_start"] >0 && $contigpos-$row["genomic_stop"] <0 ){

$ligsites_array[$row["id"]]= SeparateLigSites($row["site"]);

}

 

where $result1 is from:

$query= "SELECT * FROM bioseeker_static.gene_accession g, bioseeker_static.genomes_proteins p, smidstatic.smid_hits h WHERE g.geneid=$geneid AND p.gi=g.protein_gi AND p.redund_gi= h.gi";

$result1= mysql_query($query);

 

im not sure if this helps at all.......

 

 

 

[BioSeekerChick]

sorry, to clarify, the error states that the function fetch_assoc is preformed on a non-object

[Wildbug]

Eh, yeah, because the result is not an object, but a resource handle.  You're mixing OOP with non-OOP functions.  Use mysqli functions if you really want OOP.

 

Or use:

while ($row = mysql_fetch_assoc($result)) { /* ...etc... */ }