MySQL Insert Select

Question

Ran into a bit of trouble when trying to insert records into my DB from my forum

What it does when you create a thread is make an entry into 2 tables. First the forum_threads table with information on the thread title, description, poster, post time, etc. It will use thread_id with AUTO_INTEGER to generate the threads ID.

I then need to get that thread_id from the forum_threads and then put that as the thread_id in the forum_posts table.

I'm not sure if theres anyway I can select a row based on its ID after I just inserted it. Would I just have to select the most recent ID? Would that leave a margin of error? Other thought I had was to select based on user name and post time.

Thoughts?

<?php

if (isset($_POST['submit'])) {
    $thread_sql = "
        INSERT INTO forum_threads (
            user_id,
            forum_id,
            thread_postdate,
            thread_title,
            thread_description,
            thread_icon
        ) VALUES (
            '$_SESSION[user_id]',
            '$_GET[f]',
            '$date',
            '$_POST[topictitle]',
            '$_POST[topicdescription]',
            '$_POST[posticon]'
        )
    ";
    $thread_query = @mysqli_query ($db_connect, $thread_sql);

    $post_sql = "
        INSERT INTO forum_posts (
            user_id,
            thread_id,
            post_message,
            post_date
        ) VALUES (
            '$_SESSION[user_id]',
            '',
            '$_POST[content]',
            '$date'
        )
    ";
    $post_query = @mysqli_query ($db_connect, $post_sql);
}

?>

VoteyDisciple · Accepted Answer · 2010-11-03 15:58:31Z

4

To first answer your question directly, the function mysql_insert_id() will return the ID that was assigned to the most recently inserted row. There's no guesswork involved; MySQL will happily tell you (through its own built-in LAST_INSERT_ID() function) what ID it assigned.

On a separate note, I see that you're directly inserting values from $_POST into your SQL statement. Never, ever, EVER do that. This exposes your application to SQL injection attacks.

Either use the mysql_real_escape_string() function to properly escape the values for use in a SQL statement, or, since you're already using mysqli_ functions, use placeholders (? values) to create a prepared statement.

answered Nov 3, 2010 at 15:58

VoteyDisciple

37.9k5 gold badges100 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jan Hančič · Accepted Answer · 2010-11-03 15:55:55Z

3

MySqli_Insert_Id () will return the last auto generated ID. Call this function immediately after you insert your new thread.

answered Nov 3, 2010 at 15:55

Jan Hančič

54k17 gold badges99 silver badges101 bronze badges

Comments

Quassnoi · Accepted Answer · 2010-11-03 15:56:53Z

1

INSERT
INTO    forum_threads (…)
VALUES  (…)

INSERT
INTO    forum_posts (thread_id, …)
VALUES  (LAST_INSERT_ID(), …)

answered Nov 3, 2010 at 15:56

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Comments

MarkR · Accepted Answer · 2010-11-03 15:56:59Z

1

You can just use the last_insert_id to get the ID of the row you just inserted; don't worry about anybody else inserting a row just after, the last_insert_id is per-connection, so unless they used your connection, you're safe.

There is an API-level call to retrieve this so you don't need to ask the server specifically.

Oh yes, also, do not use the @ operator in PHP, ever, google for it if you want to know why it's bad.

answered Nov 3, 2010 at 15:56

MarkR

63.8k15 gold badges120 silver badges156 bronze badges

Comments

Asaph · Accepted Answer · 2010-11-03 20:25:55Z

1

Use MySQL's last_insert_id() function in sql code or the php wrapper for it mysql_insert_id() in a php script. These will give you the last auto_increment number generated in your connection. So it's thread safe.

edited Nov 3, 2010 at 20:25

answered Nov 3, 2010 at 15:56

Asaph

163k25 gold badges204 silver badges204 bronze badges

Collectives™ on Stack Overflow

MySQL Insert Select

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related