View Full Version : data fetch from mysql (includes sorting)

05-29-2007, 04:12 PM

So I have a HUGE database of English text. I have a great search functionaly that allows you to search and filter by categories, etc.

However. I have one thing to go. When I enter search keyword "Help" I should get all SENTENCES from my database that includes the keyword "Help" case-insensitive. I have a huge problem with sorting. This is a bit complicated to even explain. Let me try.

First of all I'll show you a 56 kB screenshot:

So I have made a search with keyword "Help". I got my results, that's good. Now you can see the L1, L2, L3, R1, R2 and R3 that means Left 1, Left 2, Left 3, Right 1, Right 2, Right 3.

I want to be able to sort my search results by any of those Ls or Rs. When I click L2, it should sort all search results by alphabetical order of a word which is located two words away from the left side of keyword "Help".

Here is all I need to do:
1) Make a MySQL query
2) Make the query to fetch all SENTENCES of text database texts that include the keyword
3) Then order the results by Ln or Rn (n = number from 1 to 3).

So what I have done so far is obviously far away from what I need. It's not even working:

$db -> query("SELECT (SELECT text FROM ".PREFIX."sources WHERE text REGEXP '$search') REGEXP '(\!|\?|\.).*?$search.*?(\!|\?|\.)';");
I was firstly trying to fetch all 'text' from 'sources' which contain $search in 'text' field. Then with those fields I want to get all SENTENCES which has the $search.

Any help would be appreciated. I will give credits!

EDIT: To help you a bit, here is a regex function which takes the correct sentence the way I want it to take it:

preg_match("/(!?\\.)(.*?$search.*?(!?\\.))/i",'this is just, an tes line. you see when it reads the correct test line. whohoo.',$matches);
echo ($matches[2]);
So know I just need to make it so that it finds all sentences from my sources.text fields and sort them by Ls and Rs properly...

05-30-2007, 02:40 AM
You could do it right in the query with a set of conditions. But you will have to figure how you want the sorting done when RIGHT or LEFT doesn't contain a word in position 1, 2 or 3. A good thing to remember, what you can do in the query should be done there, because it makes no sense to do more processing on your result outside the query when the database can do it when it's building the result set!

Also you can simplify your regex by just doing...

... WHERE text REGEXP '[[:<:]]keyword[[:>:]]';