Page 1 of 1

inserting into multiple tables

Posted: Thu Jan 28, 2010 1:11 am
by pavanesh2009
Hi ALL,

I have two table Trees & Tree_measurement. trees table has tree_id which is auto incremented value, just after querying in trees table i need to insert same tree_id into tree_measurement table as well.

my code:

<?php
include 'dbc.php';

$sql1 = "INSERT INTO trees
(tree_desc,is_fertilised,
is_watered,species_id,tree_location)
VALUES
('$_POST[tree_desc]',
'$_POST[is_fertilized]',
'$_POST[is_watered]',
'$specdata',
'$_POST[tree_location]'
)";
mysql_query($sql1,$link)or die("Insertion Failed:" .mysql_error());

$result = mysql_query("SELECT * FROM trees order by tree_id") or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
$num=mysql_numrows($result);
$i=0;
while ($i < $num) {
$row=mysql_result($result,$i,"tree_id");
//$result2=mysql_query("INSERT into user_tree_table(`tree_id`) VALUES('".$row."')") or die(mysql_error());

$sql2 = "INSERT INTO user_tree_table
(tree_id,tree_nickname,user_id)
VALUES
('$row',
'$_POST[tree_nickname]',
'$_POST[user_id]'
)";

mysql_query($sql2,$link) or die("Insertion Failed:" .mysql_error());

$sql3 = "INSERT INTO tree_measurement
(tree_id,user_id,date_of_measurement,tree_girth,tree_height,tree_damage)
VALUES
('$row',
'$_POST[user_id]',
now(),
'$_POST[tree_girth]',
'$_POST[tree_height]',
'$_POST[tree_damage]'
)";
mysql_query($sql3,$link) or die("Insertion Failed:" .mysql_error());

//echo "Done id: ".$row.". <br />";
$i++;
}
}
?>
While Inserting fields i am getting more than one entries in tree_measurement table for single query execution?
Please suggest where i am doing mistake.Thanks in Advance!!

Re: inserting into multiple tables

Posted: Thu Jan 28, 2010 8:14 am
by JakeJ
right after you insert the tree data, run this:

$tree_id = mysql_insert_id();

Then construct your other queries to use $tree_id since it will contain the auto increment number from the most recent query (your insert). Please see the manual on mysql_insert_id for more information.

Re: inserting into multiple tables

Posted: Fri Jan 29, 2010 8:41 am
by pavanesh2009
Hi JakeJ,

Thanks you very much for help, I tried the way you mentioned here but at first insertion evrything is fine but if if i am posting form second time again it inserting two entries.
If i am echoing the result after mysql_insert_id() i am getting two entries 265171 here 265 is real but don't how 171 comes After seeing it on phpmyadmin I found 171 is the last entered measurement_id for table tree_measurement.

Why it is considering measurement_id to tree_id(while inserting data into tree_measurement table)

lot's of question,kindly don't mind Please suggest!!
Thanks in Advance!!

JakeJ wrote:right after you insert the tree data, run this:

$tree_id = mysql_insert_id();

Then construct your other queries to use $tree_id since it will contain the auto increment number from the most recent query (your insert). Please see the manual on mysql_insert_id for more information.

Re: inserting into multiple tables

Posted: Fri Jan 29, 2010 4:54 pm
by JakeJ
I don't know either. Please post your code showing how you used mysql_insert_id().

Re: inserting into multiple tables

Posted: Mon Feb 08, 2010 5:13 am
by pavanesh2009
Thanks you So much dear JakeJ!!

As you said now my code works perfectly!!

Can you please suggest, if I have one table called Species_master(this table already consist of species details & their respective species_id.), Now I need to insert user selected(I have used ajax drop down) species_id into a table called 'trees' while I am inserting some other form fields using INSERT INTO table name....$_POST[is_fertilised], In this case how i can retrieve user selected Species_id for insertion ? is it possible in a single query?

Thanks once again!!



JakeJ wrote:I don't know either. Please post your code showing how you used mysql_insert_id().

Re: inserting into multiple tables

Posted: Mon Feb 08, 2010 10:48 am
by JakeJ
I'm not entirely sure I understand your question. Posting code always helps. I don't know much about Ajax but anything a user selects should be going to a $_POST[] variable which you can then use any way you want.

One thing to keep in mind about mysql_insert_id() is that it must be used before another query is made. You can use it again after each query to get the auto increment id that was just created.

If for some reason that's not convenient and you want to get the most recent record, try this query "SELECT max(id) FROM table"

Re: inserting into multiple tables

Posted: Wed Feb 10, 2010 7:08 am
by pavanesh2009
Hi JakeJ!!

Thanks once again for your time.Regarding mysql_insert_id() I am slightly confuse here, you said
that it must be used before another query is made, Well I used three queries first one is for Inserting data into table1(tree_id as PK) then as u suggested I used $tree_id = mysql_insert_id();
& just after this I have written another insert queries which is inserting $tree_id into table2 .....& then after doing this I have not written $tree_id=mysql_insert_id() but still i am able to insert same $tree_id value in table3.

So, Shall I need to write $tree_id=mysql_insert_id() again before I am inserting data into table3.

Thanks once Again!!


JakeJ wrote:I'm not entirely sure I understand your question. Posting code always helps. I don't know much about Ajax but anything a user selects should be going to a $_POST[] variable which you can then use any way you want.

One thing to keep in mind about mysql_insert_id() is that it must be used before another query is made. You can use it again after each query to get the auto increment id that was just created.

If for some reason that's not convenient and you want to get the most recent record, try this query "SELECT max(id) FROM table"

Re: inserting into multiple tables

Posted: Wed Feb 10, 2010 9:50 am
by JakeJ
If you're inserting the id from the first query in to the other two tables, there is no reason to retrieve the new id from each one.

For example, I have an application where the client enters initial details, submits and then goes to the next screen. When the submit button is hit, it generates an insert query. I use mysql_insert_id() and then use that id as the client_id in my other tables. My other tables have their own autoincrement id but it's not necessary for me to retrieve them.

I hope this helps you more than confuses you. I am probably not understanding what you are trying to accomplish.

Re: inserting into multiple tables

Posted: Tue Feb 16, 2010 10:33 pm
by pavanesh2009
Thanks JakeJ!!
No more confusions.. :D here is brief idea what I am trying to accomplish..?

On submit button click I am inserting data into three tables simultaneously, and a field of first table, I am using in second insertion query(for table 2), so for that used mysql_insert_id() as per your suggestion, & now it works like a charm.Hope this time I am more clear.. :)

Thanks once again!!

JakeJ wrote:If you're inserting the id from the first query in to the other two tables, there is no reason to retrieve the new id from each one.

For example, I have an application where the client enters initial details, submits and then goes to the next screen. When the submit button is hit, it generates an insert query. I use mysql_insert_id() and then use that id as the client_id in my other tables. My other tables have their own autoincrement id but it's not necessary for me to retrieve them.

I hope this helps you more than confuses you. I am probably not understanding what you are trying to accomplish.

Re: inserting into multiple tables

Posted: Wed Feb 17, 2010 9:09 am
by JakeJ
It all works now? Great! I'm glad I could help.