469,952 Members | 2,615 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,952 developers. It's quick & easy.

MYSQL: Get the ID during INSERT

56
Hi guys,

I don't know if this is possible. My guess is it isn't but I'll give it a try.

Is it possible to get the ID (autonumber) of the current row I'm inserting.
I know you can use LAST_INSERT_ID() method but it only works if I do this after the INSERT. What I'd like to achieve is something like this

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tb_page
  2.     (page_description, page_name_blob)
  3.     VALUES
  4.     ('Description', CONCAT('page_name_', LAST_INSERT_ID()));
Obviously the above doesn't work but since LAST_INSERT_ID() returns 0 each time.

So my question is: Is there a way to do this in one sql query? I need to use this in Ad-Hoc Queries from C# so I cannot use stored procedures

Thanks
Luk
May 19 '08 #1
17 9450
rpnew
188 100+
Hi guys,

I don't know if this is possible. My guess is it isn't but I'll give it a try.

Is it possible to get the ID (autonumber) of the current row I'm inserting.
I know you can use LAST_INSERT_ID() method but it only works if I do this after the INSERT. What I'd like to achieve is something like this

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tb_page
  2.     (page_description, page_name_blob)
  3.     VALUES
  4.     ('Description', CONCAT('page_name_', LAST_INSERT_ID()));
Obviously the above doesn't work but since LAST_INSERT_ID() returns 0 each time.

So my question is: Is there a way to do this in one sql query? I need to use this in Ad-Hoc Queries from C# so I cannot use stored procedures

Thanks
Luk
Hi,
If it is a auto increment field then i guess you can use max(id)+1 ...

Regards,
RP
May 20 '08 #2
pechar
56
Hi,
If it is a auto increment field then i guess you can use max(id)+1 ...

Regards,
RP
Thanks for the response. Unfortunately this will not work since go get MAX(id) I need to use a SELECT query on the table being currently updated. This is not allowed in MYSQL. The query I've tried to use is:


Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tb_page
  2.     (page_description, page_name_blob)
  3. VALUES
  4.     ('Description', CONCAT('page_name_', (SELECT MAX(page_id)FROM tb_page)));
Obviously I get an error. Is there another way I could use the MAX function?
What I want ot achieve is to concatenate 'page_name_' and the Current Row ID (primary key) during the INSERT

Thanks
Luk
May 20 '08 #3
ronverdonk
4,258 Expert 4TB
The MAX() function cannot be used for your purpose! When all you want is to known the next auto_increment value before inserting, you can get the next id by issuing this command
Expand|Select|Wrap|Line Numbers
  1. SELECT auto_Increment FROM INFORMATION_SCHEMA.tables WHERE table_name='table_name';
But be very cautious, because when you have multiple users issuing multiple inserts on that table while you are processing that insert id, you could insert the wrong number because another user already has used it.

Ronald
May 20 '08 #4
Atli
5,058 Expert 4TB
Why do you want the ID of the row inserted into another field?
Could you not CONCAT it into the field when you select it?

It seems odd to me to store the same value twice.
May 21 '08 #5
pechar
56
Why do you want the ID of the row inserted into another field?
Could you not CONCAT it into the field when you select it?

It seems odd to me to store the same value twice.
Shoot! Why didn't i think of this before. Well let me give you the scenario. What I'm trying to do is load the site content from a table called tbl_page (which contains the page friendly name and blobs like page_text_blob_name, page_title_blob_name, page_meta_blob_name etc. Then there is a table tbl_blobs which has references to the blob names in tbl_page. So thats why I use the auto id at the end of the field.

E.g.

Row 1
page_id - 1
page_desc - About Us Page
page_text_blob_name - page_text_1
page_title_blob_name- page_title_1
page_meta_blob_name - page_meta_1

i.e. The next record of the page table would have 2 at the end.

In the tbl_blobs the filels would be:

Row 1
blob_id - 1
blob_name - page_text_1
blob_text - "some text to display page text"

Row 2
blob_id - 2
blob_name - page_title_1
blob_text - "some text to display page title"

etc.

Do you think this is the right approach. Note though that this will handle multiple languages so each blob row has a language id as well therefore I'm not storing the data in the tbl_page table.

Thanks a lot for the help
Luk
May 21 '08 #6
pechar
56
The MAX() function cannot be used for your purpose! When all you want is to known the next auto_increment value before inserting, you can get the next id by issuing this command
Expand|Select|Wrap|Line Numbers
  1. SELECT auto_Increment FROM INFORMATION_SCHEMA.tables WHERE table_name='table_name';
But be very cautious, because when you have multiple users issuing multiple inserts on that table while you are processing that insert id, you could insert the wrong number because another user already has used it.

Ronald
Thanks for the information I'll keep the multiple-user-insert issue in mind.
I've been using mysql for 6 months or so now and I see INFORMATION_SCHEMA used frequenlty but never understood it. Would you be so kind and give me some more info? Maybe an example with actual table names?

Thanks and sorry if it sounds like a stupid question :)
Luk
May 21 '08 #7
Atli
5,058 Expert 4TB
There is a better way.

