PHP/MySQL Loop Help

Question

I need to insert a 6 digit key into my database. I can't figure out how to do the loop part. If the key is in use I need to keep generating a new key and checking it to make sure its not in use.

$row=mysql_fetch_assoc(mysql_query("SELECT count(*) as numrecords FROM id WHERE id='".$generated_id."'"); 
if ($row['numrecords'] >= 1)
{ 
//key is in use generate new key and loop to make sure its not in use
} 
else 
{ 
//insert key
}
mysql_close();

Thanks

@R3dBu77: Any reason why you can't make that database field a unique index? — stealthyninja
– stealthyninja, Commented Dec 20, 2010 at 0:57
Why aren't you using an AUTO_INCREMENT column so there's no searching? (Assuming that's what you're after, a unique primary key) — Brad Christie
– Brad Christie, Commented Dec 20, 2010 at 0:58
elusive yes. i cant use auto-icrement because i cant have the numbers in order. 1,2,3,4,5 etc. — R3dBu77
– R3dBu77, Commented Dec 20, 2010 at 0:59
Its for a video site. Video.php?id=. The uploader has the option to pick unlisted. If the pick unlisted the video will not be posted on the site but anyone with the id can view it. If I use auto-increment the id's would be sequential. A person could just keep adding 1 to the id and view every video. — R3dBu77
– R3dBu77, Commented Dec 20, 2010 at 1:05

Paul Schreiber · Accepted Answer · 2010-12-20 06:00:47Z

2

I'd add a UNIQUE key constraint on the column:

ALTER TABLE id ADD UNIQUE id;

Then you can use this PHP code:

while (1) {
  // generate ID
  $sql = "INSERT....";
  $result = mysql_query($sql);
  if ($result !== false) { break; }
}

edited Dec 20, 2010 at 6:00

answered Dec 20, 2010 at 1:05

Paul Schreiber

12.6k4 gold badges44 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Orbling Over a year ago

Very sensible, I would suggest an edit to make clear that the SQL in the loop is an INSERT.

Orbling · Accepted Answer · 2010-12-20 00:59:07Z

How about this:

do {
    $row = mysql_fetch_assoc(mysql_query("SELECT count(*) as numrecords FROM id WHERE id='".$generated_id."'");
    if ($row['numrecords'] >= 1)
    { 
        //key is in use generate new key and loop to make sure its not in use
        // regenerate $generated_id
    }
} while ($row['numrecords'] >= 1);

//insert key

mysql_close();

Or to clean things up a bit:

while (mysql_result(mysql_query("SELECT 1 FROM id WHERE id='".$generated_id."'"))) {
   $generated_id = generateKey();
}

// insert key

mysql_close();

Tomasz Kowalczyk · Accepted Answer · 2010-12-20 00:59:17Z

0

If you are using such code to generate loop, simply put a test at the beginning:

while(true)
$row=mysql_fetch_assoc(mysql_query("query goes here"));
if($row) { continue; }
// rest of code

Don't forget to add some break; when code reach too many iterations or you found non-existent sequence.

answered Dec 20, 2010 at 0:59

Tomasz Kowalczyk

10.5k6 gold badges56 silver badges70 bronze badges

Collectives™ on Stack Overflow

PHP/MySQL Loop Help

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related