Page 1 of 1
Am I High ?!
Posted: Mon Jan 26, 2004 11:53 am
by Etherguy
Don't know if I am trying to create a pipe dream here or not, so please set me straight if I am.
What I would like to do is create a primary key in a table by combining two other fields into one key... so it would look something like this.
woid = date - wid
wid = int (auto increment, unique)
date = date entry
I want woid to be the combination of the other two fields.
Any ideas ??
Posted: Mon Jan 26, 2004 1:17 pm
by jaxn
The only way I know to handle this is to first get the next value from the sequence and then update that record after having done the calculation. Since you have "auto increment" I will assume you are using MySQL. With MySQL you could do something like:
Code: Select all
<?php
// insert database connection stuff here
$date = date('Ymd',mktime());
mysql_query("INSERT INTO table_name (date) VALUES('$date')");
$wid = mysql_insert_id();
$woid = "$date - $wid"; // I am assuming that you are not trying to subtract, but trying to append the wid to the date with a hyphen between
mysql_query("UPDATE table_name SET woid='$woid' WHERE wid='$wid'");
?>
-Jackson
Posted: Mon Jan 26, 2004 1:38 pm
by Etherguy
I suppose I should have mentioned the fact that "woid" is my primary key.
sorry about that.... There has got to be a way to insert this record....
All this just to create a stupid workorder
Ideas??
Posted: Mon Jan 26, 2004 3:00 pm
by jaxn
I could be off base here, but I think any autoincrement fields in MySQL have to be the primary key for the table.
Other databased allow for sequences which definitely allow you to do what you are hoping to do.
The only way I can think to mimic this behaviour in MySQL is to have a table with only one column. that column would be your wid autoincrement (or sequence). you can then get the next value of that sequence like I mentioned above.
The problem with this method is that there is no referential data integrity since MySQL doesn't allow foreign keys (or I should say I have never used foreign keys in MySQL as they have had a slew of releases this year).
HTH,
-Jackson
Posted: Tue Jan 27, 2004 2:41 am
by twigletmac
It would be better to not have a calculated value in one of the columns - if you want to search on a particular `woid` then split it into `wid` and `date` before the search. That way you maintain your data integrity with the auto-incrementing primary key. Retrieving the `woid` is easy too as you can combine `wid` and `date` in your select query.
Mac
Posted: Tue Jan 27, 2004 7:02 am
by Etherguy
I see what you are saying Twig, but I really wanted to index everything off this workorder #, I figured it would be easier to have a single field to index off of, rather then re-write all the code to add the new query. But hey I guess you gotta do what ya gotta do....
Thanks
BTW : JAXN, You can auto-increment an integer without it being the primary key by simply setting the key to UNI
Here is an example :
woid | varchar(30) | | PRI | |
wid | int(5) | | UNI | NULL | auto_increment
As you can see, woid is my primary and wid is set to uni with auto_increment.
Thanks Again