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`

[PHP] Resources for programming with PDO

I’ve asked Twitter user what kind of PHP class would have used for a project and I was (and I am) in doubt between MDB2 (a PEAR Abstraction layer class) and mysqli extension. Someone has pointed out PDO, so I’m collecting some links to see what does this extension do:

PDO on PHP web site: surely the first place to look at;
Introduction to PDO on PHPPro: a big article which illustrates how to connect to different DBMS;
Prepared statement with PDO
Using PDO Objects in PHP 5, Handling Result Sets and More with PDO Objects in PHP 5 and Working with Prepared Queries with PDO Objects in PHP 5 all by Alejandro Gervasio;
First steps with PDO;
Fetching data with PDO;
Developing MySQL Database Applications With PHP Part 3: Using the PDO Extension With MySQL Driver, pdo_mysql;
PDO: The underestimated way to connect to your database;

[MySQL] How to install mytop on CentOS 5.3

For the ones who don’t know what mytop is, it’s a simple perl script that shows mysql’s porcesses list (SHOW PROCESSLIST) like top does for system’s processes. First of all, let’s assure what distro you’re using by typing:

[root@localhost ~]# cat /etc/issue
CentOS release 5.3 (Final)
Kernel \r on an \m

Then let’s import GPG key:

rpm --import http://dries.ulyssis.org/rpm/RPM-GPG-KEY.dries.txt

Then we download mytop through rpm command (the link is shortened through bit.ly, if rpm doesn’t follow redirect, use normal url) and install via yum:

rpm -Uhv http://bit.ly/DVVs1
yum install rpm

Then you can use an alias in your bashrc file for not inserting all the times user and password:

vim ~/.bashrc
alias mytop='mytop -uuser -ppass'

.

[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 check if a value is a number

Here it is a function for checking if a field value represent a number. Floating point and negative numbers are also considered.

CREATE FUNCTION IsNumeric (sIn varchar(1024)) RETURNS tinyint
RETURN sIn REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';

Via: Stack Overflow.

[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;
//

You can use it in this way:

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

From: MySQL substring syntax doc page

[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

If the subquery returns a string, you can use safely CONCAT operator to join the strings:

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

You can use CONCAT_WS if you want to specify a separator for the strings.

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

By reading a good contribution on PHP site, I discovered this method for exporting to an array a query submitted to MySQL:

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

It produces:

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

That’s to say you’ll have count($array) records, of which every row has count($array[0]) fields.

[PHP-MySQL] MySQL transaction with PHP

// require MySQL Version 4.0.12 using InnoDB type tables.
@mysql_connect("localhost","username", "password") or die(mysql_error());
@mysql_select_db("test") or die(mysql_error());
$query = "INSERT INTO trans (id,item,quantity)
		 values (null,'Baseball',4)";
begin(); // transaction begins
$result = @mysql_query($query);
if(!$result) {
	rollback(); // transaction rolls back
	echo "you rolled back";
	exit;
} else {
	commit(); // transaction is committed
	echo "Aggiornamento completato";
}

via DevArticles