How many mysql queries is too many?

PHP
1

I’m working on creating a mass update feature for records in the database of an app I’ve created. The script takes all the information from certain columns in a table places it in a form so the user can go through and update multiple records, click one update button and have all their changes committed to the database. The script works but I’m worried that it creates too many db queries. A db update is created for each field. So if there are 200 rows and 20 fields the script will create 4000 update queries. Here you can see what I’m doing:

The HTML is created for each field. The “name” is the field name in the db and the number at the end is the db id and the “value” is what is stored in the db. So in this example, there would be 20 fields with id 120.

<input type="text" name="first_name-120" size="40" value="Ann" />

This is the PHP that handles the form submit. It takes everything submitted, splits out the db id, and creates a new array with the id, the field name, and the value for the database. Then it places those values into an update query. You can see it does this for each field.


// Handle the update
if (isset($_POST['submitted'])) {
	unset($_POST['TableData_length']);
	unset($_POST['update-fields']);
	unset($_POST['submitted']);
	
	foreach ($_POST AS $k => $v) {
		// Get the child_id off the form name and create a new array
		$kv = explode('-', $k);
		// Add the field value to the array
		array_push($kv, $v);
		$qpost = "UPDATE children SET {$kv[0]} = '{$kv[2]}' WHERE child_id = '{$kv[1]}'";
		$rpost = mysqli_query($dbc, $qpost);
	}
}

What I wanted to do but just can’t figure how to do it is to take everything with the same id number and place it into the same update. Then, in this example, I would go from 4000 updates to 200 with. What would be even better is to only submit fields that have changed. Again, I’m not sure how I would do that.

2

"How many mysql queries is too many? "

The general answer is: Two is too many, for any given function.

“What I wanted to do but just can’t figure how to do it is to take everything with the same id number and place it into the same update.”

Exactly the correct thought.
So here’s how i’d do it, in pseudocode.

FOREACH submitted field:
Figure out the ID (hint: [FPHP]explode[/FPHP], [FPHP]end[/FPHP])
Add to an array, key ID, new element: posted_key, posted_value
ENDFOREACH
FOREACH formerly mentioned array:
Start Query String
FOREACH item of that key
Add to Query the key/update value
ENDFOREACH
End Query String, Using Where with ID
Execute Query
ENDFOREACH

This will reduce your 4000 queries to 200.

3

Thanks for the help. I’m not sure exactly how to do everything you said, here is what I have so far. I know I’m making some mistakes I just don’t know where. I’m still learning.


// Handle the update
if (isset($_POST['submitted'])) {
	unset($_POST['TableData_length']);
	unset($_POST['update-fields']);
	unset($_POST['submitted']);
	
	foreach ($_POST AS $k => $v) {
		// Remove the child_id from the form name
		$keys = explode('-', $k);
		$child_id = end($keys);
		$field_name = $keys[0];
		
		$posted[$child_id] = array('child_id' => $child_id, 'field' => $field_name, 'value' => $v);
		
	}
	
	foreach($posted AS $post) {
		$qpost = "UPDATE children SET ";
			
			foreach ($post AS $id) {
				$qpost .= "{$id['field']} = '{$id['value']}, ";
			}
			
		$qpost = substr($qpost, 0, -2); // take off the last comma and space
		$qpost .= " WHERE child_id = '{$post['child_id']}";
		
		echo $qpost;
	}
}

I get this error: /child_bulk.php’ on line 33: Uninitialized string offset:

this is line 33


$qpost .= "{$id['field']} = '{$id['value']}, ";
4

You’re actually pretty darn close. Try this:
$posted[$child_id] = array(‘child_id’ => $child_id, ‘field’ => $field_name, ‘value’ => $v);

becomes:

    $posted[$child_id][] = array('field' =&gt; $field_name, 'value' =&gt; $v);

Note the extra array declaration. We’re making a three-dimensional array here. (child_id,#,key/value).

Use the key value of the first Foreach loop to assign the child_id in the query string.

5

Thank you so much! I was thinking it had something to do with that but I just couldn’t wrap my head around it. I had one other problem in the last part of the query where I had $post[‘child_id’] and it needed to reference the array from the previous FOREACH so it needed to be $id[‘child_id’] and now it works perfectly. Feels good to have this worked out. I’ve been wrestling with it all weekend!