How to hack mysql group concat to fetch a limited number of rows


 

Limit record in group_concat + mysql

SELECT SUBSTRING_INDEX(GROUP_CONCAT(Field1 SEPARATOR ‘,’), ‘,’, [# of elements to return]) FROM TABLE;

Below SQL Query is for fetch subcategory value in a single field using “GROUP_CONCAT”.
if you want fetch limited no. of record then you can use

SUBSTRING_INDEX(GROUP_CONCAT(Field1 SEPARATOR ','), ',', 
  [# of elements to return])

SQL Query for display 4 subcategory list in a single query :

SELECT a.*, 
SUBSTRING_INDEX(GROUP_CONCAT(cast( b.uid AS char )), ',',4)
   as subcategories,
SUBSTRING_INDEX(GROUP_CONCAT(b.title SEPARATOR '#'), '#',4) 
  as subcategories_name
FROM `tx_jsproduct_category` AS a
LEFT JOIN `tx_jsproduct_category` AS b ON b.parent_id = a.uid
WHERE a.deleted =0
AND a.hidden =0
AND b.deleted =0
AND b.hidden =0
AND a.uid IN ( 1, 3, 2, 4  )
GROUP BY (
a.uid
)
ORDER BY  field( a.uid, 1, 3, 2, 4 )
LIMIT 0 , 30

Note:

SUBSTRING_INDEX(GROUP_CONCAT(b.uid SEPARATOR ','), ',',4) as 
  subcategories

4 is stand for limited record

Best Luck 🙂

If you have any query then feel free to contact me at Jainish Senjaliya

Advertisements

Speed up Typo3 – Performance Tips


If your Tyop3 installation is a bit slow the following tips my help to speed up you site.

  • Use a php cache module
  • Adjust the caching parameter of you mysql Installation.
    For me these setting helps for a performance boost
    key_buffer =32M
    table_cache = 128
    query_cache_size = 64M
    query_cache_type = 1
    log-bin
  • Try this Typo3 extension: nc_staticfilecache
OR

To speed up the render process of Typo3 of you may try this extension: nc_staticfilecache

This extension will cache each rendered page of Typo3 into a static file. When a user tries to access this page the next time the page is delivered directly.
Of course the extension takes care of the cache timeout configuration and pages which contains plugins which need to render each time.
It will reduce the server load of a Typo3 installation a lot. Just give it a try!

If you have any query then feel free to contact me at Jainish Senjaliya

Find distance between two zip codes OR Find distance between two latitude and longitude


latitude = 51.237159

longitude =  6.737445

SELECT zipcode, city, country, ( 6371 * acos( cos( radians( latitude ) ) * cos( radians( latitude_field_name ) ) * cos( radians( longitude_field_name ) – radians( longitude ) ) + sin( radians( latitude ) ) * sin( radians( latitude_field_name ) ) ) ) AS distance
FROM table_name

zipcode city country latitude longitude distance
40547 Düsseldorf Germany 51.237159 6.737445 0
40570 Dusseldorf Germany 51.198531 6.735061 4.29844576108689
41464 Neuss Germany 51.186067 6.692292 6.49371210989321
41564 Kaarst Germany 51.215095 6.636164 7.46730953495452
41468 Neuss Germany 51.169475 6.748088 7.56255673166281
40625 Düsseldorf Germany 51.234292 6.861356 8.63270963507461
40599 Düsseldorf Germany 51.177089 6.867331 11.2467526264088
47809 Krefeld Germany 51.331328 6.626299 13.0152304362707
40593 Düsseldorf Germany 51.156245 6.885656 13.6968761245336

If you have any query then feel free to contact me at Jainish Senjaliya

You could specify columns in ORDER BY clause, using their position number


select * from fe_user where uid in (“3,5,1″) order by field(uid,”3,5,1”) ;

You will get out put of above query in order by uid.

+-----------+------+
| name      | uid  |
+-----------+------+
| Dhara     | 3    |
| Jainish   | 5    |
| Senjaliya | 1    |
+-----------+------+