Jump to content


Photo

MySQL query effciancy


  • Please log in to reply
3 replies to this topic

#1 Pezzoni

Pezzoni
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 25 February 2006 - 02:21 PM

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:

    $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);

    }
    

Thanks.

Dan

#2 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 25 February 2006 - 04:59 PM

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:
<?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);
}
?>


#3 Pezzoni

Pezzoni
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 25 February 2006 - 05:46 PM

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.


#4 hitman6003

hitman6003
  • Members
  • PipPipPip
  • Advanced Member
  • 1,807 posts

Posted 25 February 2006 - 11:15 PM

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

    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);
        }
    }

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 != ""'




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users