[56K WARN] data fetch from mysql (includes sorting)

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

[56K WARN] data fetch from mysql (includes sorting)

Post by kaisellgren »

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

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:

Code: Select all

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

Code: Select all

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...
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

You might be able to achieve the same results with a full text search. As for sorting, let PHP handle that since you are offering several different sort criteria.
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Post by kaisellgren »

Everah wrote:You might be able to achieve the same results with a full text search. As for sorting, let PHP handle that since you are offering several different sort criteria.
Hmm.

A full text search?

Do you mean replacing REGEXP with LIKE?

Do you have any idea how to get one sentence that contains keyword in MySQL?

Something like

Code: Select all

SELECT (SELECT text REGEXP '(!|?|\.)(.*?$search.*?(!|?|\.))' FROM sources WHERE text LIKE '%$search%';) AS sentence;
But that is far away from working...

EDIT: Hey, how could I possibly make PHP to sort Ln or Rn ? I have no idea...
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

I mean using MATCH/AGAINST syntax. And as for sorting, you have the logic you want, you just need to turn that logic (that is in your head) into PHP code using sort(), ksort(), array_multisort(), etc.
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Post by kaisellgren »

Hmm ok.

Now I have it so that my $sentence is an array consisting of all sentences which has the search keyword.

However. How could I make it so that $sentence array is sorted by let's say L2 ? ...
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

What does L2 translate to in English?
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Post by kaisellgren »

L2 = Left 2

By L1, L2, L3, R1, R2, R3 I refer to words surrounding my search keyword.

For example if my sentence array has array line like "This is a test line." and if my keyword was "test", then L1 = "a", L2 = "is", L3 = "This", R1 = "line", R2 = "", R3 = "".
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Dayum, that's complicated. But you can adapt that logic to handle the sorting. It might require some use of the switch() construct, but it can be done.
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Post by kaisellgren »

My current code... =/

Code: Select all

if (isset($_POST['submit']))
 {
  $search = $_POST['search'];
  $sentences = array();
  $db -> query("SELECT text FROM ".PREFIX."sources WHERE text LIKE '%$search%';");
  while ($db -> fetch_assoc())
   {
    $result = $db -> row('text');
    preg_match_all("/(\\!|\\?|\\.)(.*?$search.*?(\\!|\\?|\\.))/ism",$result,$matches);
    for ($a = 0;$a < count($matches);$a++)
     {
      $value = $matches[2][$a];
      $sentences[] = trim($value);
     }
   }
  //$sentences = array('a line.' => array('L1','L2','L3','line','R1','R2','R3'));
  $sentenceslr = array();
  foreach ($sentences as $key)
   {
    // $key = full line
    preg_match("/\s+(.*?)\s+$search/i",$key,$match);
    $l1 = $match[1];
    echo "$l1 ($key)<br />"; // Just debugging and testing
   }
 }
This code should load all L1 into my array, but my regex is undone.
Post Reply