question abouts inserrting value into database

bbmak

Hi:
I dont know is this done by the database or done by the script. I want to whenever a primary key insert to the datbase, the database will have this infront of "gr-" every key

for example, in id, int, auto increasement,
whenever an id inserts, the a key will be insert to id column. I want to add "gr-" infront of the key.

Is this procedure done in the database or script?

if done in the database, please let me know how
I am using phpmyadmin

toplay

If the field is an integer, then you can't have "gr-" in front of it. It would have to be a text/varchar type of column. Also, you would have to put that yourself in your script when you insert/update. That's assuming you want something after it like "gr-something". If you only want "gr-" all the time, then you can set that as the default value for a column.

hth.

bbmak

i want to be like gr-0001
gr- (number here)
i kind of want to do it in database because that would save me a lot of work if i don't have to do it in the script.

toplay

Like I already said, you can't in an integer field. You should just display the "gr-" before the number in your script (if it's going to stay the same all the time what's the big deal?).

You can create another column and put "gr-" in there (but that's a waste of space) and when you select from the table SQL CONCAT() the two columns together.

It's best just to define a constant and assign it "gr-". Use the constant name all over your script(s). If you need to change it in the future, then you only have to change it in one place (at the constant definition). Example:

define('PREFIX', 'gr-');
...
echo PREFIX, $row['id']; // Would display gr- followed by the id column

🙂

Weedpacket

Can SQL's CONCAT() take literal arguments, i.e., CONCAT('gr-', fieldname)?

toplay

Of course CONCAT() can and you know that. Thanks for the response and [cattle] prod. 🙂

bbmak, I was trying to show that it's silly to try and put it in the database as you keep insisting.

It's one half dozen of the other. Either you have to put 'gr-' in all the SQL or in your echo statements. Weedpacket, is talking about this:

$sql = "SELECT CONCAT('gr-', id) AS gr_id FROM table_name";

You can still utilize the constant option I mentioned before as well. That way you only have to change it in one place. Example:

define('PREFIX', 'gr-');

$sql = "SELECT CONCAT('" . PREFIX . "', id) AS gr_id FROM table_name";

Or select id normally and echo 'gr-' before the id like in my previous post.

It's your choice of how you wan to do it.

Good luck.

kakki

aren't there triggers in mysql ????

toplay

Triggers are not practical in this case. They're only for INSERT, DELETE, or UPDATE and not SELECT. Plus you have to have MySQL version 5.0.2+

http://dev.mysql.com/doc/mysql/en/triggers.html

It's all in the manual.