current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free.
up vote 1 down vote favorite

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 :)

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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
share|improve this answer
    
returns an error of "Invalid query: Every derived table must have its own alias" –  cyclobs Jun 24 '11 at 1:11
    
Figgured out that error, the last line "ORDER BY occurrences DESC" needs to be "t ORDER BY occurrences DESC". Thanks for the help –  cyclobs Jun 24 '11 at 1:22
    
No problem. I fixed my solution to add the alias in. –  Jacob Eggers Jun 24 '11 at 17:05
up vote 0 down vote

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);
?>
share|improve this answer
    
i tried this but, the resulting output would be the same page (if there was 3 results it'd be the first result 3 times) –  cyclobs Jun 24 '11 at 1:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.