Archive : MySQL

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 [...]

Consider the following diagram. It’s a map of the flow of data from your user, which eventually makes its way into the database. Since we’re getting input from a user, and they’re the one that can fix invalid data, we validate data at the top layer. There’s usually no getting around this. In fact, for the best user experience on the web, you’re going to perform some JavaScript validation. [...]

Incorrect usage of UNION and ORDER BY A common problem when using the UNION command in MySQL is when you want to order the sets of data differently. If you do it incorrectly, you will see the error message: Incorrect usage of UNION and ORDER BY To solve the problem, wrap each individual SELECT statement in parenthesis as follows: (SELECT col1,col2,col3 FROM table1) UNION (SELECT cola,colb,colc FROM [...]

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 [...]