Skip to main content

Mysql function: GROUP_CONCAT() (Like implode() in php)

Today i have to solve a problem in MySql , The problem is i have two table in openCart
1. category_description
category_id language name
1 1 category 1
2 1 category 2
3 1 category 3
4 1 category 4
5 1 category 5
6 1 category 6
7 1 category 7
8 1 category 8
1. product_category
produc_idcategory_id
11
12
15
21
23
24
32
36
 The resulting data should be like that
produc_idcategory_name
1category 1,category 2,category 5
2category 1,category 3,category 4
3category 2,category 6

I can do tha by in php by taking the category name to it's respective product id and implode them , but it will take much time and processor power cos the i have to do query every product id separately, I was searching the right query in MySql and found the function   GROUP_CONCAT() http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

SELECT pc.product_id,GROUP_CONCAT(DISTINCT cd.name) category_name FROM product_to_category pc LEFT JOIN category_description cd ON (pc.category_id=cd.category_id) WHERE cd.language_id =1 GROUP BY pc.product_id And made the query i was looking for and solved my problem :)

Mysql function: GROUP_CONCAT()  (Like implode() in php)
Labels:

Comments

Post a Comment

Popular posts from this blog

OpenCart : Route

In open cart the routeing technique is very simple, If we wan  to call a module or controller /catalog/controller/product/category.php the we have to call it like 'index.php?route=product/product'. Here product is the folder name category is the controller file name without the file extension.   'index.php?route=product/product'  it will execute the   public function index(){     ...  }  'index.php?route=product/product/menthod' then is ill call    public function method(){     ...  } the method should be public otherwise the system will not be able to call it we will know more about that later.
1 comment
Read more

OpenCart : Create a Model

To make  a Model   class Model<foldername><filename with out extention> extends Model{       public function methode(){     .....     }  }  We can call a model from controller using  $this->load->model('<folder>/<filename with out extention>'); And after that we can call the method like below $this->model_<folder>_<filename with out extention>->methode(); The Method should be public
1 comment
Read more

PHP : Creating a CSV file as download file from array

Few days earlier I need to Create a text Or SCV file what will return some of the datafeed. Condition is i can't save the file in the serve i have to create and make to download for the user. So i made the below function   function genarateFeedTextFile($data=array(),$file_name=''){ $text=''; $temp=array(); if($data){ foreach($data as $value){          $text .= implode("\t",$value)."\n"; } } header("Content-Type: text/plain"); header("Content-disposition: attachment; filename=".$file_name.".txt");  header("Content-Transfer-Encoding: binary");  header("Pragma: no-cache");  header("Expires: 0"); echo $text; } This function solved my problem. But soon i have a little more problem. Some of my data contain new line "\n" or "\r" to tab "\b" so the result was not expected so i made a new function and puss all the string...
Post a Comment
Read more