Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

Question

    $keyword = $_POST['keyword'];
    $start_time = getmicrotime();


   $result = mysql_query(" SELECT p.page_url AS url,
                           COUNT(*) AS occurrences 
                           FROM page p, word w, occurrence o
                           WHERE p.page_id = o.page_id AND
                           w.word_id = o.word_id AND
                           w.word_word = \"$keyword\"
                           GROUP BY p.page_id
                           ORDER BY occurrences DESC
                           " );

$end_time = getmicrotime();

$result1 = mysql_fetch_array(mysql_query($result));

Answer 1

simple error: $query is not defined

Better approach is to use it this way

$query = 'SELECT * FROM `mytable`';
$temp = mysql_query($query) ot die(mysql_error());
$result1 = mysql_fetch_array($temp);

Answer 2

Well, I don't see the variable "query" defined anywhere in your code :S It should probably be

$result1 = mysql_fetch_array($result)

Answer 3

The problem is your query fails and thus mysql_query() returns nothing resulting in your error. I personally use helper functions for this because it's too tedious to do this on every instance:

function db_query($query) {
  $res = mysql_query($query);
  if (!$res) {
    $error = mysql_error();
    die("Query Query error '$error' on query: $query");
  }
  return $res;
}

And then use that instead of mysql_query().

The other advantage of this kind of thing is that its easier to change underlying database if you're using wrapper functions like the above.

I can see what the problem with your query is though:

SELECT p.page_url AS url,
COUNT(*) AS occurrences 
FROM page p, word w, occurrence o
WHERE p.page_id = o.page_id AND
w.word_id = o.word_id AND
w.word_word = \"$keyword\"
GROUP BY p.page_id
ORDER BY occurrences DESC

You're trying to select a column (page_url) that you're not grouping by. I would rephrase it like this:

SELECT p.page_url AS url,
       COUNT(*) AS occurrences 
FROM page p
JOIN word w ON p.page_id = w.page_id
JOIN occurrence o ON w.word_id = o.word_id
WHERE w.word_id = '$keyword'
GROUP BY p.page_url
ORDER BY occurrences DESC

Lastly, make sure you do this:

$keyword = mysql_real_escape_string($keyword);

before using it in a query.

Answer 4

This is proper way to handle MySQL queries.

$result = mysql_query($query);
if (!$result)
{
    // debug code here; you may want to disable 
    // it somehow in production code
    echo "<b>MySQL error:</b><br>";
    echo mysql_error(), "<br>";
    echo "<b>Failing query:</b><br>";
    echo "<pre>", $query, "</pre><br>";
}

Disable debug code in production version / public release for obvious reasons. You may want to use a wrapper of some sort.