Insert data value from another tables

Question

LWS 0 Newbie Poster

13 Years Ago

Here is sample tables

Case
Case_ID AC_ID ASSET_ID

Action
Action_ID Case_ID Step_No Date_Started Date_Completed Status

Procedure
P_ID Step_No Name AU_ID AC_ID
1 1 Step1a 1 1
2 2 Step2b 1 1
3 3 Step3c 1 1
4 1 Step1a 2 1
5 2 Step2b 2 1
6 3 Step1c 2 1
7 4 Step1d 2 1
8 1 Step1a 1 2
9 2 Step2b 1 2
10 3 Step3c 1 2

Asset
ASSET_ID AU_ID AC_ID
1 1 1
2 1 1
3 1 2
4 1 2
5 2 1
6 2 1
7 2 2
8 2 2

This submit form that I done,

<html>
<form method="post" action="insertvalue.php" enctype="multipart/form-data">
<table width="100%" border="0" cellpadding="3" cellspacing="1" bgcolor="#EBDDE2">
<tr><td >Asset ID :</td><td width="260"><input type="text" size="33" name="ASSEET_ID" maxlength="100"  /></td></tr>
<tr><td >Asset Category :</td><td><select name="cat" style="width: 226px;">
<option value="1"> Post Office    </option>
<option value="2"> Education     </option> </td></tr>  
<tr><td></td><td><input type="submit" name="Submit" value="Submit</td></tr>
</table>
</form>	
</html>

Now, my problem is how to insert values from the submit form into the table Case and Action. The result will be like the tables below.
If user submit ASSET_ID = 3 and AC_ID = 2

Case
Case_ID AC_ID ASSET_ID
1 2 3

Action
Action_ID Case_ID Step_No Date_Started Date_Completed Status
1 1 1 - - -
2 1 2 - - -
3 1 3 - - -

Note: Action_ID and Case_ID are auto increment value , I’m using SQL database
Appreciate your help....

php

3 Contributors
6 Replies
139 Views
1 Week Discussion Span
Latest Post 13 Years Ago Latest Post by LWS

smantscheff 265 Veteran Poster

13 Years Ago

The question is, which values do you want to insert? First write a SELECT query which retrieves them, then build an INSERT query from there, in the form of
INSERT INTO case SELECT ...
INSERT INTO action SELECT ...
By the way, it's a bad idea to call a table "case" because case is a reserved word in nearly every programming language, including MySQL.

Daniel Liwonde 0 Newbie Poster

13 Years Ago

TRY THIS: first, you can recieve the data from the form and store it in some variable
eg: $case_no = $_POST; do that with all your posted data from the form.after that, retrieve the data which you want to insert into the next table and keep it in variables as well
eg: $myquery= SELECT* FROM asset WHERE......;
Then you can keep the required data(the one you have retrived to insert into another table) in variables as well
eg: $selected = $myquery[asset_no];
$selected2 =$myquery[asset_type];
This means that you have now both your data from the table and from the form keept
in variables. then after this you can now insert the data into another table
eg: mysql_query("INSERT INTO (YOUR new table name i.e the one you want to insert the combined data from the form and the other table HERE)( column1,column2,column3...) VALUES('$selected','$selected2','$case_no'.....)") you can see now that I have included "$case_no" which is the form .after executing this I think it will work.thank you

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

LWS 0 Newbie Poster · Answer 1 · 2011-02-16T17:42:16+00:00

The question is, which values do you want to insert? First write a SELECT query which retrieves them, then build an INSERT query from there, in the form of
INSERT INTO case SELECT ...
INSERT INTO action SELECT ...
By the way, it's a bad idea to call a table "case" because case is a reserved word in nearly every programming language, including MySQL.

Thanks for suggestion, I’ll change the table name later…
Actually, I wanna do form for User to insert value. User just need to insert value case.AC_ID and case.ASSET_ID thru the submit form. Then, all the data will be insert automatically into table Case and Action.

The value of Action.Step_No is refer to the table Procedure
The value of Action.Date_Started, Action.Date_Completed, Action.Status is -

Any idea?

LWS 0 Newbie Poster · Answer 2 · 2011-02-21T20:56:35+00:00

Thanks for suggestion, I’ll change the table name later…
Actually, I wanna do form for User to insert value. User just need to insert value case.AC_ID and case.ASSET_ID thru the submit form. Then, all the data will be insert automatically into table Case and Action.
The value of Action.Step_No is refer to the table Procedure
The value of Action.Date_Started, Action.Date_Completed, Action.Status is -
Any idea?

