Advanced search  

News:

cpg1.5.48 Security release - upgrade mandatory!
The Coppermine development team is releasing a security update for Coppermine in order to counter a recently discovered vulnerability. It is important that all users who run version cpg1.5.46 or older update to this latest version as soon as possible.
[more]

Pages: [1]   Go Down

Author Topic: Search statement improvement  (Read 2329 times)

0 Members and 1 Guest are viewing this topic.

szymic1

  • Coppermine newbie
  • Offline Offline
  • Posts: 3
Search statement improvement
« 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.

Logged
Pages: [1]   Go Up
 

Page created in 0.024 seconds with 19 queries.