[UPHPU] Dual ID numbers
jacwright at gmail.com
Tue Jun 7 11:00:35 MDT 2005
Sorry, you would want to get the highest sortOrder for a specific composer.
$cdName = $_POST['cdName'];
$composerId = $_POST['composer '];
$sql = "INSERT INTO cds (name, sortOrder) (SELECT '$cdName', sortOrder+1
FROM cds WHERE composerId=$composerId ORDER BY sortOrder DESC LIMIT 1)"
On 6/7/05, Jacob Wright <jacwright at gmail.com> wrote:
> 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