CODEWITHSUNDEEP
mysql
Cory
Cory

Reputation: 742

MySQL Insert Select

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);
}

?>

Upvotes: 2

Views: 679

Answers (5)

Asaph
Asaph

Reputation: 162851

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.

Upvotes: 1

VoteyDisciple
VoteyDisciple

Reputation: 37813

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.

Upvotes: 4

MarkR
MarkR

Reputation: 63616

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.

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425753

INSERT
INTO    forum_threads (…)
VALUES  (…)

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

Upvotes: 1

Jan Hančič
Jan Hančič

Reputation: 53929

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

Upvotes: 3

Related Questions