Archive : Tutorial

Database size SELECT table_schema AS 'Db Name', Round( Sum( data_length + index_length ) / 1024 / 1024, 3 ) AS 'Db Size (MB)', Round( Sum( data_free ) / 1024 / 1024, 3 ) AS 'Free Space (MB)' FROM information_schema.tables GROUP BY table_schema ;

You have photos (id INT, photo BLOB, tally INT) and votes(id INT, userID INT, photoID INT)tables. You wish to update photos.tally values from counts per photo in the votes table. You can use a cursor to walk the photos table, updating the tally as you go: DROP TABLE IF EXISTS photos; CREATE TABLE photos (id INT, photo BLOB, tally INT); INSERT INTO photos VALUES(1,'',0),(2,'',0); DROP TABLE IF EXISTS VOTES; CREATE TABLE VOTES( userID INT, photoID INT); INSERT INTO votes VALUES (1,1),(2,1),(2,2); DROP PROCEDURE IF EXISTS updatetallies; DELIMITER // CREATE PROCEDURE updatetallies() BEGIN DECLARE done INT DEFAULT 0; DECLARE pid INT; DECLARE cur1 CURSOR FOR SELECT id FROM photos; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN cur1; FETCH cur1 INTO pid; WHILE done = 0 DO UPDATE photos SET tally = (SELECT COUNT(*) FROM votes WHERE photoid = pid ) WHERE id = pid; FETCH cur1 INTO pid; END WHILE; CLOSE cur1; SELECT id,tally FROM photos; END // DELIMITER ; CALL updatetallies(); +------+-------+ | id   | tally | +------+-------+ |    1 |     2 | |    2 |     1 | +------+-------+ but [...]

Here is mysql query , for retrieving the previous and next column values in a sequence, given a particular column value thisvalue The previous value is the maximum value less than thisvalue, and the next value is the minimum value greater than thisvalue: For Ex. We will get The Next and Previous ( ID =2 ) from MyTbl. SELECT IF(ID > 2,'next','prev') AS Direction, IF(ID > 2,MIN(ID),MAX(ID)) [...]

MySQL remove html code from data .. here ‘s My Functions: SET GLOBAL log_bin_trust_function_creators=1; DROP FUNCTION IF EXISTS fnStripTags; DELIMITER | CREATE FUNCTION fnStripTags( Dirty varchar(4000) ) RETURNS varchar(4000) DETERMINISTIC BEGIN DECLARE iStart, iEnd, iLength int; WHILE Locate( '<', Dirty ) > 0 And Locate( '>', Dirty, Locate( '<', Dirty )) > 0 DO BEGIN [...]

MySQL is currently the most popular open source database server in existence. On top of that, it is very commonly used in conjunction with PHP scripts to create powerful and dynamic server-side applications. MySQL has been criticized in the past for not supporting all the features of other popular and more expensive DataBase Management Systems. However, MySQL continues to improve with each release [...]