MySQL Magic Square 4x4 : Routines, Loop , Variables

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.

• 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

Again this is more of an example of how to use Routines & Loops and an IF Check, so I have limited the magic square to a 4x4 square.
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 |
+-------+---------+



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.

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 |
+-------+---------+
 

OK I cheated a little by just moving the columns around but you get the idea.