0

i m trying to add custom list of tags using excel file and I have done most of the work. but i am confused that how can I add same value in 2 different tables columns.

i had inserted the values in 

INSERT INTO `steve`.`wp_terms` (`term_id`, `name`, `slug`, `term_group`) VALUES (NULL, 'tag99', 'tag99', '0');

and 

INSERT INTO `steve`.`wp_term_taxonomy` (`term_taxonomy_id`, `term_id`, `taxonomy`, `description`, `parent`, `count`) VALUES (NULL, '21', 'post_tag', '', '0', '0');

now i want to add term_id in both table .. could somebody tell me how can i do that?

AND term_id is database generated value.

Muzzy
  • 51
  • 10
  • Can you refrase the question? Meaning, what problem are you facing? – Peon Oct 31 '12 at 13:47
  • http://stackoverflow.com/questions/5323528/get-the-id-of-last-inserted-records check this out and http://stackoverflow.com/questions/12548671/connecting-html-form-to-php-page-according-to-primary-key/12548743#12548743 – Rafee Oct 31 '12 at 13:48

3 Answers3

1
INSERT INTO `steve`.`wp_term_taxonomy` (`term_taxonomy_id`, `term_id`, `taxonomy`, `description`, `parent`, `count`) VALUES (NULL, LAST_INSERT_ID, 'post_tag', '', '0', '0');

If I follow correctly your first query inserts into a table and creates a new id for that record, then you want to use that ID in the next query to insert into a different table right? If that's the case the above query should work for your second one using LAST_INSERT_ID for the term_id value.

To update the question with your new code it should, I emphasize should, work with this:

 $sql2 = "INSERT INTO " . $table2 . " (`term_taxonomy_id`, `term_id`, `taxonomy`, `description`, `parent`, `count`) VALUES (NULL, LAST_INSERT_ID, 'post_tag', '', '0', '0')";

        $wpdb->query($sql);
        $wpdb->query($sql2);

The way you have it above you are overwriting your first query before you execute it.

Also I don't understand how you're inserting nulls into an auto-increment field, that alone should be throwing an error. Honestly both your queries should be leaving their base ids (the auto increment ones) out of the query entirely like this:

$sql2 = "INSERT INTO " . $table2 . " (term_id, taxonomy, description, parent, count) VALUES (LAST_INSERT_ID, 'post_tag', '', '0', '0')";

Rick Calder
  • 18,310
  • 3
  • 24
  • 41
  • yes you are right, i was confused to define myself, but you are absolutly correct. but can you give me a favour and tell me how can i figure out this – Muzzy Oct 31 '12 at 13:57
  • @Muzzy: You do not need additional queries to get what you want, just use `Alter` table as per my answer. In this case, having additional queries would result in slowing the performance, as the value in the `term_id` is generated by the DB. – Teena Thomas Oct 31 '12 at 14:17
  • How about you show the code how you think this could work, and then explain how alter table is faster. Read the manual isn't a terribly useful answer and so far you've done absolutely nada to prove that altering a table every time he inserts a new record would make any sense at all. – Rick Calder Oct 31 '12 at 14:19
  • Oh geez, he just needs to alter the table, just once. After that, for every new record, that column will be calculated just like the original table's column (which he is trying to replicate). – Teena Thomas Oct 31 '12 at 14:21
  • Are you serious? Two tables. One generates an ID one needs that ID. that does not mean the two will always be synced together >.< Think about what you're saying for a second. You can't have two auto increments that need to hold the SAME values. – Rick Calder Oct 31 '12 at 14:24
  • what you are saying makes sense, if suppose the table has already a bunch of rows in it, but if he has just say 1 record, considering he asked the question right away, then deleting that record manually, and then altering the table, and starting over, would be the right solution, instead of having an additional query for every record he wants to insert in the future. – Teena Thomas Oct 31 '12 at 14:29
  • Think about the situation like this. I have products and inventory. Now I can add a product to inventory right away by ID if that were the only way I ever added or removed product to inventory having the two columns as auto increment MIGHT work, but what if I alter either of those tables some other way? Now they're no longer synced. It just makes zero sense, so if you wouldn't mind pulling the downvote you gave me... – Rick Calder Oct 31 '12 at 14:31
  • I didn't downvote you buddy, but since you're answer does make sense in a particular scenario, I dont mind giving you a vote. – Teena Thomas Oct 31 '12 at 14:33
  • Ahh figured it was you because we disagreed. Gotta love anonymous downvotes lol. Cheers – Rick Calder Oct 31 '12 at 14:34
  • guys i have pasted my code, can you please check and let me what can i do? – Muzzy Oct 31 '12 at 14:39
  • i just added 2nd sql statement. please check now and tell me if i m going correct. – Muzzy Oct 31 '12 at 17:31
  • Nope, you just overwrote the first query with the second. The one thing I don't get is why you're inserting nulls on the auto increment fields. That should actually be throwing you an error if those fields are indeed set to auto increment in the database. – Rick Calder Oct 31 '12 at 17:33
1

Use mysql_insert_id()

mysql_query("INSERT INTO `steve`.`wp_terms` (`term_id`, `name`, `slug`, `term_group`) VALUES (NULL, 'tag99', 'tag99', '0')");

$lastInsertId = mysql_insert_id();

mysql_query("INSERT INTO `steve`.`wp_term_taxonomy` (`term_taxonomy_id`, `term_id`, `taxonomy`, `description`, `parent`, `count`) VALUES (NULL, '" . $lastInsertId . "', 'post_tag', '', '0', '0')");

NOTE: To use mysql_insert_id(), term_id in wp_terms must be set to auto increment.

Ravi
  • 2,078
  • 13
  • 23
0

you need to use ALTER table and add that column's definition/constraints into the other table, here's the manual

Teena Thomas
  • 5,139
  • 1
  • 13
  • 17
  • What does alter table have to do with the question at hand? Both tables already have the column he's trying to add the value to the column. – Rick Calder Oct 31 '12 at 14:03
  • `Alter` table can be used to have the same definition of the column `term_id` into the other table, since its the DB thats generating its value. – Teena Thomas Oct 31 '12 at 14:06
  • Please read the manual, `Alter` table can be also used to change the definition of an existing column. – Teena Thomas Oct 31 '12 at 14:13