php: merging arrays with mysql results and sorting them by an array key

basically i'm building an serch engine for开发者_开发百科 my website but my sql database contains basically 2 sets of tables (one set for pages on the site and another set for the files (.doc etc))

I've got the popluate working and i got it returning results of pages, now i want to search both pages and files which i came up with the idea of running 2 querys (because of the 2 table sets) then merging those 2 resulting arrays into one and then sorting them by 'occurrence' which is added in by the querys. But the output isn't matching the input arrays. anyway some code to give you something to work off

// Search the DB for pages
$page_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.page_word_id = o.page_word_id AND w.word_word LIKE '' '" . $stemmed_string . "' '%' GROUP BY p.page_id ORDER BY occurrences DESC") // LIMIT " . $results . "")
or die("Invalid query: " . mysql_error());

// Search the DB for files
$file_result = mysql_query("SELECT f.file_url AS url, COUNT(*) AS occurrences FROM files f, filenames fn, fileoccurrence fo WHERE f.file_id = fo.file_id AND fn.file_word_id = fo.file_word_id AND fn.file_word LIKE '' '" . $stemmed_string . "' '%' GROUP BY f.file_id ORDER BY occurrences DESC")
or die ("Invalid query: " . mysql_error());

$page_array = mysql_fetch_array($page_result);
$file_array = mysql_fetch_array($file_result);


$results = array_merge((array)$page_array, (array)$file_array);

the output of a search term (a) with var dumps looks like this

array(4) { [0]=> string(33) "/index.php?page=footy_tipping.htm" ["url"]=> string(33) "/index.php?page=footy_tipping.htm" [1]=> string(4) "1272" ["occurrences"]=> string(4) "1272" }

array(4) { [0]=> string(43) "/files/forms/misc/Adjustment%20TEMPLATE.xls" ["url"]=> string(43) "/files/forms/misc/Adjustment%20TEMPLATE.xls" [1]=> string(1) "2" ["occurrences"]=> string(1) "2" }

array(6) { [0]=> string(33) "/index.php?page=footy_tipping.htm" ["url"]=> string(43) "/files/forms/misc/Adjustment%20TEMPLATE.xls" [1]=> string(4) "1272" ["occurrences"]=> string(1) "2" [2]=> string(43) "/files/forms/misc/Adjustment%20TEMPLATE.xls" [3]=> string(1) "2" }

they are ordered the same as the variables above

Had a look around in the manual and nothing really helpfull came up on how to sort an array by key => value (eg sort($results['occurrence'], DESC))

any help would be appreaciated thanks guys :)

---

How doing it in sql as a union?

SELECT * FROM (    
    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.page_word_id = o.page_word_id
        AND w.word_word LIKE '' '" . $stemmed_string . "' '%'
    GROUP BY p.page_id
    UNION
    SELECT f.file_url AS url,
        COUNT(*) AS occurrences
    FROM files f, filenames fn, fileoccurrence fo
    WHERE f.file_id = fo.file_id
        AND fn.file_word_id = fo.file_word_id
        AND fn.file_word LIKE '' '" . $stemmed_string . "' '%'
    GROUP BY f.file_id
) t
ORDER BY occurrences DESC

---

Have you look at array_multisort? You'll have to do something like this:

<?php
// Obtain a list of columns
foreach ($results as $key => $row) {
    $occurrences[$key]  = $row['occurrences'];
}

// Sort the data with volume descending, edition ascending
// Add $data as the last parameter, to sort by the common key
array_multisort($occurrences, SORT_DESC, $results);
?>