I tried to explain it, but It didn't make sense :P
Consider this instead:
Expand|Select|Wrap|Line Numbers
  1. /* Info on all your pages */
  2. CREATE TABLE page (
  3.     PageID INT Not Null Auto_Increment Primary Key,
  4.     PageDescription Text Not Null,
  5.     /* etc */
  6. );
  7.  
  8. /* Contains all the languages available */
  9. CREATE TABLE language (
  10.     LanguageID INT Not Null Auto_Increment Primary Key,
  11.     LanguageName VarChar(100) Not Null Default 'English'
  12. ); 
  13.  
  14. /* Contains all the available types of text
  15.  * stored in your text table */
  16. CREATE TABLE textType (
  17.     TypeID INT Not Null Auto_Increment Primary Key,
  18.     TypeName VarChar(100) Not Null Default 'Title'
  19. )
  20.  
  21. /* Stores all text and references the other tables
  22.  * for info on what type and language it is,
  23.  * as well as which page it belongs to */
  24. CREATE TABLE text (
  25.     TextID INT Not Null Auto_Increment Primary Key,
  26.     TextValue Text Not Null,
  27.     TypeID_FK INT Not Null References textType(TypeID),
  28.     PageID_FK INT Not Null References page(PageID),
  29.     LanguageID INT Not Null References language(LanguageID)
  30. )
  31.  
Let me know if something isn't making sense.
May 21 '08 #8
pechar
56
There is a better way.

I tried to explain it, but It didn't make sense :P
Consider this instead:
Expand|Select|Wrap|Line Numbers
  1. /* Info on all your pages */
  2. CREATE TABLE page (
  3.     PageID INT Not Null Auto_Increment Primary Key,
  4.     PageDescription Text Not Null,
  5.     /* etc */
  6. );
  7.  
  8. /* Contains all the languages available */
  9. CREATE TABLE language (
  10.     LanguageID INT Not Null Auto_Increment Primary Key,
  11.     LanguageName VarChar(100) Not Null Default 'English'
  12. ); 
  13.  
  14. /* Contains all the available types of text
  15.  * stored in your text table */
  16. CREATE TABLE textType (
  17.     TypeID INT Not Null Auto_Increment Primary Key,
  18.     TypeName VarChar(100) Not Null Default 'Title'
  19. )
  20.  
  21. /* Stores all text and references the other tables
  22.  * for info on what type and language it is,
  23.  * as well as which page it belongs to */
  24. CREATE TABLE text (
  25.     TextID INT Not Null Auto_Increment Primary Key,
  26.     TextValue Text Not Null,
  27.     TypeID_FK INT Not Null References textType(TypeID),
  28.     PageID_FK INT Not Null References page(PageID),
  29.     LanguageID INT Not Null References language(LanguageID)
  30. )
  31.  
Let me know if something isn't making sense.
Thanks for the reply!
I understood what you are saying very well actually :)
And it really makes sense to setup the tables as you have shown me but I have one problem with it.

From the practical point of view it doesn't make too much sense. Say I am to retrive page text for an "about us" page with id 25. The query would be someting like this:

Expand|Select|Wrap|Line Numbers
  1. SELECT TextValue, TypeID_FK 
  2. FROM text
  3. WHERE PageID_FK=25 
  4. AND LanguageID=1
This would return all the textValues and Their type id. Up to this point all makes sense. Also I could have an enumeration for these types to make it simpler from the coding perspective.

But say I also have types for each control on the page like Button, Literal and Label controls. This has to be unique for each page so I'll end up with a million texttypes. How would I manage these types? Isn't it a little messy?
Please correct me if I'm wrong. Could the database be extended to make it more feasible for these localised control text descriptions?i.e. for Buttons, Labels etc.

Thanks a lot
Luk
May 22 '08 #9
Atli
5,058 Expert 4TB
But say I also have types for each control on the page like Button, Literal and Label controls. This has to be unique for each page so I'll end up with a million texttypes. How would I manage these types? Isn't it a little messy?
The textTypes don't need to be unique to each page.
The reason why we put the types in a different table is so that we can re-use the entries. So that every entry into the text table that is meant for the same type of control could reference the same row in the textType table.

