#31
  1. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,904
    Rep Power
    1045
    No, I'm talking about the column definition.

    The stand column is declared as NOT NULL (without a default value). I did this assuming that every member has a stand. However, you don't set any stand upon registration, which means MySQL tries to set it to NULL -- which clashes with the NOT NULL constraint.

    You either have to remove the NOT NULL constraint. Then you're allowed to insert NULL. Or you need to actually set a stand when a new user registers. But you cannot insert NULL into a NOT NULL column.
  2. #32
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    311
    Rep Power
    8
    ok done
  4. #33
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    311
    Rep Power
    8
    Jacques1, could you please give me an example of how, and when to use the query given in #21?
  6. #34
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    311
    Rep Power
    8
    I got this now, I now only have to fill in the comments in the file, the errors:

    PHP Code:
    <?php 

        
    require("common.php"); 
        require(
    "lib/functions.php");
        
        if(empty(
    $_SESSION['user'])) 
        { 
            
    header("Location: login.php"); 
            exit;
        } 
        
    $user_data_stmt $db->prepare('  
        SELECT 
            clan 
            , stand  
        FROM 
            users  
        WHERE 
            user_id = :user_id  
        '
    ); 
        
    $user_data_stmt->execute(array( 
            
    ':user_id' => $_SESSION['user']['user_id'
        )); 
        
    $user_data $user_data_stmt->fetch(); 
        if ( 
    $user_data    && $user_data['clan'] && ($user_data['stand'] == || $user_data['stand'] == 2) ) {             
            if(!empty(
    $_POST)) {
                if (isset(
    $_POST['action_token']) && isset($_SESSION['action_token']) && $_POST['action_token'] === $_SESSION['action_token']) 
                {
                    
    $member_clan_stmt $db->prepare('
                        SELECT clan
                        FROM users
                        WHERE user_id = :user_id
                    '
    );
                    
    $member_clan_stmt->execute(array(
                        
    ':user_id' => $_POST['user_id']
                    ));
                    
    $member_clan $member_clan_stmt->fetchColumn();
                    if(
    $member_clan == $user_data['clan']) {
                        if(
    $_POST['action'] === "Kick")
                        {
                            
    // initialise 'kickscript'                    
                            
    $user_data_stmt $db->prepare('  
                                SELECT 
                                    clan 
                                    , stand  
                                FROM 
                                    users 
                                WHERE 
                                    user_id = :user_id  
                            '
    ); 
                            
    $user_data_stmt->execute(array( 
                                
    ':user_id' => $_POST['user_id'
                            )); 
                            
    $user_data $user_data_stmt->fetch(); 
                            if ( 
    $user_data    && $user_data['clan'] && $user_data['stand'] != 1  ) {

                                
    $kick_stmt $db->prepare('
                                        UPDATE users
                                        SET clan = NULL,
                                            request = 0,
                                            stand = NULL
                                        WHERE user_id = :user_id
                                        '
    );
                                
    $kick_stmt->execute(array(
                                    
    ':user_id' => $_POST['user_id']
                                ));             
                            }

                        }
                        elseif(
    $_POST['action'] === "Change Functions")
                        {
                            
    // initialise 'function changing script'
                           
    foreach ($_POST['stand'] as $user_id => $stand) {
                                if(
    $user_data['stand'] == 1)
                                {
                                    if(
    $stand == 1)
                                    {
                                        
    // error message, confirm changing $_SESSION['user']['username'] for $user_id (search with a query)
                                    
    }
                                    if(
    $stand == 2)
                                    {
                                        
    $underboss_check_stmt $db->prepare('
                                            SELECT user_id, username
                                            FROM users
                                            WHERE clan = :clan
                                                AND request = 0
                                                AND stand = 2
                                        '
    );
                                        
    $underboss_check_stmt->execute(array(
                                            
    ':clan' => $user_data['clan']
                                        ));
                                        
    $underboss_check $underboss_check_stmt->fetch();
                                        if(
    $underboss_check)
                                        {
                                            
    // error message, confirm changing $underboss_check['username'] for $user_id (search with a query)
                                        
    }
                                        else{
                                            
    $update_stand_stmt $db->prepare('
                                                UPDATE users
                                                SET stand = :stand
                                                WHERE user_id = :user_id
                                                '
    );
                                            
    $update_stand_stmt->execute(array(
                                                
    ':stand' => $stand,
                                                
    ':user_id' => $user_id
                                            
    ));
                                        }
                                    }
                                    if(
    $user_id === $_SESSION['user']['user_id'] && $stand != 1)
                                    {
                                        
    $message "It is impossible to do this.";
                                    }
                                }
                                if(
    $user_data['stand'] == 2)
                                {
                                    if(
    $stand == 2)
                                    {
                                        
    // error message, confirm changing $_SESSION['user']['username'] for $user_id (search with a query)
                                    
    }
                                }
                                
    $update_stand_stmt $db->prepare('
                                    UPDATE users
                                    SET stand = :stand
                                    WHERE user_id = :user_id
                                    '
    );
                                
    $update_stand_stmt->execute(array(
                                    
    ':stand' => $stand,
                                    
    ':user_id' => $user_id
                                
    ));
                            }
                            
    $message "The functions are changed.";
                        }
                    }           
                }
                else
                {
                    echo 
    'invalid submission'
                    
    trigger_error('possible CSRF attack'E_USER_ERROR);    // add details for logging like the user ID, the referrer (as a possible source of the attack) etc. 
                    
    exit; 
                }
            }
        
            
    $clan_members_stmt $db->prepare('
                SELECT user_id, username, stand
                FROM users
                WHERE clan = :clan
                    AND request = 0
                '
    );
            
    $clan_members_stmt->execute(array(
                
    ':clan' => $user_data['clan']
            ));
            
    $clan_members $clan_members_stmt->fetchAll();
            echo 
    "<br>";
        }
        else
        {
            
    header("Location: clan_list.php");
            exit; 
        }
        
        
    ?>
    <html>
        <body>
            <?php echo $message "<br>"?>
            <table> 
                <tr> 
                    <th>Username</th>
                    <th>Function</th>
                    <th>Kick</th>
                </tr> 
                <form action="clan_leader_members.php" method="post">
                    <input type="hidden" name="action_token" value="<?php echo html_escape($_SESSION['action_token']) ?>"> 
                    <?php foreach($clan_members as $clan_member): ?> 
                        <tr>                                                   
                            <td><a href="http://www.domain.com/profile.php?user=<?php echo $clan_member['username'];?>"><?php echo htmlentities($clan_member['username'], ENT_QUOTES'UTF-8'); ?></a></td>
                            <?php if($user_data['stand'] == || ($user_data['stand'] == && $clan_member['stand'] != 1)): ?>
                                <td>
                                    <select name=<?php echo "stand[" $clan_member['user_id'] . "]";?>>                                                          // output will be something like "$_POST[stand][5284]"
                                        <?php if($user_data['stand'] == 1) {
                                            echo 
    '<option value="1"';
                                            if(
    $clan_member['stand'] == 1) echo "selected";
                                            echo 
    ">Leader</option>"
                                        }
                                        
    ?>
                                        <option value="2" <?php if($clan_member['stand'] == 2) echo "selected";?>>Underboss</option>
                                        <option value="3" <?php if($clan_member['stand'] == 3) echo "selected";?>>bankkeeper</option>
                                        <option value="4"  <?php if($clan_member['stand'] == 4) echo "selected";?>>recruiter</option>
                                        <option value="5"  <?php if($clan_member['stand'] == 5) echo "selected";?>>member</option>                                                                                    
                                    </select>
                                </td>
                                <?php if($clan_member['stand'] != 1): ?>
                                    <td>
                                        <input type="hidden" name="user_id" value=<?php echo $clan_member['user_id'];?>>
                                        <input type="submit" name="action" value="Kick">                                               
                                    </td>
                                <?php endif; ?>
                            <?php endif; ?>
                        </tr> 
                    <?php endforeach; ?>
                    <input type="submit" name="action" value="Change Functions">
                </form>
            </table> 
        </body>
    </html>
  8. #35
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    311
    Rep Power
    8
    ok almost done, but the only thing I now need is the message. The message has to occur at the points in the script where I have put the comments. The message will be something like "This user will replace the other one" (just like I said in the script). I think it might be done in Javascript as a popup error but I don't know how.

    Please help.
  10. #36
  11. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,904
    Rep Power
    1045
    You still need to replace the duplicate statements and the hard-coded stand IDs.

    Remove the $user_data_stmt in line 41 - 52. Replace the $member_clan_stmt with the query from #21. You should also rename it to something like $member_data_stmt. Then change the other code accordingly (replace the stand IDs with their names and change the variable names).

    Using JavaScript for the errror message seems like a bad idea, because if the user has turned it off, the site becomes unusable. Important functionalities should be done with plain HTML.

    Simply make another site with the message and a form containing all necessary data (action, user_id, stand, ...) plus an additional "confirmed" parameter as hidden fields. When the user submits the form, the data goes back to your current script, and you go through all checks for "Change Functions" again. But this time, the "confirmed" parameter is supposed to actually trigger the action.
  12. #37
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    311
    Rep Power
    8
    are you sure I'll have to replace lines 41-52 with #21? It is different, because the code in lines 41-52 are there to know what clan and stand the user has... Didn't you mean I should change the stationary code (leader, underboss etc. in the html) with those variables?

    And with site you mean file I hope
    Last edited by derplumo; July 6th, 2013 at 03:42 PM.
  14. #38
  15. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,904
    Rep Power
    1045
    Originally Posted by derplumo
    are you sure I'll have to replace lines 41-52 with #21?
    No. You remove those lines. You delete this piece of code.

    Then you go to line 28 and replace the query with the one from line #21.

    The thing is that you look up the user data twice, which is unnecessary and bloats the code. In your current code, you first fetch the stand (line 28). And then you fetch the stand again and the clan (line 41). Simply fetch both in line 28 with the query I gave you and get rid of the second query altogether.



    Originally Posted by derplumo
    And with site you mean file I hope
    Yes. In German, a page is called "Seite", which is also pronounced similar to "site", so we tend to confuse them.
  16. #39
  17. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    311
    Rep Power
    8
    ok, but why do we need (at that place) the code from #21? Sorry for being so annoying but I now don't know why I would use that code there

    If it is for the <option> and <select> etc. (so that if a new stand would be added it wouldn't give any problems like security breaches (like you said) then it would fit better at line 152-153 I think (at the end of the second "if" in the script).
  18. #40
  19. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,904
    Rep Power
    1045
    First of all, I think we agree that it doesn't make sense to fetch the clan in line 28 and then again in line 41. Right?

    Now, you could fetch the clan in line 28 and then make another query to fetch the stand in line 41. But why not simply fetch both in one query? You simply fetch both the clan and the stand in a single query in line 28. Then you don't need to query the database again in line 41.

    And since we want the actual name of the stand and not just the internal database ID, we need to join the user table with the stand table (which holds the name). And that brings us exactly to the query from #21:

    sql Code:
    SELECT
        users.clan
        , stands.name AS stand
    FROM
        users
        JOIN stands ON users.stand = stands.stand_id
    WHERE
        user_id = :user_id
    ;

    This says: Give me the ID of the user's clan and the name of the user's stand. With those information, you can do your checks.
  20. #41
  21. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    311
    Rep Power
    8
    Originally Posted by Jacques1
    First of all, I think we agree that it doesn't make sense to fetch the clan in line 28 and then again in line 41. Right?
    Already done that

    Ok, it makes sense now, but for storing the stand of a user I have to use the id's again right? But now we don't have them...
  22. #42
  23. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    311
    Rep Power
    8
    The kickscript won't work because it is one big form, and with the foreach the user_id at line 192 is written over every time in the foreach so if the kickbutton is pressed, the last user will be kicked every time... How can I resolve this?

    And just a question, don't you mean users.stand instead of users.clan in line 2 in the query you gave in #21? It would make more sense to me
  24. #43
  25. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,904
    Rep Power
    1045
    Originally Posted by derplumo
    The kickscript won't work because it is one big form, and with the foreach the user_id at line 192 is written over every time in the foreach so if the kickbutton is pressed, the last user will be kicked every time... How can I resolve this?
    You can't. The buttons would have to be in separate forms, but you cannot nest forms.

    And that's a good thing in my opinion. It doesn't make sense to have one big form and at the same time several independent forms within this form, which discard all values and do something completely different.

    If you want to keep the idea of addressing multiple users at once, use checkboxes to kick users (and use [] names, of course). Or give up the approach completely and change users separately.



    Originally Posted by derplumo
    And just a question, don't you mean users.stand instead of users.clan in line 2 in the query you gave in #21? It would make more sense to me
    ??

    Then you'd get the name and internal database ID of the stand, and the clan would be unknown. But you need to know the ID of the clan and the name of the stand, and the stand ID is completely useless.
    Last edited by Jacques1; July 13th, 2013 at 11:57 AM.
  26. #44
  27. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    311
    Rep Power
    8
    Originally Posted by Jacques1
    So you want to leave out the clan completely and instead select the stand name and the internal stand ID? That doesn't fit your application logic, because you need the stand name and the clan ID.
    No, but I thought we would need the stand_id's too, because then we could make an array as a "legend". Then we're always sure that the stand_id's are really representating the stand_names, so when I may decide to use a new stand, the structure stays intact...
  28. #45
  29. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    311
    Rep Power
    8
    ok, I'm almost there, but how do I transfer all the data, posted by the first script (so not the confirmation script) to the second, I mean, how does the second (confirmation script) send it all back?

IMN logo majestic logo threadwatch logo seochat tools logo