how to use while loop to check database

[impact]

Hello,

This is in continuation from my previous post. I dint get any reply there, so decided to make a fresh post here.

This is my code:

$random_id = "sdfsdf";
// Check if the random number already exist
$getRandomID = "SELECT * FROM shortURL WHERE randomID = '$random_id'";
$getRandomID2 = mysql_query($getRandomID) or die (mysql_error());
$getRandomID3 = mysql_fetch_array($getRandomID2);

while ($row = $getRandomID3['randomID']){
$randomID = $row['randomID'];
if($randomID == $random_id){
<- i am not able to proceed further ->
}
}

What I am trying to do is, create a $random_id check if this is already existing in database. If it does, then recreate $random_id else done!.

Some how my while loop is not working, mostly because of my limited knowledge of while loop and its application in such cases.

Any help on this will be appreciated.

Thank you.

[rocknbil]

Well first, do not select *, this is overkill. All you need is a count(*).

I'd do this **something** like so:

- generate the random id
- check if it's there
- while "it exists" keep generating and id until one doesn't exist.

You have to be very careful not to create a recursive loop here, so do this in a test environment, and put a limit on it. Here's a starting code, may need debugging, but to give you an idea:

<?php 
header("content-type:text/html"); 
$randomId = getRandomId(); 
echo "Random id: $randomId"; 
// done 
function getRandomId () { 
 // prevent recursing, 100 should be more than enough 
 $max_checks = 100; 
 $idExists=1; 
 $id=createID(); 
 for ($i=0;$i<=$max_checks;$i++) { 
 while ($idExists>0) { 
  $query = "select count(*) from table where unique_id='$id'"; 
  $result=mysql_query($query) or die("Cannot check for id " . mysql_error()); 
  // This will return a ZERO if there's no matching id. So $idExists will now be 0. 
  // If not, append it with $i. 
  $row=mysql_fetch_array($result); 
  $idExists=$row[0]; 
  if ($idExists > 0) { $id .= $i; } 
  //else { $idExists = 0; } // Not really needed, see prev. comment 
 if ($idExists==0) { break; } 
 } 
 if ($i >= $max_checks) { die("Could not get random id in $max_checks attempts!"); } 
 } 
 return $id; 
} 
function createID () { 
 // Eliminate the letter O so it doesn't get confused for a zero 
 // and the lower case L so it doesn't get confused for a one. 
 $random=NULL; 
 $letters = 'aBcdEFgHIjKLmnpQRsTuVwXyZ'; 
 $lets = preg_split('//', $letters); 
 $randNum = rand(1,9999); 
 $nums = preg_split('//', $randNum); 
 for ($i=0;$i<count($nums);$i++) { 
 $random .= $nums[$i] . $lets[intval(rand(1,count($lets)))]; 
 } 
 return $random; 
} 
?> 

[FourDegreez]

Try this.

First, a function to generate a random string:

function getRandomId($len=5) {
  $ch = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z', '1', '2', '3', '4', '5', '6', '7', '8', '9', '0');
  for ($i = 0; $i < $len; ++$i)
    $str .= $ch[rand(0, 61)];
  return $str;
}

Now a function that will keep trying different random ids until it gets one that is not used:

function getUnusedRandomId() {
  //first call our function to get a random id
  $random_id = getRandomId();

  // Check if the random number already exist in db
  // here is the SQL (you supplied, I changed * to 1)
  $getRandomID = "SELECT 1 FROM shortURL WHERE randomID = '$random_id'";

  //execute it
  $getRandomID2 = mysql_query($getRandomID) or die (mysql_error());

  //if this SQL returns a row, we know the random id is in use
  //so what we'll do is call this function again
  //or else return the random id
  if (mysql_fetch_row($getRandomID2))
    return getUnusedRandomId();
  else
    return $random_id;
}

That should do what you want. So when you want to get an unused random id, it's as easy as

$my_random_id = getUnusedRandomId();

[impact]

FourDegreez
Thank you Thank you Thank you.

return getUnusedRandomId();

is the thing that never came into my mind. I tried calling the function again in this way
getUnusedRandomId();
and every time this gave me an error. I also tried many for loops, made many small small function and what not.. I could not never understand that this can be done by
return getUnusedRandomId();