Can anyone help me??? i have no any idea to insert value from a form and other tables at the same time.

LWS 0 Newbie Poster · Answer 3 · 2011-02-25T10:04:50+00:00

TRY THIS: first, you can recieve the data from the form and store it in some variable
eg: $case_no = $_POST; do that with all your posted data from the form.after that, retrieve the data which you want to insert into the next table and keep it in variables as well
eg: $myquery= SELECT* FROM asset WHERE......;
Then you can keep the required data(the one you have retrived to insert into another table) in variables as well
eg: $selected = $myquery[asset_no];
$selected2 =$myquery[asset_type];
This means that you have now both your data from the table and from the form keept
in variables. then after this you can now insert the data into another table
eg: mysql_query("INSERT INTO (YOUR new table name i.e the one you want to insert the combined data from the form and the other table HERE)( column1,column2,column3...) VALUES('$selected','$selected2','$case_no'.....)") you can see now that I have included "$case_no" which is the form .after executing this I think it will work.thank you

thanks for your help...
But i stil can do it.....sad....

i hav four tables which name as complaint_case, maintenance_procedure,assets, action.

complaint_case
-----------------------------------------
CASE_ID | NAME | ASSET_ID |AC_ID
------------------------------------------

action
----------------------------------------
ACTION_ID | CASE_ID | A_STEP_NO |NAME | DATE_UPDATE
---------------------------------------

assets
---------------------------------------------
ASSET_ID | AU_ID | AC_ID
--------------------------------------------
1 | 1 | 1
2 | 1 | 1
3 | 1 | 2

maintenance_procedure
----------------------------------------
P_ID | STEP_NO |STEP_NAME | AU_ID |AC_ID
-------------------------------------------
1 | 1 | step1 | 1 | 1
2 | 2 | step2 | 1 | 1
3 | 1 | step1a | 1 | 2
4 | 2 | step2a | 1 | 2
5 | 3 | step3a | 1 | 2

Here is the submit form

<table bgcolor="#FBEFEF" border="0" border color="black" valign="center">
		<td>
<form method="post" action="insert.php" enctype="multipart/form-data">
<td>
<table width="100%" border="0" cellpadding="3" cellspacing="1" bgcolor="#EBDDE2">
<tr>
<FONT SIZE=6 FACE=TIME NEW ROMAN COLOR=BLUE>
<td colspan="3" align="center" >E-Complaint Form </td>
</FONT>
</tr>

<tr>
<td width="150">Name</td>
<td width="6">:</td>
<td width="260"><input type="text" size="33" name="name" maxlength="100"  /></td>
</tr>
<tr>
<td>Asset Category</td>
<td>:</td>
<td><select name="assetcat" >
<option value="Null" selected>---------SELECT AN OPTION---------</option>
<option value="2" >         Education</option>
<option value="1" >          Post Office</option>
<option value="Others" >      OTHERS...</option>
</select></td>
</tr>

<tr>
<td>Asset ID</td>
<td>:</td>
<td><input type="text" name="assetid" maxlength="5" size="33" /></td>
</tr>
<tr>

<td>&nbsp;</td>
<td>&nbsp;</td>
<td><input type="submit" name="Submit" value="Submit"> &nbsp&nbsp&nbsp&nbsp<input type="reset" name="reset" value="Reset"></td>
</tr>
</table

Here is insert.php

//connect to dbs 

$name= $_POST[name];
$assetid= $_POST[assetid];
$assetcat= $_POST[assetcat];


$sql="INSERT INTO complaint_case(NAME, AC_ID, ASSET_ID) 
 VALUES('$name','$assetcat','$assetid')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
$last_insert_CD_id = mysql_insert_id();


$myquery = "SELECT * FROM maintenance_procedure, assets WHERE assets.AU_ID =maintenance_procedure.AU_ID AND assets.ASSET_ID =$_POST[assetid]";

$selected = $myquery['STEP_NO'];


$sql1="INSERT INTO action (CASE_ID, A_STEP_NO ) VALUES ('$last_insert_CD_id','$selected')";

if (!mysql_query($sql1,$con))
 {
  die('Error: ' . mysql_error());
 }
$last_insert_AP_id = mysql_insert_id();

I wana get the result is like the tables below, if user submit AC_ID = 2 , ASSET_ID = 3

action
--------------------------------------------------------
ACTION_ID | CASE_ID | A_STEP_NO | NAME | DATE_UPDATE
-------------------------------------------------------
1 | 1 | 1 | step1a | -
2 | 1 | 2 | step2a | -
3 | 1 | 3 | step3a | -

