Dynamical SQL-Query over different tables 1

[bateman23]

Hello i've got a big, big PHP, SQL-search statement problem. I've got a lot of tables looking like that:

[b]projekte:[/b]
nr: INT
name: INT
foerder_de: TEXT
foerder_en: TEXT
... and more...

[b]acronym:[/b]
id_acronym: INT
name: VARCHAR

[b]link_acronym[/b]
id_link_acronym: INT
id_acronym: INT
projekte_nr: INT

other tables, the same style, like
keyword, linkkeyword, orga, link_orga, and so on. The link-Table - you may guess - link the data-set with a project. (So, one project may have lot of acronyms or keywords)

Now i need to create a search-mask. The user should e.g. be able to search for all projekts which got a specific value in the text-field "foerder_de" OR "foerder_en") AND have the acronym "xyz" AND the keyword "test" or "test2". They also should be able change the AND with a OR-conjunction, add other conjunctions, and so on...

First problem: I need to create the sql-query absolutly dynamical. - I think i could get that...
the bigger problem is. I don't know how my sql-statement should look like. My first idea was just this:

SELECT * from projekte, acronym WHERE (acronym.name = "test" || acronym.name = "testing") && (projekte.foerder_de = "rahmen" || projekte.foerder_en = "rahmen" || projekte.foerder_dritte = "rahmen")

..and then add other Where clauses dynamically.
But, that isn't working. so i tried the following, which isn't working at all.
I get too many results and other results - i need to get - dont show up...

SELECT * 
FROM projekte 
LEFT OUTER JOIN link_acronym, acronym ON
acronym.id_acronym = link_acronym.id_acronym &&
acronym.name = "test" || acronym.name = "testing"
WHERE (projekte.foerder_de = "rahmen" || projekte.foerder_en = "rahmen" || projekte.foerder_dritte = "rahmen")

Any ideas about my sql-querys are welcome. I have no idea how to go on.
Thanks a lot in advance,
Daniel Purucker

 

[r937]

select projekte.* 
  from projekte 
inner
  join link_acronym
    on projekte.nr
     = link_acronym.projekte_nr
inner
  join acronym 
    on link_acronym.id_acronym
     = acronym.id_acronym 
   and acronym.name in ( 'test' , 'testing' )
 where (
       projekte.foerder_de = 'rahmen' 
    or projekte.foerder_en = 'rahmen' 
    or projekte.foerder_dritte = 'rahmen'
       )

if your foerder_xx columns are TEXT, it is unlikely that they will be equal to a single word, so you might want to try

 where (
       projekte.foerder_de like '%rahmen%' 
    or projekte.foerder_en like '%rahmen%' 
    or projekte.foerder_dritte like '%rahmen%'
       )

tip: don't use || and &&, use the standard sql keywords AND and OR

tip: use singlequote to delimit strings, not doublequote



rudy

http://r937.com/

SQL Consulting

 

[bateman23]

Thanks a lot

---------------------------------------
Visit me @:

http://www.daniel-purucker.de/