php:将数组与 mysql 结果合并并按数组键对它们进行排序

Question

基本上我正在为我的网站构建一个搜索引擎，但我的 sql 数据库基本上包含 2 组表(一组用于网站上的页面，另一组用于文件(.doc 等))

我已经得到了 popluate 的工作，我得到了它返回页面的结果，现在我想搜索页面和文件，我想出了运行 2 个查询(因为有 2 个表集)然后合并它们的想法将 2 个结果数组合二为一，然后按查询添加的“出现次数”对它们进行排序。但是输出与输入数组不匹配。不管怎样，一些代码可以给你一些东西来解决

// 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);

带有 var dump 的搜索词 (a) 的输出如下所示

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" }

它们的顺序与上面的变量相同

在手册中环顾了一圈，但对于如何按键 => 值对数组进行排序并没有真正有用的东西(例如 sort($results['occurrence'], DESC))

任何帮助都将不胜感激，谢谢你们:)

Answer 1

如何在 sql 中作为一个联合来实现？

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