[UPHPU] Dual ID numbers

Jacob Wright jacwright at gmail.com
Tue Jun 7 10:58:20 MDT 2005

Sound like you want to use the primary key of the table to contain 
information for the system. You will always run into trouble when you try 
this. The one and only purpose of a primary key is to identify a particular 
record in the database. That is why they need to be unique, and why 
auto-increment was created. If you need to sort the CDs or number them with 
each composer then you should have another field. You can do an insert like 
the following if you have MYSQL 4.1.

$cdName = $_POST['cdName'];
$sql = "INSERT INTO cds (name, sortOrder) (SELECT '$cdName', sortOrder+1 
FROM cds ORDER BY sortOrder DESC LIMIT 1)"

This would give you the next highest sortOrder in the table and you could 
get it all into one query. Otherwise SELECT the hightest and add one to it. 
But never use the primary key for something other than a databse identifier 
(ex. not for displaying to the user, etc.)

Jacob Wright

On 6/7/05, Ray Hunter <bigdog at venticon.com> wrote:
> TJ Hunter wrote:
> > As far as I know, MySQL doesn't support what you're trying to do.
> > Here's what I would do though, let MySQL auto increment your primary
> > key as normal and use that key for relationships, then create another
> > field for your special id. you'll have to write your own php code to
> > figure out what the next id is going to be instead of relying on the
> > user to type it in or MySQL generating it for you.
> you might want to have 3 tables:
> 1. composer table (auto-incremented composer_id)
> 2. cd relation table (cd_id)
> 3. composer_cd join table (incremented cd_id with composer_id)
> -
> ray
> _______________________________________________
> UPHPU mailing list
> UPHPU at uphpu.org
> http://uphpu.org/mailman/listinfo/uphpu
> IRC: #uphpu on irc.freenode.net <http://irc.freenode.net>

More information about the UPHPU mailing list