问题描述:

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

}

?>

网友答案:

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

网友答案:

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.

网友答案:
INSERT
INTO    forum_threads (…)
VALUES  (…)

INSERT
INTO    forum_posts (thread_id, …)
VALUES  (LAST_INSERT_ID(), …)
网友答案:

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.

网友答案:

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.

相关阅读:
Top