phpmysqliinsertinsert-id

Trying to insert into two tables using php and mysqli


I'm working on a blog with posts. I would like to insert into 3 tables

Posts, Categories and Post_categories(to show the right category for a post)

I tried everything using mysqli such as:

$titel  = $_POST['titel'];
$post   = $_POST['post-text'];

$sql=
"INSERT INTO posts (titel, post)
VALUES ('$titel', '$post')
INSERT INTO post_categories (idposts)
VALUES ('". $mysqli->insert_id() ."')
";

if (!mysqli_query($link,$sql))
  {
  die('Error: ' . mysqli_error($link));
  }
echo "1 record added";
mysqli_close($link);

But this didn't work. I'm stuck for two hours now, and i'm almost giving up on this. I really don't know how to do this in Mysqli. I knew how it worked in Mysql.

Hope someone can help me out with this.

My table structure looks like this:

Posts

idposts
titel
post
added

Categories

idcategories
category

post_categories

id
idposts
idcategories


Solution

  • Since you are inserting into multiple table try using transactions also. Also you cannot get the mysql_insert_id() before the query gets executed.

    mysqli_autocommit($link, FALSE);
    $sql = "INSERT INTO posts (titel, post) VALUES ('".$titel."', '".$post."')";
    if (mysqli_query($link, $sql ) === TRUE) {
      $postId = mysqli_insert_id($link);
       $sql1 = "INSERT INTO post_categories (idposts) VALUES ('".$postId."')";
       mysqli_query($link, $sql1 );
    }
    if (!mysqli_commit($link)) { //commit transaction
       print("Table saving failed");
        exit();
    }