Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

add fake colum to query string

Codes here !

Moderators: macek, egami, gesf

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

wow goldgoogle-buy

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

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

imaginative
coxcyre55u
New php-forum User
New php-forum User
 
Posts: 1
Joined: Fri Aug 24, 2012 5:31 pm


Return to mySQL & php coding

Who is online

Users browsing this forum: Bing [Bot] and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.