Loop within loop/query within query best practices

[msaz87]

Best practices question:

 

Is it bad for scalability to have a query (say an UPDATE or an INSERT) within a loop -- or is it only bad for scalability to have one loop running within another? Are there any occasions when this is acceptable?

 

Thanks for the guidance

[fenway]

Too general.

[msaz87]

Too general.

 

Sorry, I thought there might be a general answer to that practice... but I'll elaborate:

 

My site will offer users the ability to construct forms and then manipulate the results. So these forms can have as many fields as they want and however many results they want. Then this will scale with however many users sign up for it. In the code right now, I have occasions where a MySQL loop is running and within that loop, another MySQL query is executed... for instance there's a function to copy a form they have already set up, so the loop runs through a list of fields and the query within that loop INSERTS that data into the new field's table.

 

Other spots, there's a MySQL loop within another MySQL loop... such as when viewing the results of a form. The first loop calls all the fields for that form, then the loop within calls the data inputted for said field.

 

My concern is whether this practice will put too much strain on the database when scaled. Right now, it works fine, but if there's 10, 20, 100 users accessing it, will it be too bogged down?

 

Hopefully that makes more sense... thanks for the help

 

 

[Muddy_Funster]

"inputted"....Really? :-\

 

I have only come accross a single instance where I absoloutly had to run a SELECT loop from within a SELECT loop, and that was when performing a cross refference lookup between MySQL and MS SQL Server.  Neither of the scenarios that you have posted requre nested loops.

 

Generaly, if you are working on the same server for all your queries then your likelyhood of requering nested loops is slim.  Avoid the practice where and when you can.

[fenway]

Give me an example of such "loops".

[msaz87]

Give me an example of such "loops".

 

My apologies if I'm not using the proper verbiage. Here's an example of a nested loop... I'm sure there's a way to do it with a join or a subquery, but my MySQL experience is limited.

 

Here's the first portion on the page which pulls the details of that form's fields and how many columns were set up:

<?php

$fields_query	= mysql_query("	SELECT field, col_name 
			FROM reg_fields 
			WHERE form_id	= '$form_id' 
			AND sortable	= '1' 
			ORDER BY rank ASC") or die(mysql_error());

$cn	= 1;				

while($row = mysql_fetch_array($fields_query)) {

$col_names[$cn]	= $row['col_name'];

$cn++

} ?>

 

And here's the portion that actually outputs the results, the first query pulling all the submission IDs and then the nested query pulling each column's result using the submission ID as the key:

<?php 

if(!empty($search_keyword)) {

$results_text	= "	SELECT submission_id 
			FROM reg_form_$form_id 
			WHERE $search_field LIKE '%$search_keyword%' ";

} else {

$results_text	= "	SELECT submission_id 
			FROM reg_form_$form_id ";

}			

if(!empty($sort_by)) {

$results_text	.= "ORDER BY ".$sort_by." ";

} else {

$results_text	.= "ORDER BY submission_id ASC ";					

}

if($submissions_count > $limit) {

$results_text	.= "LIMIT ".$start_at.", ".$limit;

}

$results_query	= mysql_query($results_text) or die(mysql_error());

$rowclass	= 0;

while($row = mysql_fetch_array($results_query)) { 

$submission_id	= $row['submission_id'];

?>

<tr class="row<?php echo $rowclass ?>">

	<td><?php echo $submission_id ?></td>

	<?php foreach($col_names as $key => $value) { ?>

		<?php

		$data_query	= mysql_query("	SELECT $value 
						FROM reg_form_$form_id 
						WHERE submission_id = '$submission_id'") or die(mysql_error());

		while($row = mysql_fetch_array($data_query)) { 

			$data	= stripslashes($row[$value]);				

		}	

		?>

		<td><?php echo $data ?></td>

	<?php } ?>

</tr>

<?php $rowclass = 1 - $rowclass; } ?>

 

Thanks for the help

[fenway]

Sorry, I don't follow -- you have dynamic table names?

 

how is the related to the previous query's output?

[msaz87]

It's dynamic because each user can create as many forms as they'd like... so in one part of the site they can create Form A, then later Form B, C, etc. and part of the process is they define what fields go into each of these forms, which is also a variable number that they dictate. When the form is created, it creates a new table in sequential order (e.g. reg_form_1, reg_form_2, etc.)

 

When the user wants to view the results of their forms, they'll see a list of all the forms they've created and when they click on the one they want to see, that form's ID is passed on to the next page as $form_id.

 

So since the names and number of fields in each form is different, that's why I did the nested queries to begin with... the first one pulling all the fields/columns for that particular form, then the nested one pulling each field's/column's results.

 

Does that make sense?

[fenway]

Not really -- seems like a DBA nightmare -- are you actually manipulating each "piece" individually?

[msaz87]

I'm not sure what you mean by "piece" but let me try and go into a little more detail...

 

A user creates a new form and it's inserted into table "reg_forms" -> the key from this is used to create a new table "reg_form_[key]"

 

The user then creates whatever fields for said form they want... for instance they might create a first name, last name and e-mail field. Each one would be inserted into the "reg_fields" table, using its key to name the column and then insert a new column into the "reg_form_[key]" table with the matching "col_[key]" name from "reg_fields"

 

After the user sets their form up on their site, a person can submit it and the data is inserted into "reg_form_[key]"

 

So when the user goes into my site to view the submissions, this is where the nested loops come into play. One loop pulls all the columns from "reg_fields" and puts them into an array for use later. Then the first loop pulls every submission_id from "reg_form_[key]" and the nested loop within that uses the array and the submission_id to pull that row's data and output it for viewing.

 

Not sure if that's any clearer...

 

[fenway]

What are they doing with each individual value in each column?  I'm trying to figure out if you actually need them separated or not.

[msaz87]

The data is just being viewed in tabular format... it's manipulated later if the user so desires, but on a row-by-row basis. This particular question deals with the high-level view. This is the output:

 

 

But right now this utilizes the nested loops since the number of columns is variable.

[fenway]

If it's on a row-by-row basis, then you don't need to store each column individually -- that will simplify this problem immeasurably.

[msaz87]

I was able to ditch the nested loop... I think I just made it too complicated in my head and couldn't see through the fog.

 

Is there ever a time when a nested loop is acceptable? Or is there pretty much always a more efficient way of handling things?

 

Thanks for the help!

[fenway]

It usually indicates a design flaw.

[msaz87]

And what about running an UPDATE/INSERT/DELETE query within a MySQL loop? Say someone did:

 

$query = mysql_query(" SELECT * FROM table_name WHERE column_1 = 'something'") or die(mysql_error());

while($row = mysql_fetch_array($query)) {

  mysql_query("UPDATE table_name SET column_2 = '".$row['column_3']."'") or die(mysql_error());

}

 

Is this a design flaw as well and too hard on the database when scaling or is it permissible?

[Muddy_Funster]

I have had to use a SELECT within a SELECT only when doing a comparison filter between MySQL and Microsoft SQL Server.  As for the UPDATE loop you posted - you have a bit of a missunderstanding about how that would work.

[fenway]

And what about running an UPDATE/INSERT/DELETE query within a MySQL loop? Say someone did:

 

$query = mysql_query(" SELECT * FROM table_name WHERE column_1 = 'something'") or die(mysql_error());

while($row = mysql_fetch_array($query)) {

  mysql_query("UPDATE table_name SET column_2 = '".$row['column_3']."'") or die(mysql_error());

}

 

Is this a design flaw as well and too hard on the database when scaling or is it permissible?

You can combine those into a single statement.