MYSQL: Get the ID during INSERT

[pechar]

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

INSERT INTO tb_page
    (page_description, page_name_blob)
    VALUES
    ('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

[rpnew]

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

INSERT INTO tb_page
    (page_description, page_name_blob)
    VALUES
    ('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

[pechar]

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:

INSERT INTO tb_page
    (page_description, page_name_blob)
VALUES
    ('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

[ronverdonk]

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

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

[Atli]

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.

[pechar]

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

[pechar]

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

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

[Atli]

There is a better way.

I tried to explain it, but It didn't make sense :P
Consider this instead:

/* Info on all your pages */
CREATE TABLE page (
    PageID INT Not Null Auto_Increment Primary Key,
    PageDescription Text Not Null,
    /* etc */
);
 
/* Contains all the languages available */
CREATE TABLE language (
    LanguageID INT Not Null Auto_Increment Primary Key,
    LanguageName VarChar(100) Not Null Default 'English'
); 
 
/* Contains all the available types of text
 * stored in your text table */
CREATE TABLE textType (
    TypeID INT Not Null Auto_Increment Primary Key,
    TypeName VarChar(100) Not Null Default 'Title'
)
 
/* Stores all text and references the other tables
 * for info on what type and language it is,
 * as well as which page it belongs to */
CREATE TABLE text (
    TextID INT Not Null Auto_Increment Primary Key,
    TextValue Text Not Null,
    TypeID_FK INT Not Null References textType(TypeID),
    PageID_FK INT Not Null References page(PageID),
    LanguageID INT Not Null References language(LanguageID)
)
 

Let me know if something isn't making sense.

[pechar]

There is a better way.

I tried to explain it, but It didn't make sense :P
Consider this instead:

/* Info on all your pages */
CREATE TABLE page (
    PageID INT Not Null Auto_Increment Primary Key,
    PageDescription Text Not Null,
    /* etc */
);
 
/* Contains all the languages available */
CREATE TABLE language (
    LanguageID INT Not Null Auto_Increment Primary Key,
    LanguageName VarChar(100) Not Null Default 'English'
); 
 
/* Contains all the available types of text
 * stored in your text table */
CREATE TABLE textType (
    TypeID INT Not Null Auto_Increment Primary Key,
    TypeName VarChar(100) Not Null Default 'Title'
)
 
/* Stores all text and references the other tables
 * for info on what type and language it is,
 * as well as which page it belongs to */
CREATE TABLE text (
    TextID INT Not Null Auto_Increment Primary Key,
    TextValue Text Not Null,
    TypeID_FK INT Not Null References textType(TypeID),
    PageID_FK INT Not Null References page(PageID),
    LanguageID INT Not Null References language(LanguageID)
)
 

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:

SELECT TextValue, TypeID_FK 
FROM text
WHERE PageID_FK=25 
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

[Atli]

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:

SELECT TextValue, TypeID_FK 
FROM text
WHERE PageID_FK=25 
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:

SELECT text.textValue, type.typeName
FROM text
INNER JOIN language AS lang
    ON text.LanguageID_FK = lang.LanguageID
    AND lang.LanguageName = 'English'
INNER JOIN textType AS type
    on text.TypeID_FK = type.TypeID
    AND type.TypeName IN('page_text', 'page_header', 'back_button')
WHERE text.PageID = 25
 

[pechar]

If you were to use joins, it would give you a much greater control over the whole thing.
Consider this:

SELECT text.textValue, type.typeName
FROM text
INNER JOIN language AS lang
    ON text.LanguageID_FK = lang.LanguageID
    AND lang.LanguageName = 'English'
INNER JOIN textType AS type
    on text.TypeID_FK = type.TypeID
    AND type.TypeName IN('page_text', 'page_header', 'back_button')
WHERE text.PageID = 25
 

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:

SELECT text.textValue, textType.typeName
FROM text, textType
WHERE text.TypeID_FK = textType.TypeID
AND text.PageID_FK=1
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

[Atli]

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:

SELECT text.textValue, textType.typeName
FROM text INNER JOIN textType
  ON text.TypeID_FK = textType.TypeID
WHERE  text.PageID_FK=1
AND text.LanguageID=1 /*where 1 is english*/
 

[pechar]

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:

SELECT text.textValue, textType.typeName
FROM text INNER JOIN textType
  ON text.TypeID_FK = textType.TypeID
WHERE  text.PageID_FK=1
AND text.LanguageID=1 /*where 1 is english*/
 

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

[ronverdonk]

......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

[pechar]

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

mysql> SELECT table_name, table_type, engine
    -> FROM information_schema.tables
    -> WHERE table_schema = 'my_database_name'
    -> ORDER BY table_name DESC;

Anyway don't bother I'll try to solve it somehow

Thanks a lot
Luk

[pechar]

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:

language - stores details on language
language_id_pk       
language_description 
language_culture_info
language_align       
language_meta_data   
language_default
 
 blob - stores actual text/html
blob_id_pk     
blob_text      
blob_type_id_fk
page_id_fk     
language_id_fk
 
 blob_type - stores the blob types ex page title, back_button
blob_type_id_pk
blob_type_name 
 
 page - stores info on pages
page_id_pk       
page_description 
page_static_link 
page_parent_id_fk
page_order_number
page_deleted     
page_hidden      
show_in_sitemap  
publish_from_date
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

[Atli]

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.

[pechar]

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