SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Gothenburg, Sweden
    Posts
    135
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Slow query, fast html loading?

    Hey all,

    I'm using php and mysql for a web application. I have a form where users can enter their personal information (like firstname, lastname and so on).

    When first entering the personal information page the information that the user has already supplied is read from the database and outputted to the relevant fields in the form.

    When the user changes a field to a different value and then submits it and the form will reload itself it will read the old database values instead of the new data inserted. I tried using the sleep(5); function in php right after the UPDATE statement to let the database have enough time to do the update but to no avail.

    I'm thinking that when I'm first fetching the current personal information it's getting a cached query?

    I have no idea what to do here. Can anyone please help me?

    Sincerely,
    Peter

  2. #2
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Gothenburg, Sweden
    Posts
    135
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is SELECT SQL_NO_CACHE the answer to my problems? It seemed to help....

    Or maybe not.. didn't seem to have an effect at all...

  3. #3
    SitePoint Enthusiast
    Join Date
    Jan 2007
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is likely a problem with the php code rather than the DB. You definitely not need any sleep() or anything because the UPDATE statement will not return before it has completed all it needs to do.

    Maybe if you posted part of the relevant code and queries it might be easier to get some help.

  4. #4
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Gothenburg, Sweden
    Posts
    135
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll post the whole part of the code to not miss out anything important:

    PHP Code:
    <?php

    /***********************
    Page: personal-settings.php
    Function: Edit the users personal settings
    Last modified: 070730 01:10
    By: Peter
    Reason: Added IM field
            Added Website and Date of birth fields
    ToDo:
    ************************/

    $sidId "settings";
    $membershiprequired TRUE;
    require_once(
    "db.inc");
    require_once(
    "page-display.inc");
    require_once(
    "text.inc");
    require_once(
    "profile.inc");
    require_once(
    "register.inc");

    $sidNamn "Personal Settings";

    $userid $_SESSION['uid'];

    $sql "SELECT avatar, dob, firstName, lastName, im, presentation, country, website, email, showEmail, commentView
            FROM Users, UserSettings 
            WHERE Users.userid = 
    $userid
            AND UserSettings.userid = 
    $userid";

    $r_recordset $db->query($sql);
    $userinfo $r_recordset->fetchRow();

    $showemail $userinfo->showemail;

    if(
    $showemail == 0) {
        
    $emailchecked "";
    } else {
        
    $emailchecked " checked=\"checked\"";
    }

    if(isset(
    $userinfo->im)) {
        list(
    $dbIMService$dbIMUser) = explode(": "$userinfo->im);
    }

    if (isset(
    $_POST['cancel'])) {
        
    $host  $_SERVER['HTTP_HOST'];
        
    $uri  rtrim(dirname($_SERVER['PHP_SELF']), '/\\');
        
    $extra 'index.php';
        
    header("Location: http://$host$uri/$extra");
        exit;
    }

    if (isset(
    $_POST['submit-image'])) {

        
    checkAvatarUpload($_FILES$db);
        
    } else if(isset(
    $_POST['submit-reset'])) {
        
        
    $avatar $userinfo->avatar;
        
    $defaultpath "/img/avatar-empty.gif";

        if(!
    preg_match("/.*img\/avatar-empty.gif/",$avatar)) {
            
    unlink("/home/proj5/html/peter/v1" $avatar);
        }
        
        
    $sql "UPDATE Users SET avatar = " $db->quote($defaultpath) . " WHERE userId = " $db->quote($userid);
        
    $result $db->query($sql);
        
        if(
    PEAR::isError($result)) {
            
    $e_reset_image "<p class=\"error-msg\">There was a problem resetting your avatar. Please try again later.</p>";
        } else {
            
    $s_reset_image "<p class=\"success-msg\">Avatar successfully reset.</p>";
        }
        
    } else if (isset(
    $_POST['save'])) {
        
        
    $firstname $_POST['firstname'];
        
    $lastname $_POST['lastname'];
        
    $day $_POST['day'];
        
    $month $_POST['month'];
        
    $year $_POST['year'];
        if(
    $day == "Day") {
            
    $day "00";
        } 
        if (
    $month == "Month") {
            
    $month "00";
        }
        if (
    $year == "Year") {
            
    $year "0000";
        }
        
    $dob $year "-" $month "-" $day;
        
    $imuser $_POST['im-user'];
        
    $imservice $_POST['im-service'];
        
    $email $_POST['email'];
        if(isset(
    $_POST['showemail'])) {
            
    $showemail $_POST['showemail'];
        } else {
            
    $showemail 0;
        }
        if(empty(
    $showemail)) {
            
    $showemail "0";
        }
        
    $oldpassword $_POST['oldpassword'];
        
    $password1 $_POST['password1'];
        
    $password2 $_POST['password2'];
        
    $presentation $_POST['presentation'];
        
    $website $_POST['website'];
        
    $country $_POST['country'];
        if(
    $country == "Not chosen") {
            
    $country "";
        }
        
        
    checkFirstName($firstname);
        
    checkLastName($lastname);
        
        if(
    $month != "00" || $day != "00" || $year != "0000") {
            
    checkDob($month$day$year);
        }
        
        
    checkIm($imuser$imservice);
        
        if(
    $email != $userinfo->email) {
            
    checkEmailProfile($email$oldpassword$db);
        }
        
        if(!empty(
    $password1) || !empty($password2)) { 
            
    checkPasswordProfile($password1$password2$oldpassword$db);
        }
        
        
    checkPresentation($presentation);
        
        if(
    $website != "http://") {
            
    checkUrlProfile($website);
        }
        
    $website $_POST['website'];
        
        if(
    $website == "http://") {
            
    $website "";
        }

        if (
    $error == 0) {

            
    $s_update_profile "<p class=\"success-msg\">Your settings were successfully updated.</p>";
            
            
    $firstname $db->quote($firstname);
            
    $lastname $db->quote($lastname);
            
    $dob $db->quote($dob);
            
    $formattedIM $db->quote($formattedIM);
            
    $email $db->quote($email);
            
    $showemail $db->quote($showemail);
            
    $passwordMd5 $db->quote(md5($password1));
            
    $presentation $db->quote($presentation);
            
    $website $db->quote($website);
            
    $country $db->quote($country);
            
    $userid $db->quote($userid);
            
            if(!empty(
    $password1)) {
                
    $sql "UPDATE Users SET firstName = $firstname, lastName = $lastname, dob = $dob, im = $formattedIM, presentation = $presentation, country = $country, website = $website, email = $email, password = $passwordMd5
                        WHERE userId = 
    $userid";

                
    $db->query($sql);
                
                
    $sql "UPDATE UserSettings
                        SET showEmail = 
    $showemail
                        WHERE userId = 
    $userid";
                        
                
    $db->query($sql);            
            } else {
                
    $sql "UPDATE Users SET firstName = $firstname, lastName = $lastname, dob = $dob, im = $formattedIM, presentation = $presentation, country = $country, website = $website, email = $email
                        WHERE userId = 
    $userid";

                
    $db->query($sql);
                
                
    $sql "UPDATE UserSettings
                        SET showEmail = 
    $showemail
                        WHERE userId = 
    $userid";
                        
                
    $db->query($sql);
            }
        } else {
            if(
    $error 1) {
                
    $errors "<em>($error errors)</em>";
            } else {
                
    $errors "<em>($error error)</em>";
            }    
        }
    }

    if(!
    $_SERVER['QUERY_STRING'] == "") {
       
    $_SESSION['allgetvars'] = "?" $_SERVER['QUERY_STRING'];
    } else {
       
    $_SESSION['allgetvars'] = "";
    }

    include(
    "../v1/header.php");
    ?>

        <div class="profile-nav">
            <ul>
                <?php printCategories($category,$sidId,$db);?>
            </ul>
        </div>

        <div class="heading">
            <h2>Edit Your Settings <?php        
    if ($error 0) {
        echo 
    $errors;
    }        
    ?></h2>
            <p>All fields marked with an asterisk (<em>*</em>) are required fields.</p>
        </div>
    </div>

    <div id="contain">

        <div id="main-content">
            <form id="settings-form" action="/settings/" method="POST">
                <?php echo $s_update_profile?>
                <fieldset>
                    <legend>Personal Information</legend>
                    <div>
                        <label for="first-name">First Name</label>
                        <input id="first-name" name="firstname" type="text" maxlength="50" value="<?php if(isset($_POST['firstname'])) { echo $_POST['firstname']; } else if(isset($userinfo->firstname)) { echo $userinfo->firstname; } ?>">
                        <?php 
                        
    if (isset($_POST['save']) && isset($e_firstname_length)) {
                            echo 
    $e_firstname_length
                        }
                        
    ?>
                        <label for="last-name">Last Name <em></em></label>
                        <input id="last-name" name="lastname" type="text" maxlength="50" value="<?php if(isset($_POST['lastname'])) { echo $_POST['lastname']; } else if(isset($userinfo->lastname)) { echo $userinfo->lastname; } ?>">
                        <?php 
                        
    if (isset($_POST['save']) && isset($e_lastname_length)) {
                            echo 
    $e_lastname_length
                        }
                        
    ?>
                    </div>
                    <div>
                        <label for="dob">Date of Birth</label>
                        <?php 
                        printDob
    ($_POST['day'], $_POST['month'], $_POST['year'], $userinfo->dob); 
                        if (isset(
    $_POST['save']) && isset($e_dob)) {
                            echo 
    $e_dob
                        }
                        
    ?>
                        <label for="country">Country</label>
                        <?php
                        printCountries
    ($_POST['country'], $userinfo->country);
                        
    ?>
                        </div>
                </fieldset>
                
                <fieldset>
                    <legend>Write a Presentation</legend>

                        <label for="presentation">Your Presentation</label>
                        <textarea name="presentation" id="presentation" rows="100" cols="80"><?php if(isset($_POST['presentation'])) { echo $_POST['presentation']; } else if(isset($userinfo->presentation)) { echo $userinfo->presentation; } ?></textarea>
                        <?php 
                        
    if (isset($_POST['save']) && isset($e_presentation_length)) {
                            echo 
    $e_presentation_length
                        }
                        
    ?>
                        <p><strong>Note:</strong> No HTML is allowed in your presentation.</p>
                </fieldset>
                
                <fieldset>
                    <legend>Contact Information</legend>

                    <div>
                        <label for="email">Email Address <em>*</em></label>
                        <input id="email" name="email" type="text" maxlength="255" value="<?php echo $userinfo->email?>">
                        <?php 
                        
    if (isset($_POST['save']) && isset($e_email_registered)) {
                            echo 
    $e_email_registered
                        } else if (isset(
    $_POST['save']) && isset($e_email)) {
                            echo 
    $e_email;
                        }
                        
    ?>
                    </div>
                    <div>
                        <input type="checkbox" name="showemail" id="showemail" value="1"<?php if($_POST['showemail'] == "1") { echo " checked=\"checked\""; } else { echo $emailchecked; }?>>
                        <label for="showemail" class="row">Show my e-mail in profile and let other users search by my e-mail</label>
                        <p><strong>Note:</strong> You will need to enter your password below if you want to change your e-mail address.</p>
                    </div>

                        
                    <div>
                        <label for="website">Website</label>
                        <input id="website" name="website" type="text" maxlength="255" value="<?php if(isset($_POST['website'])) { echo $_POST['website']; } else if(isset($userinfo->website)) { echo $userinfo->website; } else { echo "http://"; } ?>">
                        <?php 
                        
    if (isset($_POST['save']) && isset($e_url_profile)) {
                            echo 
    $e_url_profile
                        }
                        
    ?>

                        <label for="im-user">Instant Messaging Username</label>
                        <input id="im-user" name="im-user" type="text" maxlength="50" value="<?php if(isset($_POST['im-user'])) { echo $_POST['im-user']; } else if(isset($dbIMUser)) { echo $dbIMUser; } ?>">
                        <?php 
                        
    if (isset($_POST['save']) && isset($e_im_user)) {
                            echo 
    $e_im_user
                        }
                        
    ?>
                        
                        <label for="im-service">Instant Messaging Service</label>
                        <?php
                        printIMServices
    ($_POST['im-service'], $dbIMService);
                        if (isset(
    $_POST['save']) && isset($e_im_select)) {
                            echo 
    $e_im_select
                        }
                        
    ?>
                    </div>
                    
                </fieldset>
                
                <fieldset>
                    <legend>Your Password</legend>
                        <label for="oldpassword">Current Password</label>
                        <input id="oldpassword" name="oldpassword" type="password" maxlength="255" value="">
                        <?php 
                        
    if (isset($_POST['save']) && isset($e_wrong_old)) {
                            echo 
    $e_wrong_old;
                        }
                        
    ?>

                        <p><strong>Note:</strong> If you change your e-mail, you will need to provide your current password to save the new e-mail address.</p>
                        <label for="password1">New Password</label>
                        <input id="password1" name="password1" type="password" maxlength="255" value="">

                        <label for="password2">Confirm New Password</label>
                        <input id="password2" name="password2" type="password" maxlength="255" value="">
                        <?php 
                        
    if (isset($_POST['save']) && isset($e_password)) {
                            echo 
    $e_password;
                        } else if (isset(
    $_POST['save']) && isset($e_nomatch)) {
                            echo 
    $e_nomatch;
                        }
                        
    ?>
                        <p><strong>Note:</strong> To change your password, enter your current password above, and then your new password.</p>
                </fieldset>
                <fieldset>
                    <legend>Save New Settings?</legend>
                    <input id="save-profile" type="submit" name="save" value="Save">
                    <input id="cancel-profile" type="submit" name="cancel" value="Cancel">
                </fieldset>
            </form>
            
            <form id="avatar-form" action="" method="post" enctype="multipart/form-data">
                <fieldset>
                <legend>Manage Your Avatar</legend>
                <div>
                    <label for="input-image">Choose an Image File</label>
                    <input id="input-image" type="file" name="image">
                    <?php
                    
    if (isset($_POST['submit-image']) && $error != 0) {
                        if (isset(
    $e_choose)) {
                            echo 
    $e_choose;
                        } else if (isset(
    $e_filesize)) {
                            echo 
    $e_filesize;
                        } else if (isset(
    $e_image)) {
                            echo 
    $e_image;
                        } else if (isset(
    $e_extension)) {
                            echo 
    $e_extension;
                        }
                    } else if (isset(
    $_POST['submit-image']) && $error == 0) {
                        if (isset(
    $success)) {
                            echo 
    $success;
                        } else if (isset(
    $failure)) {
                            echo 
    $failure;
                        }
                    } else if (isset(
    $_POST['submit-reset']) && isset($e_reset_image)) {
                        echo 
    $e_reset_image
                    } else if (isset(
    $_POST['submit-reset']) && isset($s_reset_image)) {
                        echo 
    $s_reset_image;
                    }
                    
    ?>
                    <p><strong>Tips:</strong> Your avatar should be a jpeg, png or gif (no animated gifs) and must be less than 100 kb. The avatar will be resized to 48 by 48 pixels.</p>
                </div>
                <div>
                    <input type="submit" name="submit-image" value="Upload Avatar">
                    <input type="submit" name="submit-reset" value="Reset to Default">
                </div>                
                </fieldset>
            </form>
        </div>

    </div>
    </div>

        <?php
        
    include("../v1/footer.php");
    ?>

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    dude, you're reading the data BEFORE you do the update... so you're going to get the data BEFORE the update.

  6. #6
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Gothenburg, Sweden
    Posts
    135
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dude thanks!

    How would I rearrange the code for it to work properly then?


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
  •