search function

[Former user]

Originally reported on Google Code with ID 139

Hey

After i saw issue 138 i also noticed that maybe we need some enhancement for the search
function:

1. When i search for any word and choose LIKE, this wont work accept when i add % sign
to the word %searchWord%. Without the % sign its acting same as (=).

maybe we should check if the user already using % sign when he chooses LIKE.
If not, than we should add the % sign to the search word.

2. it would be good to also mark the founded search word in results, by adding a class="found"

3. We can also offer RegExp for search options. You can see how i did it: http://www.havalite.com/?p=98

4. I'm missing the replace function, in case i would like to replace a word in my table!

I believe these 5 points (with issue 138) are very important and can make phpLiteAdmin
to a powerful tool.

Reported by teryaki1963 on 2012-11-09 23:16:01

[Christopher Kramer]

Hey,

1. I don't agree completely. LIKE and = is not the same, even if we don't use %.
'Test' LIKE 'test' is true whereas 'Test'='test' is false. If I choose "LIKE", I really
mean LIKE and would hate phpLiteAdmin for adding % without me asking for it. But what
I would propose is to add another operator "LIKE %...%" like PhphMyAdmin does (see
screenshot).

2. I agree.

3. Would be a cool feature, agreed.

4. Cool idea, agreed.

Reported by crazy4chrissi on 2012-11-10 13:42:41 - Status changed: Accepted

<hr> * Attachment: pma_like.png<br>

[Former user]

    But what I would propose is to add another operator "LIKE %...%" like PhphMyAdmin
does

Yep, thats better.

For point 2, here is a simple function:

function searchResMarker($srch, $text){
    $srch = '/'.$srch.'/ui';
    if (preg_match_all($srch, $text, $args, PREG_PATTERN_ORDER)) {
        $text = preg_replace($srch, '<u class="found">'.$args[0][0].'</u>', $text);
    }
    return $text;
}

// style: .found{ background:#FFFF00; text-decoration:none; }

You don't need this if you have better solution. I mean only to share my help

Reported by teryaki1963 on 2012-11-10 14:41:14

[Christopher Kramer]

Thanks. Always be careful with preg_* and user-submitted text. Better do:
$srch = '/'.preg_quote($srch,'/').'/ui';
Otherwise, $srch containing characters that hava a special meaning in preg would be
interpreted as such. For example searching for "fullstop. Go on here." would also return
"fullstop, Go on here,".

We should also be careful with case-insensitive. We should only use this if it was
a LIKE search, not if it was a = search. In case of a = search, using str_replace()
might be an easier and more efficient way than using regex.

Reported by crazy4chrissi on 2012-11-10 14:51:21

[Former user]

Very good
From all these points the most difficult is the replacement tool.
Here is an image, how i would integrate it:
Also ask before replacing: Are you sure, you want to replace %s in table %s?
-----------------

One more point!
When i select a database, i see no SEARCH?
I believe this is important as well to quickly find anything. it mustn't be much and
we can add in the left box, just like the image i did.

Reported by teryaki1963 on 2012-11-11 11:13:27

<hr> * Attachment: ReplaceTool.png<br> * Attachment: db_search.png<br>

[Christopher Kramer]

Hmm. regarding replace: wouldn't we better add one replace-field for every search-field?

This would allow me to replace "www." with "http://www." in url and "tipo" with "typo"
in "name" in one go.

Or would we simply require the user to do multiple serach/replace operations in this
case?

Regarding "Search in Database": definitely a nice feature. Although not too easy to
implement. This can hardly be done with one SQL statement as we'd need to UNION the
results of multiple SELECTs. But the tables might have a different number of columns,
so UNION doesn't work.
So we need to query multiple tables and return the results of each table.
Of course not very complicated indeed, but needs to be implemented and is not straight-forward
using the current implementation.

Another thought: Would make sense to be able to restrict the tables to search inside.
Some tables might be very huge and you might know they don't contain your search string.

So I guess we could (additionally?) add another tab to search inside dbs where you
can
- choose the tables
- select LIKE, =, <, > and so on as operators
- and enter your search string

Reported by crazy4chrissi on 2012-11-11 18:16:05

[Former user]

Hey
What do you think is better in search option:

1. if (LIKE %..% == selected) JS will change value in the textarea to %value% (the
value of <option> remains "LIKE")
2. or Php will check the operator %LIKE% and set % to the value

I'm for the first choice, cause maybe the user wants only %value or value%

Reported by teryaki1963 on 2012-11-18 12:17:09

[Christopher Kramer]

Hey,

maybe an hybrid approach? So JS will add % around the search-value. But if JS is deactivated
and the "LIKE %...%"-operator is chosen, php will check whether there really is (at
least one) % around the value and if not, add the %s. I would say that would be the
optimal solution both for users having JS enabled and users using noscript etc.

I might be a bit conservative in this regard, but I like to have a non-js fallback
solution wherever possible.

Reported by crazy4chrissi on 2012-11-18 15:30:37

[Former user]

Ok
LIKE %..% added
JS and php check, both should work

Reported by teryaki1963 on 2012-11-19 05:10:00

[Christopher Kramer]

Thanks!

I corrected 2 small things:
- htmlencode() around fieldnames. A field can be called like this for example: bla">yeah
  We need to encode stuff like this properly, otherwise stuff will break if fieldnames
contain special characters like quotes, <>& and stuff like this.
- the php-check did not add % at the end, only at the beginning

(Sorry that I always fine-tune some stuff that you commit. Your work is always very
good and helpful and you really push this project forward. I just think we can improve
our overall quality if somebody reviews all changes and corrects things he notices.
I am sure my commits often need corrections like this as well, so feel free to correct
me if you notice something.)

