MySQL - using a foreach of sorts in an SQL Procedure

Question

I've been doing a lot of searching for some way to create a "Foreach" of sorts with a MySQL query.

I know how I'd go about this if it were PHP or Java, but I want this to be all MySQL.

So, I'm going to create a sort of 'theoretical' MySQL situation, since the original query has a lot of unnecessary information that I feel confuses the scope of the question.

What I'm envisioning is something that works in this sort of way (This will be an ugly mess of php and mysql pseudo code.)

@rooms = ('master_bedroom'=>'MBS_MASTER_BEDROOM_SIZE',
'bed_room2' = 'B2S_BR_2_SIZE')
 FOREACH(@rooms as @room=>@column_name)
 {
      UPDATE `relative_room_sizes` SET area = CALCULATE_AREA(`buildings`.@column_name)
      WHERE `idx_computed_values`.`room_type` = @room 
      AND `idx_computed_values`.`computed_size` = 'large'
 }

Am I just taking things too far, or is there some very basic feature of MySQL that I'm just having a lot of trouble discovering? I did a lot of searching here but any sort of foreach tends to relate back to using an actual programming language to take care of this sort of dirty work.

Edit: Here's kind of the idea of what I want to achieve in the procedure, but I did it in PHP because... it's what I know :)... Essentially I want to be able have a procedure that does what this PHP code will output, and hopefully in just as compact a manner.

<?php
$rooms = array(
'master_bedroom' => 'MBS_MASTER_BEDROOM_SIZE',
'bed_room2' => 'B2S_BR_2_SIZE',
'bed_room3' => 'B3S_BR_3_SIZE',
'bed_room4' => 'B4S_BR_4_SIZE',
'living_room' => 'LRS_LIVING_ROOM_SIZE',
'dining_room' => 'DRS_DINING_ROOM_SIZE ',
'kitchen' => 'KTS_KITCHEN_SIZE',
'family_room' => 'FRS_FAMILY_ROOM_SIZE');

$room_sizes = array('large','small');
$table_with_properties = 'idx_search';

?>
<?foreach($rooms as $room_type=>$column_name):?>
    <?foreach ($room_sizes as $room_size):?>
    #Initialize field in case it isn't already declared
    INSERT INTO `idx_computed_values` (`room_type`,`computed_size`,`area`)
    values ('<?=$room_type?>','<?=$room_size?>',0)
    ON DUPLICATE KEY UPDATE `computed_size` = `computed_size`;

    UPDATE `idx_computed_values` SET area = 
    (SELECT AVG(CALCULATE_AREA(`<?=$table_with_properties?>`.<?=$column_name?>))<? if($room_size == 'large'):?>+<?php endif;?><? if($room_size == 'small'):?>-<?php endif;?> 
         (0.6 * STDDEV(CALCULATE_AREA(`idx_search`.<?=$column_name?>))) 
         FROM `<?=$table_with_properties?>`
         WHERE `<?=$column_name?>` IS NOT NULL )
    WHERE `idx_computed_values`.`room_type` = '<?=$room_type?>'
    AND `idx_computed_values`.`computed_size` = '<?=$room_size?>';

    <? endforeach;?>
<? endforeach;?>

Francois Deschenes · Answer 1 · 2011-06-22T05:28:24.183

1

It sounds like you want to create a stored procedure. Assuming that the "array" comes from the result of a query, you'll have to use cursors. MySQL's documentation has an example you can refer to at http://dev.mysql.com/doc/refman/5.0/en/cursors.html or http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html.

edited Jun 22 '11 at 05:28

answered Jun 22 '11 at 04:48

Francois Deschenes

24,816
4
64
61

Actually, the more I think about it... yes this is exactly a stored procedure. But my main issue is that the array actually comes from a bunch of preset column names. I actually created 100% PHP code to do my idea which I'm going to update my question with, but yeah, for some reason I wasn't exactly viewing this as a procedure. – Anther Jun 22 '11 at 05:02
@Anther - I would have provided an example but it's so specific to your database design (i.e. I would have needed to know the field types) so I thought I'd point you in the right direction. – Francois Deschenes Jun 22 '11 at 05:03
@Francois I updated my question to make it a little bit more vidid of what I'm trying to achieve, and maybe you'll come back and tell me it is indeed Cursors that I want to be looking at, but I feel like it's a bit more simple than that. --- I'm actually thinking it may be best to just have the PHP execute this directly if I can't figure out a way to do this in SQL without copy and pasting a lot of the statements. – Anther Jun 22 '11 at 05:12
@Anther - That's slightly different than what I expected. MySQL doesn't have an "array" data type that a stored procedure can accept. You could still create a stored procedure but it's probably not worth the effort (let alone it's something else to maintain and being database agnostic does have it's benefits). Just out of curiosity, why are you looking for a 100% SQL alternative to PHP? – Francois Deschenes Jun 22 '11 at 05:33
@Francois Well, the original solution to this problem was going to be a lot more databaseally(yes I know I made up a word)-- complicated than the current query and I'm just making an assumption here, but I think my boss didn't want the PHP server to hang on a long running process. I'm not sure if that's a true problem but it's essentially why I'm going through what I'm going through right now. So... if it comes down to it it might just end up being run with slight modifications to that small PHP script. – Anther Jun 22 '11 at 05:42
@Anther - I doubt that a 100% MySQL solution would be that much faster. You'll save a few round trips but that about it. You could use prepared statements (using `mysqli_prepare` for instance) or you could create a stored procedure to which you pass the parameters inside the `foreach` loops (no need for cursors here). The alternative would be to find a way to pass an array to a MySQL procedure (MySQL doesn't have an array data type) or perhaps do something that loops through the information_schema's columns table and does the math although you'd probably be missing the sizes (i.e. small). – Francois Deschenes Jun 22 '11 at 06:07

score 1 · Accepted Answer · edited Sep 09 '17 at 00:56

MySQL does have looping constructs if you define them in a stored procedure, as defined here: For loop example in MySQL

However SQL looping constructs are not MySQL's strong point because they are not easy to read and are relatively inefficient compared to when loops are done elsewhere like in Java/Python/PHP.

SQL is best used for when the data structure used to represent the data is appropriately vectorized, and you can perform operations simply, such as: update stuff from mytable where column = 'foobar' joining tables on keys if needed.

If you need looping constructs in SQL, then either the data is not laid out correctly in the database or the task you're trying to do is so complex that it can't be represented intuitively in SQL.

MySQL - using a foreach of sorts in an SQL Procedure

2 Answers2