On web development, now a days PHP is very popular for making fastest web site developing. this technology is gives better control over MySql database and with combination of both we can create small and large sites as per requirement.
So, why should we wait …just learn from below links..
http://oreilly.com/catalog/9780596005603/
http://www.w3schools.com/PHP/DEfaULT.asP
http://serendipitsolutions.com/vanillaforum/comments.php?DiscussionID=5
STEP -1
Now, I want to know about MySQL Store Proceduer (SP) , Trigger , View etc. available in MySql. So, I am searching many sites for best examples. off course MySql manual is best one for learning above things. but we can learn better with more examples.
For that I have search few sites like..
http://www.devshed.com/c/a/MySQL/Examining-MySQL-50/2/
http://guyh.textdriven.com/MySqlSpp/MyExamples.html
Above both are relay good sites.
As off my learning curve I am posting few things for all MySql DBA. So, If you found any other good example then you are free to post.
From above site I have made one simple SP as below..
DELIMITER $$
DROP PROCEDURE IF EXISTS `mass_testsite`.`testing`$$
CREATE PROCEDURE `mass_testsite`.`testing`(IN par1 VARCHAR(100), OUT par2 VARCHAR(100))
BEGIN
UPDATE adminactivitylog SET userName = ‘Blog’ where userName=par1;
SELECT userName from adminactivitylog where userName = ‘Blog’ INTO par2;
END$$
DELIMITER ;
For Run SP you have use CALL function
For Eg.
SET @par1 = ‘mark’;
call `mass_testsite`.`testing`(@par1, @userName);
SELECT @userName;
Need to learn More….
STEP -2
How to use WHILE….DO…END WHILE?
/*****
WHILE …DO
END WHILE
*****/
DELIMITER $$
DROP PROCEDURE IF EXISTS `db5`.`t3`$$
CREATE PROCEDURE `db5`.`t3`()
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT ’string’*/
BEGIN
DECLARE v INT;
SET v = 0;
WHILE v < 5 DO
INSERT INTO t VALUES(v);
SET v = v + 1;
END WHILE;
END$$
DELIMITER ;
How to use REPEAT….UNTIL…END REPEAT?
/*****
REPEAT;
UNTIL … /*** No semicoloan ****/
END REPEAT;
*****/
DELIMITER $$
DROP PROCEDURE IF EXISTS `db5`.`t3`$$
CREATE PROCEDURE `db5`.`t3`()
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT ’string’*/
BEGIN
DECLARE v INT;
SET v = 0;
REPEAT
INSERT INTO t VALUES(v);
SET v = v + 1;
UNTIL v >= 5 /****** NEED TO CHECK THIS POINT *******/
END REPEAT;
END$$
DELIMITER ;
/*****
LOOP;
END LOOP;
*****/
How to use LOOP…END LOOP?
DELIMITER $$
DROP PROCEDURE IF EXISTS `db5`.`t3`$$
CREATE PROCEDURE `db5`.`t3`()
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT ’string’*/
BEGIN
DECLARE v INT;
SET v = 0;
label_1: LOOP
INSERT INTO t VALUES(v);
SET v = v + 1;
IF v > 5 THEN
LEAVE label_1;
END IF;
END LOOP;
END$$
DELIMITER ;
/*****
ITERATE - Means start loop again.
LEAVE - Break out loop.
*****/