Reported by crazy4chrissi on 2012-11-19 10:02:23

[Former user]

Don't worry my friend, i also depend on you and 4 eyes are better than 2. 
The second correction: i missunderstood you cause you said:
(php will check whether there really is (at least one) % around the value and if not,
add the %s.)
Thats why i added only one %
----------------------
I'll check, what comes next and see if i can find good solution

Reported by teryaki1963 on 2012-11-19 12:30:27

[Former user]

Point 2: mark the founded search word in results, by adding a class="found"

- i used this only in case of LIKE, other cases won't make much sense.
- str_replace makes difference in upper/lower case, i used str_ireplace which is php5
above. (i believe, this is no problem according to sqlite+php5)

Reported by teryaki1963 on 2012-11-19 15:12:42

[Christopher Kramer]

Cool. Thanks for doing this. I noticed 1 problem. Try this test case:

CREATE TABLE "test139_12" ('a' TEXT);
INSERT INTO "test139_12" ("a") VALUES ('& am');

Now search for LIKE %am%.

The & will be replaced by & and then search & replace begins marking the "am" in "&".
This will break output HTML and produce unexpected output.

It's not too easy to fix this because if we htmlencode() later, this would break the
<u>-html markup. I might have a look at it tomorrow.
I guess we'll need to use another temporary marker or repair the <u> after htmlencoding.
Perfect solution might be an advanced htmlencode()-function with a parameter like the
strip_tags()-parameter that allows to ignore certain allowed tags.
I don't have time to have a closer look at it now.

Reported by crazy4chrissi on 2012-11-19 21:04:37

[Former user]

Yep, thats right, but changing htmlencode() just because of this one operation is not
necessary.
there is also another easy solution by adding fake tags [fnd]result[/fnd] than replace
it with the real one after encoding. Check svn!

Reported by teryaki1963 on 2012-11-19 22:32:23

[Christopher Kramer]

Yeah, that's what I meant with "another temporary marker".

Problem with this approach is we now assume that our data does not contain [fnd] or
[/fnd]. Example:

CREATE TABLE "test139_14" ('a' TEXT,'b' INTEGER);
INSERT INTO "test139_14" ("a") VALUES ('[fnd] bla',1);

Now search for b=1. Strange things will happen: In a column where we did not even search
for (a), text that we did not search for (bla) gets highlighted and HTML gets invalid
as there is no [/fnd] so no </u>.

Of course we could assume this a rare case. But I'm still not really happy with the
solution.
We could at least make sure every [fnd] has a [/fnd] (preg_replace?) and we only replace
it, if we are really introduced one in this cell. This would avoid most problems, still
leaving some problematic cases open of course.

Most easy solution I can think of at the moment would be to use a random-number concatenated
like [fnd82736568494]. Probability that this is contained is really low, but we can
even make sure: We check whether the cell currently contains this text.  If not, it
is safe to use it. If it does, we generate a new random number until we find one that
is not contained. As the length of SQLite texts is always finite, not all natural numbers
can be contained in a text so we will eventually find one that is not contained.

Okay, maybe I shouldn't write comments like this in-between doing computer science
theory. We probably don't wanna prove correctness of phpLiteAdmin ;-)

Hmm... also not a very elegant solution...

Reported by crazy4chrissi on 2012-11-19 22:53:41

[Former user]

Okay, maybe I shouldn't write comments like this in-between doing computer science theory.

hmmm, yep :D

Reported by teryaki1963 on 2012-11-20 00:46:49

[Christopher Kramer]

Okay, here is the elegant solution:
We do not insert a marker like [fnd]. We split the string into parts (multiple strings).
The boundaries between the parts are implicit markers. We then run htmlencode on each
of the parts. We finally stick all the parts together, adding <u>'s and </u>'s between
the parts.
Example: Searching for BLA in the string "BLA this& BLAis some bla stringBLA".
Gets splittet into:
[0]=>"" // before the first occurence
[1]=>"BLA" // first occurence
[2]=>" this& " // between 1st and second
[3]=>"BLA" // 2nd occurence
[4]=>"is some " // between 2nd and 3rd
[5]=>"bla" // 3rd - here we see we need to keep the occurences as well because they
can be lowercase or even if we searched uppercase. Problem with the current approach!
[6]=>" string" // between 3rd and last
[7]=>"BLA" // 3rd

Now we do htmlencode() on all the parts. This will change
[2] => " this& "

And now for every part with an odd index (1,3,5,7) we do "<u>".$part."</u>" and stick
everything together. Alternatively we could do $part."<u>" after all even indexes and
$part."</u>" after all odd indexes.

Reported by crazy4chrissi on 2012-11-23 10:58:43

[Former user]

Hey
well, i'm trying to avoid big operations. I really don't find the the solution with
[fnd] bad maybe a bit poor, but if we use something like [fNdQx19f] (no need for random
operations) than, i don't think that anyone on earth will search for this (except crazy
people :) )
But i'm also flexible and you can surely try your solution, if you think its safer
for getting exact results.

Reported by teryaki1963 on 2012-11-23 16:23:53

[phpLiteAdmin]

Changeset: a30abf9c9374

Summary: improved highlighting of search results as sketched here: https://bitbucket.org/phpliteadmin/public/issues/139/search-function#comment-19342021 Before, weird things happend, like when searching for LIKE 'bla', a row containing 'BLA' was incorrectly displayed in lowercase in the search results. Also, LIKE %..., LIKE ...% and LIKE %...% now highlight correctly. Before, searching for 'test%' would have highlighted both 'test' occurences in 'test test', even though only the first one matches the query. As no markers are used anymore, this also solves problems with rows containing the strings [fnd] and [/fnd], which caused incorrect highlighting before as we used these strings as markers.