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

Second Highest Value SQL query

What is the simplest SQL Query to find the second largest value?

Answer :

select max(FIELDNAME) 
   from TABLENAME
      where FIELDNAME not in 
   (select max(FIELDNAME) from TABLENAME);

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

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    |
+-----------+------+