add fake colum to query string

Codes here !

Moderators: macek, egami, gesf

iam4423
New php-forum User
New php-forum User
Posts: 50
Joined: Fri Jan 20, 2012 8:00 am

add fake colum to query string

Postby iam4423 » Sat Aug 18, 2012 8:05 am

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
php-forum Active User
php-forum Active User
Posts: 450
Joined: Fri Aug 05, 2011 9:53 am

Re: add fake colum to query string

Postby NigelRen » Sat Aug 18, 2012 10:38 am

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
New php-forum User
New php-forum User
Posts: 50
Joined: Fri Jan 20, 2012 8:00 am

Re: add fake colum to query string

Postby iam4423 » Sat Aug 18, 2012 12:24 pm

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
New php-forum User
New php-forum User
Posts: 1
Joined: Fri Aug 24, 2012 5:31 pm

wow goldgoogle-buy

Postby coxcyre55u » Mon Aug 27, 2012 9:31 pm

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

imaginative


Return to “mySQL & php coding”

Who is online

Users browsing this forum: No registered users and 1 guest

cron