SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Jan 2007
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How many mysql queries is too many?

    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.
    HTML Code:
    <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.

    PHP Code:
    // 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. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    "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: explode, end)
    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.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  3. #3
    SitePoint Member
    Join Date
    Jan 2007
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    PHP Code:
    // 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($qpost0, -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
    PHP Code:
    $qpost .= "{$id['field']} = '{$id['value']}, "

  4. #4
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    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' => $field_name, 'value' => $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.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  5. #5
    SitePoint Member
    Join Date
    Jan 2007
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •