Hiya,
I'm doing an admin page so people can add details of artists to a database.
Somethings the people will know the artist id number for the festival, but somethings they don't so we put temp id numbers for them on the website.

All temp numbers are above a certain number.

& we change the temp number to match the correct festival number later on. This does mean there are gaps in the numbers after a while.

Now, what I want to do is when the add an artist to the database via this admin page (done in PHP) will will find the next temp number in the sequence.

ie. Taken numbers already in the database: 400,401,402,404,405,406,455,563.
& i want the admin page to pick the next free number after 399, so in this case it would pick up 403.

does anyone know how to do this?

I've been trying to do this as:

############################################
//query the database for the next free ID Number
$artists_id_temp_countnum_start = 399;
$artists_id_temp_countnum = $artists_id_temp_countnum_start;
$artists_id_temp_num_found = false;

do
{
$artists_id_temp_countnum++;

$artists_id_temp_search = mysql_query("SELECT artist_id FROM $artists_details WHERE artist_id=".$artists_id_temp_countnum."", $conn);

if(!$artists_id_temp_search)
{
$artists_id_temp_num_found = 1;
$artists_id_temp_num = $artists_id_temp_countnum;
}
else
{
$artists_id_temp_num_found = 0;
}

}
while($artists_id_temp_num_found == 0);

echo("<p> $artists_id_temp_num</p>");

############################################

If anyone can help, that would be great.

Regards
Adam

    I had this problem for an auction program I made in VB6/MySQL. Here is how I did it:

    Run the following query:

    select `artist_id` from `artist_details` order by `artist_id` desc

    If you get no results, then set

    $new_artist_id = $minimum_artist_id;

    But if you get results, iterate like so:

    $new_artist_id = 0;
while ($record = mysql_fetch_assoc($result))
{
            If ($new_artist_id == 0) {
                $new_artist_id = $record['artist_id'] + 1;
                $vLastNumber = $record['artist_id'] + 1;
            }

        If (($vLastNumber - $record['artist_id']) > 1) {
            // We have a gap
            $new_artist_id = $record['artist_id'] + 1
       }

       $vLastNumber = $record['artist_id'];

        If ($record['artist_id'] == $minimum_artist_id) {
            break;
        }

}

If ($new_artist_id > $minimum_artist_id) {
        If ($vLastNumber <> $minimum_artist_id) {
            $new_artist_id = $vLastNumber;
       }
}

    I edited VB code to produce the above example, so it may have some errors.

    My VB code looks like this, in case you are interested.

        Dim rdoQry  As New rdoQuery
    Dim rdoRS   As rdoResultset


With rdoQry
    .Name = "MaxBidderNumber"
    .Sql = "select `bidder_number` from `bidders` order by `bidder_number` desc"
    .RowsetSize = 1
    Set .ActiveConnection = mMySQL
    Set rdoRS = .OpenResultset(rdOpenKeyset, rdConcurRowVer)
End With

Dim vLastNumber As Long


If (rdoRS.RowCount = 0) Then
    GetNewBidderNumber = MINIMUM_BIDDER_NUMBER
    On Local Error GoTo 0
    Exit Function
End If


Do Until rdoRS.EOF
    With rdoRS

        If (GetNewBidderNumber = 0) Then
            ' First Row
            GetNewBidderNumber = CLng(!bidder_number) + 1
            vLastNumber = !bidder_number + 1
        End If

        If ((vLastNumber - CLng(!bidder_number)) > 1) Then
            ' We have a gap
            GetNewBidderNumber = CLng(!bidder_number) + 1
        End If

        vLastNumber = CLng(!bidder_number)

        If (!bidder_number = MINIMUM_BIDDER_NUMBER) Then
            ' We are good!
            Exit Do
        End If

        rdoRS.MoveNext
    End With
Loop



If (GetNewBidderNumber > MINIMUM_BIDDER_NUMBER) Then
    If (vLastNumber <> MINIMUM_BIDDER_NUMBER) Then
        ' There is nobody occupying the minimum space
        GetNewBidderNumber = MINIMUM_BIDDER_NUMBER
    End If
End If


' close record set
rdoRS.Close
rdoQry.Close

    Hope this helps.

      7 days later

      Thankyou Zabmilenko,
      that worked after playing to fit what i wanted & find where the missing element was, which was ";" in this code:

      BEFORE

      If (($vLastNumber - $record['artist_id']) > 1) {
      // We have a gap
      $new_artist_id = $record['artist_id'] + 1
      }

      NOW

      If (($vLastNumber - $record['artist_id']) > 1) {
      // We have a gap
      $new_artist_id = $record['artist_id'] + 1 ;
      }

      THANK YOU so much

      Adam 🙂

        Write a Reply...