## Wednesday, May 21, 2014

### 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.
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))    BEGINDECLARE 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 NOWDROP 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 equalSET @SQ_SUM =  ( N * (POW(N,2) + 1) ) / 2;-- MIN ValueSET @min=1; -- MAX ValueSET @max=POW(N,2); -- BUILD THE SQUAREWHILE ( @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;-- CONTINUESET @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))    BEGINDECLARE 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 NOWDROP 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 nSET @nXn = N + N;-- SQ_SUM This is the formula for what the total should equalSET @SQ_SUM =  ( N * (POW(N,2) + 1) ) / 2;-- MIN ValueSET @min=1;-- MAX ValueSET @max=POW(N,2);-- insert_optionsSET _io_ =0;-- BUILD THE SQUAREWHILE ( @min <= @nXn ) DO-- TEST VALUESSET _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;-- CONTINUESET @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.

@AnotherMySQLDBA