Questions #Tagged [sql]

Questions: 4

How can we use find_in_set() function with multiple search string in mysql?

In MySQL find_in_set() would not be able to perform multiple search. Below trick can be followed to achive the same:<br> <pre><xmp>If the requirement is to find all the records having 1,3 below code snippet will work: CONCAT(',', tags, ',') REGEXP ',(1,3),' Whereas if the requirement is to find all the records having either 1 or 3 below code snippet will work: CONCAT(',', tags, ',') REGEXP ',(1|39),'</xmp></pre>

 

#mysql     #sql    

How to get num_rows value from result set of sql query in php?

In PHP mysqli_num_rows() function is an inbuilt function which is used to return the number of rows present in the result set. It is generally used to check if data is present in the database or not. Below is the syntax for this function:<br> <pre><xmp>mysqli_num_rows ( $result );</xmp></pre>

 

#php     #mysql     #sql    

How to select the distinct values and relative count along with sorting from data that is stored as comma separated values in MySql?

Below is the sql query that will bring the desired output:<br> <pre><xmp>SELECT val, COUNT(*) as cnt FROM ( SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.val, ',', n.n), ',', -1) val FROM table t CROSS JOIN ( SELECT a.N + b.N * 10 + 1 n FROM (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b ORDER BY n ) n WHERE n.n <= 1 + (LENGTH(t.val) - LENGTH(REPLACE(t.val, ',', ''))) and val <> '' ) T GROUP BY val ORDER BY cnt DESC</xmp></pre>

 

#mysql     #sql    

How to use limit and order by clause in same sql query?

Limit clause is use to limit the number of records that we get in the select query whereas order by is use to get the records in some specific order either ascending or descending order depending on the requirements of the dataset. Below is the code snippet to show how both can be used together:<br> <pre><xmp>Select * FROM table where val <> '' and FIND_IN_SET('1',val) > 0 order by creation_date desc limit 0,30</xmp></pre>

 

#mysql     #sql