I setup up a mysql chemistry database using the mychem cartridge. I use the JSME javascript module by Peter Ertl to draw molecules and feed the molecular 'smiles' string to build the mysql query.
When I type my request in the mysql console, I get the results in the correct order, sorted on the column tan.
When I send the query through php to the mysql database using :
$db = mysql_connect('localhost', '****', '****') or die ('Unable to connect');
mysql_select_db('mychem', $db) or die(mysql_error($db));
$query = "SET @fp = (SELECT FINGERPRINT2(SMILES_TO_MOLECULE('".$_POST['smiles']."')))";
$results = mysql_query($query, $db) or die(mysql_error($db));
$query = "SELECT No, Name from (SELECT No, Name, tan from (SELECT No, Name, TANIMOTO(@fp, fin) as tan FROM (SELECT compounds.name as No, pdt.fullname as Name, bin_structures.fp2 as fin From pdt,compounds,bin_structures WHERE pdt.name=compounds.name AND compounds.id=bin_structures.compound_id AND MATCH_SUBSTRUCT('".$_POST['smiles']."',obserialized)) x order by tan DESC) y)z";
$results = mysql_query($query, $db) or die(mysql_error($db));
php code to populate the table :
while ($row = mysql_fetch_assoc($results)) {
echo "<tr>";
echo '<td><a href="info.php?name='.$row["No"].'">'.$row["No"].'</a></td><td>'.$row["Name"]./*'</td><td>'.$tan.*/'</td><td><img src ="structures/'.$row["No"].'.png" height="150"></td>';
echo "</tr>";
}
mysql_free_result($results); ?>
then I get the results sorted on the name column.
Do you have an idea of what I am doing wrong ?
I added an echo statement for print out my query. I used that query in the mysql console and I get the results in the correct order though.
Thank you for your inputs
