Rewrite without using try catch

[hsriat]

IDs in the database are saved in the form of abc_001, abc_002 or they may be abc_asd, abc_a12

I run a code to check the highest numerical suffix of the ids.
in the above case, it would return 3.

Here's the code for that.

$highest_serial = 0;
while($row = mysql_fetch_array($sql))
{
    $this_serial = str_replace("abc_", "", $row['id']);
    try
    {
        $this_serial = variant_int($this_serial);
    }
    catch(Exception $e) 
    {
        null;
    }
    if (filter_var($this_serial, FILTER_VALIDATE_INT)!=false && $this_serial>$highest_serial)
    $highest_serial = $this_serial;
}

If I don't use try-catch, it gives error for the ids of type abc_asd, and if I don't do variant_int(), it considers abc_002's "002" as a string and thus returns a wrong result.

Do you see any other way to do this without using try-catch...?

[TheServant]

No idea, but what about using an ID column in the database (numerical auto-increment) and then have an internal ID column with the system you describe. It's just if you are naming something and include numbers in the name, really, it is a string, and so you will always need the variant_int() or something like it to convert it.

[Atli]

You can use the is_numeric function to check whether a string is a number or not.

You could try something like:

function is_id_numeric($id) {
  $pieces = explode("_", $id);
  return is_numeric($pieces[1]);
]
 

I have to wonder tho, why do you use such complex ID's for you table?
It is faaar simpler to use an integer with auto_increment.

[hsriat]

I have to wonder tho, why do you use such complex ID's for you table?
It is faaar simpler to use an integer with auto_increment.

No idea, but what about using an ID column in the database (numerical auto-increment) and then have an internal ID column with the system you describe. It's just if you are naming something and include numbers in the name, really, it is a string, and so you will always need the variant_int() or something like it to convert it.

Actually its a little bit complex than it seems. Ids, can not only be in the form of abc_001, but also xyz_001 or abc_xxx or simply abcdef. And Ids are not made by user, but by an administrator. So if he will enter a prefix say abc and want to generate 10 ids, Ajax will check for the highest prefix with that requested suffix in the database (with I am doing in the given snippet) and pop-up a div with a form showing registration for those 10 ids. So auto increment was no solution. Hope you understand my situation.

Thanks :)
Harpeet

[hsriat]

You can use the is_numeric function to check whether a string is a number or not.

Thanks for that is_numeric function. I never knew it.
Done like this...[php]$highest_serial = 0;
while($prefixed_id = mysql_fetch_array($sql))
{
$this_serial = str_replace($prefix."_", "", $prefixed_id['id']);
if (is_numeric($this_serial))
{
$this_serial = variant_int($this_serial);
if ($this_serial>$highest_serial)
$highest_serial = $this_serial;
}
}[/php]

Still have to use variant_int to convert 002 to 2

[TheServant]

Actually its a little bit complex than it seems. Ids, can not only be in the form of abc_001, but also xyz_001 or abc_xxx or simply abcdef. And Ids are not made by user, but by an administrator. So if he will enter a prefix say abc and want to generate 10 ids, Ajax will check for the highest prefix with that requested suffix in the database (with I am doing in the given snippet) and pop-up a div with a form showing registration for those 10 ids. So auto increment was no solution. Hope you understand my situation.

Thanks :)
Harpeet

Ahh, that's a toughie. I wondered why you had abc_asd and if it was common. Glad you got that work around though. Are you happy with that solution?

[dlite922]

No idea, but what about using an ID column in the database (numerical auto-increment) and then have an internal ID column with the system you describe. It's just if you are naming something and include numbers in the name, really, it is a string, and so you will always need the variant_int() or something like it to convert it.

First good answer you've had. :) lol j/k

I'm with you on this, why is it necessary for you to have letters in your IDs?

[hsriat]

Ahh, that's a toughie. I wondered why you had abc_asd and if it was common. Glad you got that work around though. Are you happy with that solution?

Yeh, I wanted to remove try catch, and now it works exactly as expected.

why is it necessary for you to have letters in your IDs?

I explained that. Look at post #4.
These are user ids. Depending upon different department, they can have different prefixes. Like there are 10 persons in HR deptt, their ids will be hr_001 to hr_010. I 11th person joins, he should automatically get id hr_011. And I someone is arrogant enough, he can request for id like hr_arrogant (id with word suffix).

Am I doing right according to you?

[hsriat]

I 11th person joins, he should automatically get id hr_011. And I someone is arrogant enough, he can request for id like hr_arrogant (id with word suffix).

I think there's a problem with "F" key of my keyboard.

[dlite922]

Yeh, I wanted to remove try catch, and now it works exactly as expected.




I explained that. Look at post #4.
These are user ids. Depending upon different department, they can have different prefixes. Like there are 10 persons in HR deptt, their ids will be hr_001 to hr_010. I 11th person joins, he should automatically get id hr_011. And I someone is arrogant enough, he can request for id like hr_arrogant (id with word suffix).

Am I doing right according to you?

I believe something could be fixed higher up. We're beating a dead horse here.

