search

Buzzinoffbond / phpliteadmin

Automatically exported from code.google.com/p/phpliteadmin
0 stars 0 forks source link

search function #139

Open GoogleCodeExporter opened 9 years ago

GoogleCodeExporter commented 9 years ago 
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.

Original issue reported on code.google.com by teryaki1...@googlemail.com on 9 Nov 2012 at 11:16

GoogleCodeExporter commented 9 years ago 
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.

Original comment by crazy4ch...@gmail.com on 10 Nov 2012 at 1:42

Attachments:

GoogleCodeExporter commented 9 years ago 
    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

Original comment by teryaki1...@googlemail.com on 10 Nov 2012 at 2:41

GoogleCodeExporter commented 9 years ago 
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.

Original comment by crazy4ch...@gmail.com on 10 Nov 2012 at 2:51

GoogleCodeExporter commented 9 years ago 
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.

Original comment by teryaki1...@googlemail.com on 11 Nov 2012 at 11:13

Attachments:

GoogleCodeExporter commented 9 years ago 
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

Original comment by crazy4ch...@gmail.com on 11 Nov 2012 at 6:16

GoogleCodeExporter commented 9 years ago 
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%

Original comment by teryaki1...@googlemail.com on 18 Nov 2012 at 12:17

GoogleCodeExporter commented 9 years ago 
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.

Original comment by crazy4ch...@gmail.com on 18 Nov 2012 at 3:30

GoogleCodeExporter commented 9 years ago 
Ok
LIKE %..% added
JS and php check, both should work

Original comment by teryaki1...@googlemail.com on 19 Nov 2012 at 5:10

GoogleCodeExporter commented 9 years ago 
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.)

Original comment by crazy4ch...@gmail.com on 19 Nov 2012 at 10:02

GoogleCodeExporter commented 9 years ago 
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

Original comment by teryaki1...@googlemail.com on 19 Nov 2012 at 12:30

GoogleCodeExporter commented 9 years ago 
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)

Original comment by teryaki1...@googlemail.com on 19 Nov 2012 at 3:12

GoogleCodeExporter commented 9 years ago 
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.

Original comment by crazy4ch...@gmail.com on 19 Nov 2012 at 9:04

GoogleCodeExporter commented 9 years ago 
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!

Original comment by teryaki1...@googlemail.com on 19 Nov 2012 at 10:32

GoogleCodeExporter commented 9 years ago 
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...

Original comment by crazy4ch...@gmail.com on 19 Nov 2012 at 10:53

GoogleCodeExporter commented 9 years ago 
Okay, maybe I shouldn't write comments like this in-between doing computer 
science theory.

hmmm, yep :D

Original comment by teryaki1...@googlemail.com on 20 Nov 2012 at 12:46

GoogleCodeExporter commented 9 years ago 
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.

Original comment by crazy4ch...@gmail.com on 23 Nov 2012 at 10:58

GoogleCodeExporter commented 9 years ago 
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.

Original comment by teryaki1...@googlemail.com on 23 Nov 2012 at 4:23