add fake colum to query string

[iam4423]

Okay so im not actually sure f this is possible but i see no reason why it wouldnt be.

Basically what im trying to do is create a site wide search function for my cms because i want to be able to sort the data received from multiple tables i have been using a UNION query

Code: Select all

//=====================define tables to be searched=====================
      $sql="SELECT * FROM `".$db_prefx."search` ".$search_group."";
      $query=$pdo->prepare($sql);
      $query->execute();
      if($query->rowCount() < 1) {
         $notice->display('noplugin');
         break;
         }
      $sub_sql='';
      //=====================for each table=====================
      while ($row=$query->fetch()) {
         $sub_params=array();
         $array=explode(' ',$search);
         $where=' ';
         //=====================bulid where clause=====================
         foreach($array as $name => $value) {
            if($where != ' ') {$where.='OR ';}
            $where.="`".$row['search_field']."` LIKE :word".$name." OR `".$row['search_field_2']."` LIKE :word".$name." ";
            $sub_params['word'.$name]="%".$value."%";
            }
         //=====================build query=====================
         if($sub_sql != '') {$sub_sql.=" UNION ";}
         $sub_sql.="(SELECT `".$row['search_url_colum']."` AS `ID`, `".$row['search_field']."` AS `title`, `".$row['search_field_2']."` AS `body`  FROM `".$db_prefx.$row['search_table']."` WHERE ".$where.")";
         }

the search itself works great but the problem is that depending upon what table the data comes from it needs to link to a different file (well actually the same file with a different GET)

the link that i need to use is stored in the search table so what i need to do is add that as a fake field in my query, something like this

Code: Select all

//=====================build query=====================
         if($sub_sql != '') {$sub_sql.=" UNION ";}
         $sub_sql.="(SELECT `".$row['search_url_colum']."` AS `ID`, `".$row['search_field']."` AS `title`, `".$row['search_field_2']."` AS `body` (ADD `url` = ".$row['search_url'].")  FROM `".$db_prefx.$row['search_table']."` WHERE ".$where.")";
         }

Basically add a field caled url that contains the data from $row['search_url']

Any help or even just confirmation of wether or not this is possible would be appreciated

EDIT: annother way to do it would be to add $row['search_url'] to the beginning of $row['search_url_colum']

[NigelRen]

It's quite common to use SQL like this, adding in fields or values from queries.
It's always worth trying some of these things in something like phpmyadmin to test out your ideas, but it's basically something like...

Code: Select all

select 'Field1' as ID, dbValue
    from someTable;


[iam4423]

hmmm that's a lot more simple that i was expecting, it works perfectly.

Thanks a lot this has been doing my head in.

[coxcyre55u]

................................................,Guia WOW ORO

imaginative