Insert Common Field Into Two Linked Tables At Once

[banacan]

I've created a page with one form and two INSERT commands to add content to two tables. They are linked by a common field, page_id. In the past there were two separate pages, one for each section to INSERT the content. The first page set the page_id using AUTO-INCREMENT, then at the next page, the user would select the page to link and the page_id would be inserted into that table. Now I want to make one page that will INSERT a unique value into the common field of both tables at once so they can be linked. I realize that the common field cannot be the AUTO-INCREMENT field from the one table as before, so my question is, what is the best way to go about this. How do I generate a unique value to input into each table? Should I QUERY the primary table, find the highest number in that field and add 1?

 

Any suggestions would be helpful.

[Larry]

I suspect you should still use auto_increment, but I'm not exactly clear as to what's going on. How about posting the table definitions, or the relevant parts?

[banacan]

Hi Larry,

 

Thanks for your reply. Basically I'm creating something like you have in chapter 2, Modularizing a Web Site, but I'm getting all of the content for each page from a database (two tables). One table contains all page data, like Page Title, Meta Description, Navigation Name, Link Name, Navigation Title, PID (parent ID for sub-navigation), Order (navigation order), etc., the other table contains all of the page content, like Copy, Headings, Images, etc.

 

As I said in my previous post, I originally had this as a two-step process. The first step was to provide the page data through one page/form, then the next step, on a different page/form, the page content would be entered and linked to the correct page data record by storing it's page_id in a field called page_id. Then using a left join in my query, the content would be assembled on the web page.

 

Now I want to eliminate one step in the process and have one page/form that inputs data into two tables. But as I say, the first table, "w_page", contains the page data, and the page_id in that table is auto-increment. The other table, "webtext", contains the page content, and its page_id field was set by the user. So I'm trying to figure out how to set the page_id value in both tables so I can keep them linked. I like having the page_id set by auto-increment because the database will insure that the value is always unique, but it leaves me with the problem of getting that value into the webtext table at the same time.

 

I hope this gives you a better idea of what I'm trying to accomplish. Now I'll list the table structure for your information.

 

Table

 

w_page

 

page_id - key field (auto-increment) int(10)

page_name - varchar

nav_name - varchar

link_name - varchar

title - varchar

navInclude - enum (y/n)

subnavInclude - enum (y/n)

pid - int(2)

page_title - varchar

meta_desc - varchar

order - int(2)

 

webtext

 

copy_id - key field (auto-increment) int(10)

headline - varchar

deck - varchar

subhead - varchar

body_copy - text

image - varchar

caption - varchar

page_id - int(10)

 

There are many more field in the webtext table but they are identical like subheads 1-4, body_copy 1-4, etc. but this gives you the idea.

 

Any suggestions you might have I would appreciate hearing.

 

Thanks for your help Larry. I hope you and your family are well.

 

Best,

 

Brett

[Larry]

My first thought is that I don't understand why these aren't one table. If you have a one-to-one relationship between two tables like you do, why have two tables?

 

But the answer for your situation is that you create the record in the first table, call the mysqli_insert_id() function to get that table's just created primary key, then you can use that value in the second query.

[banacan]

Larry,

 

Yeah, it probably would be simpler to make this all one table - though I will have a bit of housekeeping to do. I guess I'll get to work combining these tables, changing queries, etc.

 

Thanks for providing perspective.