Activation Key and row values incorrect

[Xtremer360]

I have no clue why but I keep getting the second rows id to be 1000 and the userID to be 1. Also the activationkey formation is not working and not sure why either. Anyone see anything?

 

<?php

session_start();

// Include the database page
require ('../inc/dbconfig.php');
require ('../inc/global_functions.php');

//Login submitted
if (isset($_POST['submit'])) { 
    
    // Errors defined as not being any
    $errors = "no";
    
    // Assign variable values if there is values
    $username = empty($_POST['username'])?null:trim($_POST['username']);
    $password = empty($_POST['username'])?null:trim($_POST['password']);
    $password2 = empty($_POST['username'])?null:trim($_POST['password2']);
    $email = empty($_POST['username'])?null:trim($_POST['email']);
    $firstName = empty($_POST['username'])?null:trim($_POST['firstName']);
    $lastName = empty($_POST['username'])?null:trim($_POST['lastName']);
    
    // Error checking, make sure username and password given 
if (!$username || !$password || !$password || !$email || !$firstName || !$lastName) {

        // No username or password given error
        $errors = "yes";
        $message = "You must enter values to all of the form fields!";
        
        $output = array('errorsExist' => true, 'message' => $message); 
        
} else {
  
        // No errors reported 
        // Escape post data
        $username = mysqli_real_escape_string($dbc,$_POST['username']);
        $password = mysqli_real_escape_string($dbc,$_POST['password']);
        $password2 = mysqli_real_escape_string($dbc,$_POST['password2']);
        $email = mysqli_real_escape_string($dbc,$_POST['email']);
        $firstName = mysqli_real_escape_string($dbc,$_POST['firstName']);
        $lastName = mysqli_real_escape_string($dbc,$_POST['lastName']);
        
        // Retrieve IP Address of new user
        $ipAddress = $_SERVER['REMOTE_ADDR'];
        
        // Query the database for user info with username
        $query = "SELECT * FROM manager_users WHERE username = '".$username."' OR emailAddress = '".$email."'";
        $result = mysqli_query($dbc,$query);
        
        // Fetch returned data from result set
        $row = mysqli_fetch_array($result);
        
        // Count number of returned results from query
        if (mysqli_num_rows($result) == 0) {
            
            // Find out if the password is the same as the confirm password
            if ($password == $password2) {
                
                // Assign hashed password to variable
                $hashPassword = GenPassHash($password, $password2);
                
                // No user found ready to insert new user with query
                $query = "INSERT INTO manager_users (username, password, password2, emailAddress, firstName, lastName) VALUES ('".$username."','".$hashPassword."','".$password2."','".$email."','".$firstName."','".$lastName."')";
                $result = mysqli_query($dbc,$query);
                echo $query;
                
                // Get insert ID
                $id = mysqli_insert_id($dbc); 
                echo $id;
                
                // Find out last user inserted's userID
                $query2 = "SELECT userID FROM manager_users WHERE id = '".$id."'";
                $result2 = mysqli_query($dbc,$query2);
                
                // Fetch returned data from result set
                $row2 = mysqli_fetch_array($result2);
                
                // Assign query array values to variables
                $userID = $row2['userID'];
                
                // Develop activation key for new user
                $activationKey =  mt_rand() . mt_rand() . mt_rand() . mt_rand() . mt_rand();
                
                // Find out if it was the first user inserted
                if ($id == 1) {
                    
                    // Update first user with new userID
                    $query = "UPDATE manager_users SET userID = 10000 WHERE id = '".$id."'";
                    $result = mysqli_query($dbc,$query);
                    
                } else {
                    
                    // Increase userId by 1 to get new userID
                    $userID = $userID + 1;
                    
                    // Update new user with new userID
                    $query = "UPDATE manager_users SET userID = '".$userID."' WHERE id = '".$id."'";
                    $result = mysqli_query($dbc,$query);
                    
                }
                
                // Insert new registration into separate table
                $query2 = "INSERT INTO manager_users_registrations (userID, registrationDate, registrationKey, ipAddress) VALUES ('".$userID."',CURRENT_TIMESTAMP,'".$activationKey."','".$ipAddress."')";
                $result2 = mysqli_query($dbc,$query);
                
                // Email user new registration account
                function my_domain_name() {
            		$my_domain = $_SERVER['HTTP_HOST'];
            		$my_domain = str_replace('www.', '', $my_domain);
            		return $my_domain;
            	}
                $sender_name = "Kansas Outlaw Wrestling";
                $sender_email = "noreply@kansasoutlawwrestling.com";
                $reply_to = "noreply@kansasoutlawwrestling.com";
                $recipient_email = $row[ 'emailAddress' ]; 
                $email_subject = "KOW Manager Account Registration";
                $year = date(Y);
        
                $email_body = '
                <table width="500" border="0" cellspacing="0" cellpadding="0">
                  <tr>
                    <td><img src="http://'.my_domain_name().'/images/logo.png" border="0" alt="LOGO" /></td>
                  </tr>
                  <tr>
                    <td> 
                        <table width="500" border="0" cellspacing="0" cellpadding="0">
                          <tr>
                            <td>Hello '.$firstName.' '.$lastName.' Welcome to our website!\r\rYou, or someone using your email address, has completed registration at '.my_domain_name().'.com. You can complete registration by clicking the following link:\rhttp://www.'.my_domain_name().'.com/verify.php?$activationKey\r\rIf this is an error, ignore this email and you will be removed from our mailing list.\r\rRegards,\ '.my_domain_name().'.com Team</td>
                          </tr>
                        </table>
                    </td>
                  </tr>
                  <tr>
                    <td>© '.$year.' - '.my_domain_name().'</td>
                  </tr>
                </table>
                ';
                $mail_template = '
        		<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
        		<html>
        		<head>
        		<title>'.$email_subject.'</title>
        		</head>
        		<style type="text/css">
        			body{background: #EDEBEA;}
        			#wrapper{background:#FFF;border:4px solid #DDD;width:650px;}
        		</style>
        		</head>
        		<body>
        		<div id="wrapper">"'.$email_body.'</div>
        		</body>
        		</html>
        		';
                $headers  = 'MIME-Version: 1.0' . "\r\n";
        		$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
        		$headers .= 'Content-type: text/html; charset=us-ascii' . "\r\n";
        		$headers .= 'From:' .$sender_name. "\r\n";
        		$headers .= 'Reply-To: ' .$reply_to. "\r\n";
        		$headers .= '1\r\nX-MSMail-Priority: High' . "\r\n";
        		$headers .= 'X-Mailer: Kansas Outlaw Wrestling Mail Controller v1.0' . "\r\n";
                mail($recipient_email, $email_subject, $email_body, $headers); 
                
                // Registration was sucessful
                $errors = "no";
                $message = "Registration was successful please check the account you gave for a verification email!";
        
                $output = array('errorsExist' => false, 'message' => $message); 
                
            } else {
                
                // Passwords did not match
                $errors = "yes";
                $message = "You must enter the same password for the confirm password!";
        
                $output = array('errorsExist' => true, 'message' => $message); 
                
            }            

        } else {
           
           // User doesn't exist in database error
           $output = array('errorsExist' => true, 'message' => 'Sorry there is already an account set up with that username or email address, please check your username and email address and try again!'); 
            
        }
       

    }
        
    
}

//Output the result
$output = json_encode($output);
echo $output;


?>

[btherl]

What do you mean by "the second rows id"?  Which part of the code are you talking about?

[Xtremer360]

 

<?php
// No user found ready to insert new user with query
                $query = "INSERT INTO manager_users (username, password, password2, emailAddress, firstName, lastName) VALUES ('".$username."','".$hashPassword."','".$password2."','".$email."','".$firstName."','".$lastName."')";
                $result = mysqli_query($dbc,$query);
                echo $query;
                
                // Get insert ID
                $id = mysqli_insert_id($dbc); 
                echo $id;
                
                // Find out last user inserted's userID
                $query2 = "SELECT userID FROM manager_users WHERE id = '".$id."'";
                $result2 = mysqli_query($dbc,$query2);
                
                // Fetch returned data from result set
                $row2 = mysqli_fetch_array($result2);
                
                // Assign query array values to variables
                $userID = $row2['userID'];
                
                // Develop activation key for new user
                $activationKey =  mt_rand() . mt_rand() . mt_rand() . mt_rand() . mt_rand();
                
                // Find out if it was the first user inserted
                if ($id == 1) {
                    
                    // Update first user with new userID
                    $query = "UPDATE manager_users SET userID = 10000 WHERE id = '".$id."'";
                    $result = mysqli_query($dbc,$query);
                    
                } else {
                    
                    // Increase userId by 1 to get new userID
                    $userID = $userID + 1;
                    
                    // Update new user with new userID
                    $query = "UPDATE manager_users SET userID = '".$userID."' WHERE id = '".$id."'";
                    $result = mysqli_query($dbc,$query);
                    
                }
?>

[btherl]

Which column is your auto increment column, is it "id"?  If so, the userID column is being fetched before it is set.  Do you want to set the new userID to be the largest old userID + 1?

[Xtremer360]

Yes I do.

[btherl]

Ok, you probably want to do this then:

 

// Find the highest existing userID
                $query2 = "SELECT MAX(userID) FROM manager_users";
                $result2 = mysqli_query($dbc,$query2);
                
                // Fetch returned data from result set
                $row2 = mysqli_fetch_array($result2);
                
                // Assign query array values to variables
                $highestUserID = $row2['userID'];

 

If you have an index on the userID column, this query will be fast even if there are thousands of users.  If you have tens or a few hundred users then it doesn't matter much if you have an index or not.

[Xtremer360]

that didn't work because the first row in the table was id-1  userID-10000 and the second row went to id-10003 and userID-1

[btherl]

Did you use $highestUserID or $userID when calculating the new user id?

[Xtremer360]

where else do I put the variable highestuserID

[btherl]

See this code:

 

                    // Increase userId by 1 to get new userID
                    $userID = $userID + 1;

 

It takes $userID, and sets it to $userID + 1.  The old value has to come from somewhere.  So you should change it to this:

 

                    // Increase userId by 1 to get new userID
                    $userID = $highestUserID + 1;

[Xtremer360]

For some reason it's not retrieving the userID. And yes the column is called userID.

 

 

<?php
// Find the highest existing userID
                $query2 = "SELECT MAX(userID) FROM manager_users";
                $result2 = mysqli_query($dbc,$query2);
                echo $query2;
                
                // Fetch returned data from result set
                $row2 = mysqli_fetch_array($result2);
                
                // Assign query array values to variables
                $highestUserID = $row2['userID'];
                echo $highestUserID;

// Find out if it was the first user inserted
                if ($id == 1) {
                    
                    // Update first user with new userID
                    $query = "UPDATE manager_users SET userID = 10000 WHERE id = '".$id."'";
                    $result = mysqli_query($dbc,$query);
                    
                } else {
                    
                    // Increase userId by 1 to get new userID
                    $userID = $highestUserID + 1;
                    
                    // Update new user with new userID
                    $query = "UPDATE manager_users SET userID = '".$highestUserID."' WHERE id = '".$id."'";
                    $result = mysqli_query($dbc,$query);
                    echo $query;
                    
                }
?>

[btherl]

Sorry about that - using MAX() changes the column name.  Using this sql will make it fetchable as userID again:

 

<?php
// Find the highest existing userID
                $query2 = "SELECT MAX(userID) AS userID FROM manager_users";
                $result2 = mysqli_query($dbc,$query2);
                echo $query2;
                
                // Fetch returned data from result set
                $row2 = mysqli_fetch_array($result2);
                
                // Assign query array values to variables
                $highestUserID = $row2['userID'];
                echo $highestUserID;