Like say, if you have a back_button in all your pages. Each of them having different text in multiple languages. This would require a bunch of rows in your text table, but you would only need to add a single row to the textType table that would represent a back_button, and could be referenced by all the text entries that belong to that type.
From the practical point of view it doesn't make too much sense. Say I am to retrive page text for an "about us" page with id 25. The query would be someting like this:

Expand|Select|Wrap|Line Numbers
  1. SELECT TextValue, TypeID_FK 
  2. FROM text
  3. WHERE PageID_FK=25 
  4. AND LanguageID=1
That is a very simple way of doing this.
If you were to use joins, it would give you a much greater control over the whole thing.
Consider this:
Expand|Select|Wrap|Line Numbers
  1. SELECT text.textValue, type.typeName
  2. FROM text
  3. INNER JOIN language AS lang
  4.     ON text.LanguageID_FK = lang.LanguageID
  5.     AND lang.LanguageName = 'English'
  6. INNER JOIN textType AS type
  7.     on text.TypeID_FK = type.TypeID
  8.     AND type.TypeName IN('page_text', 'page_header', 'back_button')
  9. WHERE text.PageID = 25
  10.  
May 22 '08 #10
pechar
56
If you were to use joins, it would give you a much greater control over the whole thing.
Consider this:
Expand|Select|Wrap|Line Numbers
  1. SELECT text.textValue, type.typeName
  2. FROM text
  3. INNER JOIN language AS lang
  4.     ON text.LanguageID_FK = lang.LanguageID
  5.     AND lang.LanguageName = 'English'
  6. INNER JOIN textType AS type
  7.     on text.TypeID_FK = type.TypeID
  8.     AND type.TypeName IN('page_text', 'page_header', 'back_button')
  9. WHERE text.PageID = 25
  10.  
Ok thanks for clearing the "button" issue I didn't think of it that way.
One more thing why didnt you do the following instead of the joins. Isn't this a simpler way:

Expand|Select|Wrap|Line Numbers
  1. SELECT text.textValue, textType.typeName
  2. FROM text, textType
  3. WHERE text.TypeID_FK = textType.TypeID
  4. AND text.PageID_FK=1
  5. AND text.LanguageID=1 /*where 1 is english*/
If not could you please tell me why? I'm not too keen on using JOINS since I do get a little confused with multiple JOINS :/ though I know they have better performace

Thanks
Luk
May 22 '08 #11
Atli
5,058 Expert 4TB
That would work to yes, if you have the IDs.
Joins are more useful when you need to fetch rows from a table based on values from another table, which is not the case here if you have the IDs rather then the names.

But... your query is still not free of joins.
By using multiple tables in your FROM clause, you are effectively joining them in the most general way. You are basically getting a row for every possible column combo from both tables.

Then, once all the rows have been created, they are filtered out using your WHERE clause.

If you use a INNER JOIN with an ON clause, you can specify how the tables are joined, so that you only get rows that you will actually use, removing the need to filter them later using a WHERE clause.

Which could look something like:
Expand|Select|Wrap|Line Numbers
  1. SELECT text.textValue, textType.typeName
  2. FROM text INNER JOIN textType
  3.   ON text.TypeID_FK = textType.TypeID
  4. WHERE  text.PageID_FK=1
  5. AND text.LanguageID=1 /*where 1 is english*/
  6.  
May 22 '08 #12
pechar
56
If you use a INNER JOIN with an ON clause, you can specify how the tables are joined, so that you only get rows that you will actually use, removing the need to filter them later using a WHERE clause.

Which could look something like:
Expand|Select|Wrap|Line Numbers
  1. SELECT text.textValue, textType.typeName
  2. FROM text INNER JOIN textType
  3.   ON text.TypeID_FK = textType.TypeID
  4. WHERE  text.PageID_FK=1
  5. AND text.LanguageID=1 /*where 1 is english*/
  6.  
Great! Just the info I was hoping for you to give me.
Thanks a lot that clears a lot in my JOINS department :)

Thank you
Luk
May 22 '08 #13
ronverdonk
4,258 Expert 4TB
......I see INFORMATION_SCHEMA used frequenlty but never understood it. Would you be so kind and give me some more info? Maybe an example with actual table names? .....
INFORMATION_SCHEMA provides access to database metadata.

