MySQL query effciancy

[Pezzoni]

I've just written a PHP/MySQL script which updates various (and potentially numerous) database rows. The only way I can think of doing this is through a looping query, but as we all know, this is a Bad Thing™, so I'd like an alternate way to work this, preferably with a minimum number of queries!
Unfortunately, I'm a bit stuck as to how to do this.
The script as it is:

[code]    $sql = 'SELECT '.PROFILE_FIELDS_TABLE.'.profile_id, '.PROFILE_FIELDS_TABLE.'.profile_name, '.PROFILE_FIELDS_TABLE.'.profile_description
            FROM '.PROFILE_FIELDS_TABLE.'';
    $fields = $db->db_query($sql);

    $profile_fields_user = array();

    foreach ($fields as $field){

        if(!empty($_POST[$field['profile_id']])){

            $field['value'] = $_POST[$field['profile_id']];

        }
        else {

            $field['value'] = '';

        }

        $sql2 = 'UPDATE '.USER_PROFILE_FIELDS_TABLE.'
                SET profile_value = "'.$field['value'].'"
                WHERE user_id = "'.$auth->userdata['user_id'].'" AND profile_id = "'.$field['profile_id'].'"';
        $db->db_action($sql2);

    }
    [/code]

Thanks.

Dan

[wickning1]

I don't think you can avoid the loop on that one. If you're using mysqli you can use prepared statements to cache the query with MySQL and get a speed boost. You can even build it into that database class you're using. That's what I did with mine.

You could also use mysqli_multi_query() to send them all at once, again mysqli only.

I cleaned up the PHP a little bit so I could actually read what was going on:
[code]<?php
$sql = 'SELECT profile_id, profile_name, profile_description FROM '.PROFILE_FIELDS_TABLE;
$fields = $db->db_query($sql);

foreach ($fields as $field) {
    $field['value'] = $_POST[$field['profile_id']];
    
    $sql2 = 'UPDATE '.USER_PROFILE_FIELDS_TABLE.
            ' SET profile_value = "'.$field['value'].'"    
            WHERE user_id = "'.$auth->userdata['user_id'].'"
            AND profile_id = "'.$field['profile_id'].'"';
    $db->db_action($sql2);
}
?>[/code]

[Pezzoni]

I'm not using MySQLi unfortunately, so it looks like I may have to stick with the loop.
Fortunately it's not a frequently accessed part of the project.

Thanks.

[hitman6003]

Assuming that the post values have numeric keys (for the profile ids) you can do something like this:

[code]    foreach ($_POST as $key => $value) {
        if (is_numeric($key) && $value != "") {
            $query = 'UPDATE '.USER_PROFILE_FIELDS_TABLE.'
                            SET profile_value = "'.$value.'"
                              WHERE user_id = "'.$auth->userdata['user_id'].'"';
            $db->db_action($query);
        }
    }[/code]

It would eliminate a lot of your queries...you wouldn't be doing one even for the empty values...unless you want to updated with empty values, then just remove the '&& $value != ""'