[UPHPU] Dual ID numbers

Jacob Wright 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 mailing list