In a good system tables talk to each other via Primary keys, which are always defined as

id INT NOT NULL AUTO_INCRMENT UNIQUE

(sorry for caps, SQL habit)

You'd have a department table and an employee table. (if its a one to many rel'ship, if its a many to many rel'ship, you'd have a link table in between)

employee Table:
ID
departmentID
number
firstName
lastName
Blah
Blah2

department Table
ID
code
name
blah
etc...


In this example employee.departmentID is a foreign key to deparment.ID

I added employee.number because you could use that for their sequence if you don't want to use ID (for example if you could have two employee's with number 10, but they have different ID's and have different departmentIDs). This is possible through some checking and the responsibility of incrementing the number will be on your shoulders.

I would just use the employee.ID as the number.

To use your funky numbering scheme is to grab the employee number and the deparment code (which is "hr").

Example:

[PHP]

SELECT CONCAT(d.code, "_",e.number) FROM employee AS e, department as d;

[/PHP]

That should display the names as:

hr_0345

In case the number is actually not a number, but a word, this scenario works too. Again, incrementing the employee.number will be your responsibility. You can have

hr_arrogant

Let me know if you have any questions.

-Dan

[Atli]

These are user ids. Depending upon different department, they can have different prefixes. Like there are 10 persons in HR deptt, their ids will be hr_001 to hr_010. I 11th person joins, he should automatically get id hr_011. And I someone is arrogant enough, he can request for id like hr_arrogant (id with word suffix).

Am I doing right according to you?

You should seriously consider Dan's suggestion.

The method you described violates one of the basic rules of relational databases, namely the first normal form (1NF).
You should never store more than a single piece of data in a single field.

Check out this article to see more.

[hsriat]

@dlite922 and Atli

I agree I'm quite bad at database design. But I think either you are misinterpreting me or I'm in trouble. Though I hope first one is true.

The ids above in the form of hr_123 etc has nothing to do with its identification with the department by the system.

All the ids are in one table. And there's only one table. Department table is not the in picture yet. In fact there's a different field for department, which contains the department code.

When asked for a particular department, query executed would be WHERE `deptt`='hr' but not WHERE `id` LIKE 'hr_%'

Is that where confusion lies?... or am I in trouble?

[Atli]

If you have the department name it in the table already, why do you include it in the ID?
(I even think that violates the 2NF as well as the 1NF :P)

I would recommend removing the department prefix from your ID and replacing it either with a simple integer that can simply be auto_incremented or a unique string column.

[hsriat]

If you have the department name it in the table already, why do you include it in the ID?
(I even think that violates the 2NF as well as the 1NF :P)

I would recommend removing the department prefix from your ID and replacing it either with a simple integer that can simply be auto_incremented or a unique string column.

Well that are the client's requirements. They want ids of the deptt with a prefix of deptt code.

"Physics deptt should have ids like phy_001, phy_002 ..so on.. while for the temporary candidate, we would like to have ids like phy_x01 , phy_x02 or phy_a, phy_b ... depending upon the wish of department administrator. Administrator should also be enabled to provide special ids like phy_good_student_Atli and phy_good_student_dlite922"


That was what I am told to do.:D

[Atli]

Hmm... damn clients always complicating things :P

How about if you create a table structure somewhat like this:

CREATE TABLE user (
  UserID VarChar(50) Not Null,
  DepCode VarChar(50) Not Null Default 'phy',
  /* etc... */
  Primary Key(UserID, DepCode)
)
 

Note that the Primary Key consists of both the UserID field and the DepCode field, so each UserID is only unique to the DepCode.

Then you could insert into the UserID field whatever you like, whether it be a number or a string.

INSERT INTO user(UserID, DepCode) VALUES
('001', 'phy'), ('002', 'phy'), ('good_student', 'phy'),
('001', 'sci'), ('number_two', 'sci'), ('random_guy', 'sci'),
/* etc */
 

Then, when you select the User info, you could CONCAT those two fields to create the actual key, as requested by your client.

SELECT CONCAT(DepCode, "_", UserID) AS 'UserKey' FROM user;
 

[hsriat]

This sounds impressive.
Will change like suggested.
I never knew Primary Key(UserID, DepCode) could work like this. I though primary key needs to be unique throughout the table.

I'm too bad in db design. Can you refer some tutorials?
I don't even know functions in SQL (like CONCAT). Only things I know are SELECT, INSERT, UPDATE. I recently came to know int(1) is not same as varchar(1) .. :(

And world is too cruel, one needs to be perfect in everything to survive. :D

Regards,
Harpreet

[Atli]

I though primary key needs to be unique throughout the table.

And this is very true. A primary key does need to be unique for each row.
But, MySQL can create the key based on up to 16 columns.
Think of it as a hidden column, made up of the values of all the given columns, added one after the other, separated by "_".

So in the table I posted earlier, the primary key for a row with UserID="001" and DepCode="phy" would be "001_phy".

Can you refer some tutorials?

I don't really know any tutorials. I just Google stuff I want to know.
And go through the documentation, of course.