I got sidetracked today playing with Magic Squares and thought it might be a good chance to give an example of using MySQL Routines , Loops and IF checks.
So If you are unaware of what a Magic Square is I have included a few links. It might save you a Google search but otherwise think Sudoku as an example.
I am passing the value of 4 to the routine to show how to do so. It is an unnecessary step since I am hard coding the square to 4 anyway but it works for the example.
Now that I built out the routine, do I get valid results? Keep in mind this is not ALL of the possible options, just options that might work. 924 options for a 4x4 square exist.
No! They work for the rows but not the columns. While this shows how to use routines, loops and an IF statement, the example fails it it doesn't provide what I wanted. So I had to rework the insert with more of an IF check as well as swap a few numbers at the end.
Now does this work?
OK I cheated a little by just moving the columns around but you get the idea.
So If you are unaware of what a Magic Square is I have included a few links. It might save you a Google search but otherwise think Sudoku as an example.
- http://en.wikipedia.org/wiki/Melencolia_I
- http://en.wikipedia.org/wiki/File:Albrecht_D%C3%BCrer_-_Melencolia_I_%28detail%29.jpg
- http://www.mathematische-basteleien.de/magsquare.htm
- http://mathworld.wolfram.com/MagicSquare.html
- http://quasistoic.org/fun/magicsquare/all.html
I am passing the value of 4 to the routine to show how to do so. It is an unnecessary step since I am hard coding the square to 4 anyway but it works for the example.
DROP PROCEDURE IF EXISTS magic_sq;
DROP TABLE IF EXISTS `magic_sq` ;
delimiter //
CREATE PROCEDURE magic_sq( N int(11))
BEGIN
DECLARE nXn INT;
DECLARE SQ_SUM INT;
DECLARE _passfail_ INT;
DECLARE min INT;
DECLARE max INT;
-- DRAW THE TEMPLATE FOR THE SQUARE MAX of 8 FOR NOW
DROP TABLE IF EXISTS `magic_sq` ;
CREATE TABLE `magic_sq` (
`xy_id` int(11) NOT NULL AUTO_INCREMENT,
`1` int(11) NULL DEFAULT NULL,
`2` int(11) NULL DEFAULT NULL,
`3` int(11) NULL DEFAULT NULL,
`4` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`xy_id`) ,
UNIQUE KEY `Y1` (`1`),
UNIQUE KEY `Y2` (`2`),
UNIQUE KEY `Y3` (`3`),
UNIQUE KEY `Y4` (`4`)
) ENGINE= MEMORY;
-- n X n
SET @nXn = N + N;
-- SQ_SUM This is the formula for what the total should equal
SET @SQ_SUM = ( N * (POW(N,2) + 1) ) / 2;
-- MIN Value
SET @min=1;
-- MAX Value
SET @max=POW(N,2);
-- BUILD THE SQUARE
WHILE ( @min <= @nXn )
DO
-- TEST VALUES
SET _passfail_ = IF ( (@min + (@min +1) + ( @max - 1) +@max) = @SQ_SUM ,1 , 0 ) ;
-- IF VALID RESULTS THEN SAVE THEM
IF _passfail_ = 1 THEN
INSERT INTO magic_sq VALUES (NULL,@min ,(@min +1),( @max - 1) , @max );
END IF;
-- CONTINUE
SET @min= @min +2;
SET @max= @max -2;
END WHILE;
END//
delimiter ;
Now that I built out the routine, do I get valid results? Keep in mind this is not ALL of the possible options, just options that might work. 924 options for a 4x4 square exist.
CALL magic_sq(4);
select * from magic_sq;
+-------+------+------+------+------+
| xy_id | 1 | 2 | 3 | 4 |
+-------+------+------+------+------+
| 1 | 1 | 2 | 15 | 16 |
| 2 | 3 | 4 | 13 | 14 |
| 3 | 5 | 6 | 11 | 12 |
| 4 | 7 | 8 | 9 | 10 |
+-------+------+------+------+------+
+-------+---------+
| xy_id | per_row |
+-------+---------+
| 1 | 34 |
| 2 | 34 |
| 3 | 34 |
| 4 | 34 |
+-------+---------+
DROP PROCEDURE IF EXISTS magic_sq;
DROP TABLE IF EXISTS `magic_sq` ;
delimiter //
CREATE PROCEDURE magic_sq( N int(11))
BEGIN
DECLARE nXn INT;
DECLARE SQ_SUM INT;
DECLARE _passfail_ INT;
DECLARE _io_ INT;
DECLARE min INT;
DECLARE max INT;
-- DRAW THE TEMPLATE FOR THE SQUARE MAX of 8 FOR NOW
DROP TABLE IF EXISTS `magic_sq` ;
CREATE TABLE `magic_sq` (
`xy_id` int(11) NOT NULL AUTO_INCREMENT,
`1` int(11) NULL DEFAULT NULL,
`2` int(11) NULL DEFAULT NULL,
`3` int(11) NULL DEFAULT NULL,
`4` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`xy_id`) ,
UNIQUE KEY `Y1` (`1`),
UNIQUE KEY `Y2` (`2`),
UNIQUE KEY `Y3` (`3`),
UNIQUE KEY `Y4` (`4`)
) ENGINE= MEMORY;
-- n X n
SET @nXn = N + N;
-- SQ_SUM This is the formula for what the total should equal
SET @SQ_SUM = ( N * (POW(N,2) + 1) ) / 2;
-- MIN Value
SET @min=1;
-- MAX Value
SET @max=POW(N,2);
-- insert_options
SET _io_ =0;
-- BUILD THE SQUARE
WHILE ( @min <= @nXn )
DO
-- TEST VALUES
SET _passfail_ = IF ( (@min + (@min +1) + ( @max - 1) +@max) = @SQ_SUM ,1 , 0 ) ;
-- IF VALID RESULTS THEN SAVE THEM
IF _passfail_ = 1 THEN
IF _io_ = 0 THEN
INSERT INTO magic_sq VALUES (NULL,@min ,(@min +1),( @max - 1) , @max );
SET _io_ =1;
ELSEIF _io_ = 1 THEN
INSERT INTO magic_sq VALUES (NULL,( @max - 1),@max , @min , (@min +1) );
SET _io_ =2;
ELSEIF _io_ = 2 THEN
INSERT INTO magic_sq VALUES (NULL,@max ,(@min +1) , ( @max - 1) , @min );
SET _io_ =4;
ELSEIF _io_ = 4 THEN
INSERT INTO magic_sq VALUES (NULL, (@min +1) , @max , @min ,( @max - 1) );
SET _io_ =0;
END IF;
END IF;
-- CONTINUE
SET @min= @min +2;
SET @max= @max -2;
END WHILE;
SELECT @x3y2 := `2` FROM magic_sq WHERE xy_id = 3;
SELECT @x3y3 := `3` FROM magic_sq WHERE xy_id = 3;
SELECT @x4y2 := `2` FROM magic_sq WHERE xy_id = 4;
SELECT @x4y3 := `3` FROM magic_sq WHERE xy_id = 4;
UPDATE magic_sq SET `2` = @x4y3 , `3` = @x4y2 WHERE xy_id = 3;
UPDATE magic_sq SET `2` = @x3y3 , `3` = @x3y2 WHERE xy_id = 4;
select * from magic_sq;
select SUM(`1`),SUM(`2`),SUM(`3`),SUM(`4`) from magic_sq;
select xy_id, SUM(`1` +`2` +`3` + `4`) as per_row from magic_sq GROUP BY xy_id;
END//
delimiter ; Now does this work?
CALL magic_sq(4);+-------+------+------+------+------+
| xy_id | 1 | 2 | 3 | 4 |
+-------+------+------+------+------+
| 1 | 1 | 2 | 15 | 16 |
| 2 | 13 | 14 | 3 | 4 |
| 3 | 12 | 7 | 10 | 5 |
| 4 | 8 | 11 | 6 | 9 |
+-------+------+------+------+------+
4 rows in set (0.22 sec)
+----------+----------+----------+----------+
| SUM(`1`) | SUM(`2`) | SUM(`3`) | SUM(`4`) |
+----------+----------+----------+----------+
| 34 | 34 | 34 | 34 |
+----------+----------+----------+----------+
1 row in set (0.22 sec)
+-------+---------+
| xy_id | per_row |
+-------+---------+
| 1 | 34 |
| 2 | 34 |
| 3 | 34 |
| 4 | 34 |
+-------+---------+
