creating a sequence

[tito]

I am using a link script that generates a unique ID for a link entry. I would like to generate a sequence for that link within a category.

For example category1 could have ID=652 and sequence=3. Meaning that the link ID in db is 652 and this is the third item in category.

The only way I can think of that being done is to do:

query db select cat_id, sequence from links where cat_id=$values[Cat_ID];
read all existing sequence in that selected cat_id starting with 1 (eg 1,2,3,4 etc...)

$sequence=first empty slot from read.

and in the insert statement for new/update listing, I will put $sequence as value to be inserted.

Now, sometimes, when a listing gets deleted, we will have 1,2,4 ... missing the "3". So the above query will pick that "3". It will pick the first empty 'slot', not the last one.

How can I build such a query? I could easily then insert it in the function below.

function UpdateContactData($ID, $UpdateCode, $values) {

global $db, $link_table;

// Prepare Data
$Name = trim($values[Name]);
$Address1 = trim($values[Address1]);
$Address2 = trim($values[Address2]);
$City = trim($values[City]);
$Cat_ID = $values[Cat_ID];
$Zip = trim($values[Zip]);
$Phone1 = trim($values[Phone1]);
$Phone2 = trim($values[Phone2]);
$Phone3 = trim($values[Phone3]);
$Phone4 = trim($values[Phone4]);
$Phone5 = trim($values[Phone5]);
$Cell = trim($values[Cell]);
$Fax1 = trim($values[Fax1]);
$Fax2 = trim($values[Fax2]);
$Fax3 = trim($values[Fax3]);
$Fax4 = trim($values[Fax4]);
$Fax5 = trim($values[Fax5]);
$Email = trim($values[Email]);
$WebsiteURL = trim($values[WebsiteURL]);
    $x = $values[x];
    $y = $values[y];
// Update Data
mysql_query("UPDATE $link_table SET Name = '$Name', Cat_ID = '$Cat_ID', Address1 = '$Address1', Address2 = '$Address2', City = '$City', Zip = '$Zip', Phone1 = '$Phone1', Phone2 = '$Phone2', Phone3 = '$Phone3', Phone4 = '$Phone4', Phone5 = '$Phone5', Cell = '$Cell', Fax1 = '$Fax1', Fax2 = '$Fax2', Fax3 = '$Fax3', Fax4 = '$Fax4', Fax5 = '$Fax5', Email = '$Email', WebsiteURL = '$WebsiteURL', x = '$x', y = '$y'  WHERE ID = '$ID' AND UpdateCode = '$UpdateCode'", $db) or die(mysql_error());

}[/SIZE]

[matto]

I suggest simply renumbering the sequence when you delete an entry.

here's the code that I use for deleting one news article; articles being ordered by a "sequence" field.

        $sql = "delete from article where id='$id'";
        db_query($sql);

    // renum sequence

    $sql =  "select distinct id,sequence from article order by sequence";
    $articles = db_query($sql);

    $newseq = 1;
    for($i=0; $i<count($articles); $i++)
    {
            $sql = "update article set sequence=$newseq where id='".$articles[$i][0]."'";
            db_query($sql);

            $newseq++;
    }

perhaps this is useful to you.

[tito]

thank you.. I am going to try that.

[tito]

and how d you insert a sequence for a new article?