ACTION_ID and CASE_ID are auto increment value, i hope my question is clear enough.
i had try the method above , but its no work...i able insert value to table complaint_case, but only insert value action.CASE_ID into the 1st row....
i dont know what's problem in my coding..

really appreciate for help...

LWS 0 Newbie Poster · Answer 4 · 2011-02-26T18:39:26+00:00

thanks for your help...
But i stil can do it.....sad....
i hav four tables which name as complaint_case, maintenance_procedure,assets, action.
complaint_case
-----------------------------------------
CASE_ID | NAME | ASSET_ID |AC_ID
------------------------------------------
action
----------------------------------------
ACTION_ID | CASE_ID | A_STEP_NO |NAME | DATE_UPDATE
---------------------------------------

assets
---------------------------------------------
ASSET_ID | AU_ID | AC_ID
--------------------------------------------
1 | 1 | 1
2 | 1 | 1
3 | 1 | 2
maintenance_procedure
----------------------------------------
P_ID | STEP_NO |STEP_NAME | AU_ID |AC_ID
-------------------------------------------
1 | 1 | step1 | 1 | 1
2 | 2 | step2 | 1 | 1
3 | 1 | step1a | 1 | 2
4 | 2 | step2a | 1 | 2
5 | 3 | step3a | 1 | 2

Here is the submit form
<table bgcolor="#FBEFEF" border="0" border color="black" valign="center">
		<td>
<form method="post" action="insert.php" enctype="multipart/form-data">
<td>
<table width="100%" border="0" cellpadding="3" cellspacing="1" bgcolor="#EBDDE2">
<tr>
<FONT SIZE=6 FACE=TIME NEW ROMAN COLOR=BLUE>
<td colspan="3" align="center" >E-Complaint Form </td>
</FONT>
</tr>

<tr>
<td width="150">Name</td>
<td width="6">:</td>
<td width="260"><input type="text" size="33" name="name" maxlength="100"  /></td>
</tr>
<tr>
<td>Asset Category</td>
<td>:</td>
<td><select name="assetcat" >
<option value="Null" selected>---------SELECT AN OPTION---------</option>
<option value="2" >         Education</option>
<option value="1" >          Post Office</option>
<option value="Others" >      OTHERS...</option>
</select></td>
</tr>

<tr>
<td>Asset ID</td>
<td>:</td>
<td><input type="text" name="assetid" maxlength="5" size="33" /></td>
</tr>
<tr>

<td>&nbsp;</td>
<td>&nbsp;</td>
<td><input type="submit" name="Submit" value="Submit"> &nbsp&nbsp&nbsp&nbsp<input type="reset" name="reset" value="Reset"></td>
</tr>
</table
Here is insert.php
//connect to dbs 

$name= $_POST[name];
$assetid= $_POST[assetid];
$assetcat= $_POST[assetcat];


$sql="INSERT INTO complaint_case(NAME, AC_ID, ASSET_ID) 
 VALUES('$name','$assetcat','$assetid')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
$last_insert_CD_id = mysql_insert_id();


$myquery = "SELECT * FROM maintenance_procedure, assets WHERE assets.AU_ID =maintenance_procedure.AU_ID AND assets.ASSET_ID =$_POST[assetid]";

$selected = $myquery['STEP_NO'];


$sql1="INSERT INTO action (CASE_ID, A_STEP_NO ) VALUES ('$last_insert_CD_id','$selected')";

if (!mysql_query($sql1,$con))
 {
  die('Error: ' . mysql_error());
 }
$last_insert_AP_id = mysql_insert_id();
I wana get the result is like the tables below, if user submit AC_ID = 2 , ASSET_ID = 3

complaint_case
-----------------------------------------
CASE_ID | NAME | ASSET_ID |AC_ID
------------------------------------------
1 | John | 3 | 2

action
--------------------------------------------------------
ACTION_ID | CASE_ID | A_STEP_NO | NAME | DATE_UPDATE
-------------------------------------------------------
1 | 1 | 1 | step1a | -
2 | 1 | 2 | step2a | -
3 | 1 | 3 | step3a | -

ACTION_ID and CASE_ID are auto increment value, i hope my question is clear enough.
i had try the method above , but its no work...i able insert value to table complaint_case, but only insert value action.CASE_ID into the 1st row....
i dont know what's problem in my coding..
really appreciate for help...

Any comment ?? i need to solve this problem urgently....Anybody can help me??