data fetch from mysql (includes sorting)

Hi,

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.*?(\!|\?|\.)';");

Code (markup):

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

PHP:

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...

 

have you tried to add this to your query

 ... order by Rn asc //or desc

Code (markup):

 

Well my query does not even work. It does not get the sentence for some reason. Maybe the regex is wrong.

How do I define Rn ? It is not a field, column, table or database. It's just a word near the search keyword...

I could somehow do

(SELECT sentence REGEXP ' (.*?) $keyword') AS R1 ORDER BY R1 ASC;

Code (markup):

But how?