Output as array

Question

       $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
                               " );

$output = "<loginsuccess>";

for( $i = 1; $row = mysql_fetch_array($result); $i++ )   {


$output .="<keyword>".$_POST['keyword']."</keyword><name>".$row['url']."</name><occur>".$row['occurrences']."</occur><queryTime>".(substr($end_time-$start_time,0,5))."</queryTime>";
}

$output .= "</loginsuccess>";
print ($output);

I am gettign the output in XML, instead i want it in array... how to achieve this.

The code below is not working...

$ret = array();
for( $i = 1; $row = mysql_fetch_array($result); $i++ )   {

     $tmp['url'] = $row['url'];
     $tmp['occurrences'] = $row['occurrences'];

     $ret[] = $tmp;

}
 return $ret;

Answer 1

You're already getting the results in an array when you call mysql_fetch_array($result). That's what mysql_fetch_array does.

Unless you want some other kind of array format? If so, you'll have to be more specific.

Answer 2

Use print_r():

echo "<pre>";
print_r($row);
echo "</pre>";

Note: If you won't wrap it in <pre> tags, the output will be difficult to read as it will appear all on one line

Answer 3

Try it like so, notice the here-doc style of writing the SQL statement. I find it very helpful. Also I would suggest rewriting the SQL to use JOINs instead of crowding the WHERE clause with join criteria.

$sql = <<<EOSQL
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;
EOSQL;
$result = mysql_query($sql) or die('failed to execute the query');

while ($row = mysql_fetch_array($result)) {
    print_r ($row);
}

Hope this is roughly what you're after

Answer 4

A for-loop is only to be used in an incremental fashion, you attempted to use it in place of a while loop. This isn't "do 1,000 times" but "do while we have a row to use".

Try this:

while($row = mysql_fetch_array($result)) {
  $tmp['url'] = $row['url'];
  $tmp['occurrences'] = $row['occurrences'];
  $ret[] = $tmp;
}
return $ret;

Answer 5

You need to initialize your $tmp array before using it.