[RESOLVED] Query Question III

fontener

ok so I have this:

$query ="SELECT r.*, f.*, m.*, p.*, d.*,  CASE WHEN EXISTS(SELECT * FROM e where e.f_id = r.f_id) THEN 'active' ELSE 'inactive' END AS 'act' FROM r, f, m, p, d WHERE r.f_id = f.f_id AND r.md_id = m.md_id AND r.p_id = p.p_id AND r.pd_id = d.pd_id ?also"...
...
...$case = $row["act"];

when I set ?also=AND act='active' I get an error. what am I doing wrong?

it works when i do ?also=AND f_id='something' for example. just not the act..

fontener

here's the error if it matters any...

Error performing query: Unknown column 'act' in 'where clause'

bradgrafelman

Try removing the quotes around act in the query; column aliases, just like normal identifiers, can not be surrounded in quotes (that would make them a string, not an identifier).

fontener

nope. still nothing.. I can sort by act... just cant get it to work with the ?also=AND act='active'

Lars_Berg

if you move the query into a derived table , you can use the correlation names from the innner query in the where clause of the outer query.

select * 
  from (select r.*, 
               f.*, 
               m.*, 
               p.*, 
               d.*,  

               case when exists (select * 
                                  from  e 
                                  where e.f_id = r.f_id) then 'active' else 'inactive' end as act 
          from r, f, m, p, d 
         where r.f_id = f.f_id 
           and r.md_id = m.md_id 
           and r.p_id = p.p_id 
           and r.pd_id = d.pd_id) dt
 where 1 = 1

fontener

Great! thanks. Another question.. what's the best way to have a variable in the WHERE clause without having to have your link be like <a href="?link="AND this=a AND that=b">show only this and that</a>

bradgrafelman

<a href="search.php?this=a&that=b">Search</a>

You'd then be able to sanitize $GET['this'] and $GET['that'] just like any other string data in order to prevent SQL injection attacks.

fontener

Lars Berg;10948304 wrote:

if you move the query into a derived table , you can use the correlation names from the innner query in the where clause of the outer query.

select * 
  from (select r.*, 
               f.*, 
               m.*, 
               p.*, 
               d.*,  

               case when exists (select * 
                                  from  e 
                                  where e.f_id = r.f_id) then 'active' else 'inactive' end as act 
          from r, f, m, p, d 
         where r.f_id = f.f_id 
           and r.md_id = m.md_id 
           and r.p_id = p.p_id 
           and r.pd_id = d.pd_id) dt
 where 1 = 1

I've tried this and all I get is "Error performing query: Duplicate column name f_id":queasy:

Lars_Berg

Could you post your real query.