forum.coppermine-gallery.net
No Support => Feature requests => Topic started by: szymic1 on September 02, 2005, 05:05:20 pm
-
Hi,
I modify Coppermina 1.3.3 for my purposes and I've analysed how Coppermine search for picture (using search form).
Usually it is done like that (we looking for pictures "morze kaktus krajobraz"):
[7] => SELECT pid FROM cpg133_pictures WHERE ( CONCAT(' ', keywords, ' ') LIKE '% kaktus %' OR filename LIKE '%kaktus%' OR title LIKE '%kaktus%' OR location LIKE '%kaktus%'OR caption LIKE '%kaktus%' )
[8] => SELECT pid FROM cpg133_pictures WHERE ( CONCAT(' ', keywords, ' ') LIKE '% krajobraz %' OR filename LIKE '%krajobraz%' OR title LIKE '%krajobraz%' OR location LIKE '%krajobraz%'OR caption LIKE '%krajobraz%' )
[9] => SELECT pid FROM cpg133_pictures WHERE ( CONCAT(' ', keywords, ' ') LIKE '% morze %' OR filename LIKE '%morze%' OR title
LIKE '%morze%' OR location LIKE '%morze%'OR caption LIKE '%morze%' )
And finaly:
SELECT pid, filepath, filename, url_prefix, filesize, pwidth, pheight, ctime, aid, title, caption,user1, country_name, pricecat_name FROM cpg133_pictures, cpg133_country, cpg133_pricecat WHERE ( pid in ( LISTO OF ALL PIDS FROM STATEMENTS ABOVE ))
If pictere set is very big final statement can be very very long (I plan to use coppermina to store 10 thousands pictures) and even cause of mySQL or PHP crash.
But it can be done in such way:
SELECT pid FROM cpg133_pictures WHERE ( CONCAT(' ', keywords, ' ')
LIKE '% morze %' OR filename LIKE '%morze%' OR title LIKE '%morze%' OR location
LIKE '%morze%'OR caption LIKE '%morze%' )
AND
( CONCAT(' ', keywords, ' ') LIKE '% kaktus %' OR filename
LIKE '%kaktus%' OR title LIKE '%kaktus%' OR location
LIKE '%kaktus%'OR caption LIKE '%kaktus%' )
AND ( CONCAT(' ', keywords, ' ') LIKE '% krajobraz %'
OR filename LIKE '%krajobraz%' OR title LIKE '%krajobraz%' OR location LIKE
'%krajobraz%'OR caption LIKE '%krajobraz%' )
AND user2='CL'
Creation of such dynamic statement is very easy and I do not understand why Coppermina developers use several statements instead one?
P.S I've removed less important parts from statements.