CodePuzzling::tumblr('!');

A view fro MySQL triggers

Here it is a small view to see all triggers stored in the DBMS. IMHO it’s more usable than going into INFORMATION_SCHEMA database and browse TRIGGERS records.

CREATE VIEW `List_triggers` AS
SELECT `TRIGGER_NAME` AS `Name`, `TRIGGER_SCHEMA` AS `Database`,
	   `EVENT_OBJECT_TABLE` AS `Table`,
	   CONCAT_WS(" ", ACTION_TIMING, EVENT_MANIPULATION) AS `Action`
FROM `INFORMATION_SCHEMA`.`TRIGGERS`

[MySQL] How to create a random day for a date

If you have extracted with PHP or a SQL function a date (“YYYY-MM-DD”) and you want to change the day with a random day, here is it a series of SELECT for doing it:

SELECT date FROM (
-- generates a number x between 1 and 27
	SELECT @rand:=(
		SELECT FLOOR(1 + (RAND() * 27))
	) as rand, 
		    DATE_ADD('2006-05-01',INTERVAL @rand DAY) as date
) as t; -- give an alias, otherwise it's uncorrect

[MySQL] How to clean URL with a function

Suppose that you have a table with a column named “referer” which stores referring urls and suppose that you want to update this column by cutting off everything but the domain and subdomain. Here it is a small function that does the job:

delimiter //
drop function if exists sub_domain;
CREATE FUNCTION sub_domain (url text(1000)) RETURNS CHAR(100)
BEGIN
DECLARE str1 varchar(10);
DECLARE str2 varchar(10);
SET str1="http://";
SET str2="https://";
RETURN if(substring_index(substring_index( substring_index(REPLACE(url,str2,str1), '/',3),str1,-1),'.',-1) != 'com', substring_index( substring_index( substring_index(REPLACE(url,str2,str1), '/',3),str1,-1),'.',-3), substring_index( substring_index( substring_index(REPLACE(url,str2,str1), '/',3),str1,-1),'.',-3));
END;
//

UPDATE `mytable` 
SET `referer` = (SELECT sub_domain(`referer`));

[MySQL] How to swap columns values in a table

I find a very useful webpage on how to swap columns values in MySQL. In that article are presented three methods, of which I’ve chosen the following one which makes use of a temporary variable:

UPDATE table
SET x=(@temp:=x), x = y, y = @temp 
WHERE condition....

[SQL] How to concatenate a string with a result of a query

SELECT CONCAT('my',(SELECT string
                   FROM table
                   WHERE condition = 1)) as aName

How to export to an array a MySQL query with 3 lines of code

$result = mysql_query("SELECT * FROM table");
for($i = 0; $array[$i] = mysql_fetch_assoc($result); $i++) ;
array_pop($array); // removes last empty array

Array
(
    [0] => Array
        (
            [id] => 1
            [user] => myuser
            [pass] => mypass
            ... other fields
        )
    ... and so on

[SQL] How to compare two SQL schema with sqlfairy

sqlt-diff -d -c schema_to_update.sql=MySQL updated_schema.sql=MySQL 2>/dev/null|grep -v "DROP TABLE"

• -d: prints debug info;
• -c: case insensitive;
• 2>/dev/null: don’t print warning and error messages so you can grep results data;
• grep -v “word”: don’t print lines that match the pattern specified between quotes

Which quotes have more than X services?

SELECT id, COUNT(productid) as numProd 
FROM `vtiger_inventoryproductrel`
WHERE id IN (SELECT quoteid FROM vtiger_quotes, vtiger_crmentity
                      WHERE quoteid = crmid AND deleted = 0)
GROUP BY id 
HAVING numProd > 4