I'm trying to do a simple EXPLAIN query in MySQL from a php script:

$link = mysql_connect($dbHost, $dbUser, $dbPass);
$database = "test";
$sql = "SELECT * from t t1, t t2";
mysql_select_db($database, $link);
$explainSQL = "EXPLAIN " . $sql;
echo $explainSQL;
$explainResult = mysql_query($explainSQL, $link);
while($explainRow = mysql_fetch_array($explainResult)){
 $tables[] = $explainRow;
}
print_r($tables);

But I get this:

EXPLAIN SELECT * from t t1, t t2
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\xampp\xampp\htdocs\ajaxMyTop\ajaxMyTop\explain.php on line 9

If I run the echo'd explainSQL directly in MySQL, it works fine.

Is there something special I have to use to work with result-sets from EXPLAIN statements?

    Add to this line:

    $explainResult = mysql_query($explainSQL, $link);

    or die(mysql_error()) like this:

    $explainResult = mysql_query($explainSQL, $link) or die(mysql_error());
    groovepapa wrote:

    Is there something special I have to use to work with result-sets from EXPLAIN statements?

    No.

      D'oh, I should have thought of that before posting. Thanks.

      I'll have to try that when I get back home this evening.

        $sql = "SELECT * from t t1, t t2";

        is t t1, t t2 seperate tables? remove the space

          PABobo wrote:

          $sql = "SELECT * from t t1, t t2";

          is t t1, t t2 seperate tables? remove the space

          No, t1 and t2 are aliases for the same t table he don't need to remove the spaces, maybe some fields from that table are linked with each other ...

            Using the mysql_error, I found that the userid I was connecting with did not have "SELECT" permissions on the particular database I was using. So apparently the SELECT privilege is required to execute EXPLAIN statements.

              U need to set permision of read/write for that user from your CP or ask your webmaster to set it for u ...

                Yeah, I took care of it. I'm running a home server for testing so it was no biggie, just had trouble tracking down the problem.

                  Write a Reply...