computerzworld wrote:Now if i give the range from 1 to 15 to find unused ids then it should display me the numbers 5,9,13,14,15. And I want to find these numbers using query. And again this column is not auto-increment. It contains unique ids. And I just want to retrieve these numbers. Not want to re-use them. How can I do this?
Did you not try to use one or both of the ways that I suggested in posts #5 and #7? They are both very simple:
// Method #1
$start = 1;
$end = 15;
$stmt = $db->prepare("SELECT id FROM my_table WHERE id >= :start AND id <= :end ORDER BY id");
$stmt->bindValue(':start', $start, PDO::PARAM_INT);
$stmt->bindValue(':end', $end, PDO::PARAM_INT);
$stmt->execute();
$ids = array();
foreach ($stmt->fetch(PDO::FETCH_ASSOC) as $row) {
$ids[] = $row['id'];
}
$deleted_ids = array_diff(range($start, $end), $ids);
// Method #2
$start = 1;
$end = 15;
$stmt = $db->prepare("SELECT id FROM deleted_ids WHERE id >= :start AND id <= :end ORDER BY id");
$stmt->bindValue(':start', $start, PDO::PARAM_INT);
$stmt->bindValue(':end', $end, PDO::PARAM_INT);
$stmt->execute();
$deleted_ids = array();
foreach ($stmt->fetch(PDO::FETCH_ASSOC) as $row) {
$deleted_ids[] = $row['id'];
}
Note that if you are not using auto-increment, Piranha's concern about race conditions for insertion applies.
