Why will the following SQL command not work with mysql_query in PHP?

SELECT       *
FROM         `enzymes`
  INNER JOIN `compounds`
  ON         compounds.compound_id = enzymes.compound_id
WHERE        `ec` LIKE '1.11%'

it works in phpmyadmin and mysql workbench.

It won't work re开发者_如何学JAVAgardless whether I backtick everything use mysql_real_escape_string and add the database name where appropriate. the phpmyadmin php code won't work either.

ERRORS: Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in....

same error for all other query-resource dependent functions

Upd:

case 'ec':
    $dbl = mysql_connect(DB_ADDRESS, DB_USER, DB_PASSWORD);
    mysql_select_db("kegg", $dbl);
    //a)connection is fine
    //b)tried with explicitly providing the db name vs with pre-selection
    $sql = "SELECT * FROM enzymes INNER JOIN `compounds` ON compounds.compound_id=enzymes.compound_id";
    print_r($sql);
    $result = mysql_query( $sql, $dbl);
    print  mysql_error ($dbl);
    while ($row = mysql_fetch_assoc($result)) {
         print_r($row)
         $items[ $row['name'] ] = $row['compound_id'];          
    }

,,,

Solution: Thanks everyone,- Brad gave the crucial answer first.

$sql = "SELECT enzymes.*, compounds.*\n"
. "FROM enzymes\n"
. " INNER JOIN compounds\n"
. " ON compounds.compound_id = enzymes.compound_id\n"
. "WHERE enzymes.ec LIKE '1.11%' LIMIT 0, 30 ";

A similar topic came up recently where it was suggested that it only works when selecting specific fields. The crucial solution when all data is desired is to resolve ambiguities to:

enzymes.*, compounds.*

---

Given that error, mysql_query is returning a boolean FALSE, indicating an error. Try this:

$result = mysql_query(...your query here...) or die(mysql_error());

to see exactly what the error is.

---

My gut is telling me the database is confused by your column references. When you start doing joins, and especially when you have columns names that may match, it's good practice to be explicit on what you're selecting, comparing, etc. e.g.

SELECT       enzymes.*
FROM         enzymes
  INNER JOIN compounds
  ON         compounds.compound_id = enzymes.compound_id
WHERE        counpounds.ec LIKE '1.11%'

But, without knowing the exact error that's occurring, this can only be a guess.

UPDATE

Now that I see the error, make sure you're testing for a proper query response. When you call mysql_query (or whatever you use), check the resource for failure before trying to fetch the result. Most connection libraries have a *_error or *_last_error method you can call to see what caused the failure.

---

There are a number of reasons that a query will work in environments like phpMyAdmin but not through PHP's mysql functions. For starters, make sure you've selected the proper database with a USE command via PHP's mysql_select_db function (docs). When using phpMyAdmin, that is being performed in the background automatically, not so with a PHP script.

If you specify the names of the database in your query, the previous step won't be required, BUT, your code will be that much harder to maintain if there is ever a change in the database name.

To specify the table, do this: SELECT field1, field2 FROM database_name.table_name WHERE...

Second, if you are using double or single quotes incorrectly, the variables might not be parsed or other such issues: this will cause your query to be invalid. A code sample would help determine if this is the case.

---

This indicated that you should pass an mySQL resource in whith the mysql_query.

look at:

resource mysql_query ( string $query [, resource $link_identifier ] )

You will get the $link_identifier when opening a connection:

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
   die('Could not connect: ' . mysql_error());
}

---

Avoid column ambiguous error in MySQL INNER JOIN. Please try:

SELECT       compounds.compound_id as cid, enzymes.compound_id as eid
FROM         `enzymes`
  INNER JOIN `compounds`
  ON         compounds.compound_id = enzymes.compound_id
WHERE        `enzymes`.`ec` LIKE '1.11%'

And if you'll get similar pairs of ids, the problem is here.