Trying to insert into two tables using php and mysqli

Question

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

Search on google for "mysql insert into multiple tables". There's a couple of examples like this one : http://stackoverflow.com/questions/10043887/sql-insert-into-multiple-tables Basically, if you don't make a "stored proc", it won't be possible. You need to insert the first one, then the second, etc. — mogosselin, Jan 19 '14 at 14:16

score -1 · Answer 1 · answered Jan 19 '14 at 14:15

-1

You can't execute two queries at same time using mysqli_query, instead you should use mysqli_multi_query if you really want to do so

answered Jan 19 '14 at 14:15

Manish Goyal

700
1
7
17

Is that the best way of inserting to two tables? I'm not sure. – Kees Sonnema Jan 19 '14 at 14:16

score -1 · Accepted Answer · answered Jan 19 '14 at 14:16

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

This worked like a charm! Thanks Sir. I'm fairly new to Mysqli, because my last 'big' project was with old shitty mysql. — Kees Sonnema, Jan 19 '14 at 14:21

Trying to insert into two tables using php and mysqli

2 Answers2