My SQL Tips

1 REMAP VALUES INSIDE A QUERY USING CASE…WHEN SYNTAX

 

SELECT id,title,
   (CASE date WHEN '0000-00-00' THEN '' ELSE date ENDAS date
   FROM your_table
SELECT id,title,
   (CASE status WHEN THEN 'open' WHEN THEN 'close' ELSE 'standby' ENDAS status 
   FROM your_table

2 FIND DUPLICATE RECORDS WITH EMAIL FIELD

SELECT email, COUNT(email) AS q
   FROM emails_table GROUP BY email HAVING q > 1 
   ORDER BY DESC

3 EXTRACT RECORDS WITH A RANDOM ORDER

  SELECT * FROM your_table ORDER BY RAND()

4 RESET THE AUTOINCREMENT COUNTER IN A TABLE

ALTER TABLE your_table AUTO_INCREMENT = 100

Next record you insert will have id=100.

5 ADD AN AUTOMATIC INCREMENT COLUMN FOR A SELECT

set @N = 0;
SELECT @N := @N +1 AS number, value FROM table;

6 JOINING FIELDS WITH CONCAT FUNCTION

   SELECT CONCAT(name,’ ‘,surname) AS complete_name FROM users

7 SELECT PARTIAL DATE VALUES WITH DATE FUNCTIONS

SELECT id,title, YEAR(date_field) FROM your_table
SELECT id,title,
   CONCAT(MONTH(date_field),'/',YEAR(date_field)) as new_date
   FROM your_table

8 INSERTING ROWS IGNORING DUPLICATES ON A FIELD WITH UNIQUE KEY

On a table “people” with a unique key on ‘name’ field:

INSERT IGNORE INTO people (name) VALUES (‘john’)

 

You can run this query many times, no error will be returned.

9 USING FULLTEXT INDEX AND MATCH AGAINST SEARCH

To work, this tip, need to add the full text index on the content_column. Note that if you already have a table filled with data, adding the index will not create it… so you have to start from an empty table.

10 HOW TO SAY “ONE MONTH AGO” IN MYSQL

SELECT usercount(*) AS logins
   FROM stat_log
   WHERE action='LOGIN' AND dt_when >= DATE_ADD(CURDATE(), INTERVAL -1 MONTH)
   GROUP BY user

This where clause with dt_when lets you count the records that has date greater or equal to one month ago.

11 SET CORRECT CHARSET

1
SET NAMES 'utf8';

Run this query after your connection starts. More info about charsets:

How to get correct characters :

 

  1. create fields on db with utf8_general_ci collation

  2. make the query ‘set names’ after any connection (look the block code below, you need mysql 5)

  3. put meta charset utf8 tag in the html head block

  4. read and write strings to db and to html without using utf8_encode or other functions

 

after connecting to your db make this query:
mysql_query("SET NAMES 'utf8';");

Sometimes these steps do not work, and you start say f**ck f**ck! Well, it’s also happened to me, and I’ve notice that sometimes php.ini make some problems because it has a wrong default charset setted. So, if this is your problem, put this line at the beginning of your php files:

ini_set('default_charset''UTF-8');

 

 

 

You can leave a response, or trackback from your own site.

Leave a Reply