Metadata is data about the data, such as the name of a database or table, the data type of a column, or access privileges. Other terms that sometimes are used for this information are data dictionary and system catalog.
INFORMATION_SCHEMA is the information database, the place that stores information about all the other databases that the MySQL server maintains. Inside INFORMATION_SCHEMA there are several read-only tables. They are actually views, not base tables, so there are no files associated with them.

In effect, we have a database named INFORMATION_SCHEMA, although the server does not create a database directory with that name. It is possible to select INFORMATION_SCHEMA as the default database with a USE statement, but it is possible only to read the contents of tables. You cannot insert into them, update them, or delete from them.
See also Chapter 22. INFORMATION_SCHEMA Tables in the MySQL documentation.

Ronald
May 22 '08 #14
pechar
56
See also Chapter 22. INFORMATION_SCHEMA Tables in the MySQL documentation.

Ronald
Thanks for the info! Though when i try to do the following (as shown in MYSQL doc) I get an error stating:

Error Code : 1146
Table 'information_schema.tables' doesn't exist

Expand|Select|Wrap|Line Numbers
  1. mysql> SELECT table_name, table_type, engine
  2.     -> FROM information_schema.tables
  3.     -> WHERE table_schema = 'my_database_name'
  4.     -> ORDER BY table_name DESC;
Anyway don't bother I'll try to solve it somehow

Thanks a lot
Luk
May 22 '08 #15
pechar
56
Hi Atli and guys,

Back with one more question.

I dont know if you or anyone could give me a hint on how I could change my database design for multilingual purposes. I'm trying to do the following:

So I have a table design similar to what you had told me: i.e. 4 tables:

Expand|Select|Wrap|Line Numbers
  1. language - stores details on language
  2. language_id_pk       
  3. language_description 
  4. language_culture_info
  5. language_align       
  6. language_meta_data   
  7. language_default
  8.  
  9. blob - stores actual text/html
  10. blob_id_pk     
  11. blob_text      
  12. blob_type_id_fk
  13. page_id_fk     
  14. language_id_fk
  15.  
  16. blob_type - stores the blob types ex page title, back_button
  17. blob_type_id_pk
  18. blob_type_name 
  19.  
  20. page - stores info on pages
  21. page_id_pk       
  22. page_description 
  23. page_static_link 
  24. page_parent_id_fk
  25. page_order_number
  26. page_deleted     
  27. page_hidden      
  28. show_in_sitemap  
  29. publish_from_date
  30. publish_to_date
This works great with the pages and also with entities on the page like buttons labels etc.
Unfortunately this design won't work with images.

I have a table called images which has a field alt_text (which was meant to store alternate image text) but this also needs to be multilingual so I'd ideally point to the blobs table as I did with the pages but the problem is that images are not and cannot be associated to a certain page and so don't have a pageId. This is because they can be reused. The same goes for email templates (ex email headers and footers would ideally be blobs also reused in the table email_templates)

Could you please guide me to what I could do? I don't think creating extra blob tables for both table email_templates and images would be a good idea. Could I somehow combine it into at least one? Any other ideas

Thanks a lot
Luk
May 29 '08 #16
Atli
5,058 Expert 4TB
I think the best way to handle this would be to simply create separate blob tables for the images and email_templates.

The current blob table is linked to specific pages. Given that your images and templates are not, you really should not try to mix the data up.

You probably could if you really wanted to, but It would most likely create a big mess and add an unnecessary layer of confusion.

The best method I could think of for storing this data in the current layout would be to add fake pages, one for the images and for for the templates, and create a blob_type for each image / template so it can be referenced.

But like I say... it would be a big mess.
You are far better of with the two extra tables, in my opinion.
May 30 '08 #17
pechar
56
I think the best way to handle this would be to simply create separate blob tables for the images and email_templates.

The current blob table is liked to specific pages. Given that your images and templates are not, you really should not try to mix the data up.

You probably could if you really wanted to, but It would most likely create a big mess and add an unnecessary layer of confusion.

The best method I could think of for storing this data in the current layout would be to add fake pages, one for the images and for for the templates, and create a blob_type for each image / template so it can be referenced.

But like I say... it would be a big mess.
You are far better of with the two extra tables, in my opinion.
Sorry for late reply.

Thanks a lot you've answered my question perfectly

Regards
Luk
Jun 10 '08 #18

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Matthias Ainsworth | last post: by
1 post views Thread by Christiaan | last post: by
1 post views Thread by James | last post: by
1 post views Thread by PowerLifter1450 | last post: by
2 posts views Thread by daniel | last post: by
Atli
6 posts views Thread by Atli | last post: by
3 posts views Thread by len | last post: by
reply views Thread by Edwin.Madari | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.