Jump to content

Generate value that doesn't exist in table


Go to solution Solved by Ch0cu3r,

Recommended Posts

I'm generating a nine digit pseudo-random number to be inserted into a database table with mysqli where the code inserted doesn't already exsit, and I just wanted to check with someone to see if this is an okay way of doing so.

function generateNewCode($a_con){

    $report = rand(100000000, 999999999);
		
    $code_string = "SELECT code_number FROM Codes WHERE code_number = " . $new_code;

    $code_result = mysqli_query($a_con, $code_string);
		
    while(mysqli_num_rows($code_result) >= 1){
			
        $report = rand(100000000, 999999999);
		
    }
		
    return $report;

}

Technically this could run forever, if the generated number continuously matches... But I don't know how else to avoid that.

  • Solution

Running queries within loops is not very efficient. You could select all codes from the database and then add them to an array. Then use PHP to loop through that array checking to see if the new code exists, if does regenerate the code and check again. Only add the code to the database when the code doesn't match.

 

Something like

function getCodes()
{
    $code_string = "SELECT code_number FROM Codes";
    $code_result = mysqli_query($a_con, $code_string);

    $codes = array();
    while($row = mysqli_fetch_assoc($code_result))
    {
        $codes[] = $row['code_number'];
    }

    return $codes;
}


function generateNewCode($codes){
 
    // generate code
    $report = rand(100000000, 999999999);
    
    // check that it exists
    if(in_array($report, $codes))
        return generateNewCode($codes); // it exists, call this function again, generates a new code
    else
        return $report; // Code doesn't exist, return the generated code
}

$codes = getCodes(); // get codes from database
$report = generateNewCode(&$codes); // generate a code
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.