[zz]利用PHP在mysql中同时插入多个表格（主、辅表）

view plaincopy to clipboardprint?

   1. /*************************by garcon1986*********************************************************/  
   2. <?php  
   3. // get the values from entreprise creation form  
   4. $secteur = $_POST['secteur'];  
   5. $nom = $_POST['nom_entreprise'];  
   6. $taille = $_POST['taille_entreprise'];  
   7. $concurrent = $_POST['concurrent_entreprise'];  
   8. $investisseur = $_POST['investisseur_entreprise'];  
   9. $partenaire = $_POST['partenaire_entreprise'];  
  10. $client = $_POST['client_entreprise'];  
  11. $fournisseur = $_POST['fournisseur_entreprise'];  
  12. $info_general = $_POST['information_generale'];  
  13. //connection  
  14. //include("./conn/connect.php");  
  15. $conn = mysql_connect("localhost","charles","charles") or die("connection failed: ".mysql_error());  
  16. mysql_select_db("crm") or die("select db failed: ".mysql_error());  
  17. //execution - using trasaction  
  18. mysql_query('START TRANSACTION',$conn) or die(mysql_error());  
  19. mysql_query("insert into entreprise(nom, nombre_salarie, secteur, info_general) values('".$nom."','".$taille."','".$secteur."','".$info_general."')",$conn) or die(mysql_error());  
  20. $id = mysql_insert_id($conn) or die(mysql_error());  
  21. mysql_query("insert into concurrent(entreprise_id, nom) values('".$id."', '".$concurrent."')",$conn) or die(mysql_error());  
  22. mysql_query("insert into investisseur(entreprise_id, nom) values('".$id."', '".$investisseur."')",$conn) or die(mysql_error());  
  23. mysql_query("insert into partenaire(entreprise_id, nom) values('".$id."', '".$partenaire."')",$conn) or die(mysql_error());  
  24. mysql_query("insert into client(entreprise_id, nom) values('".$id."', '".$client."')",$conn) or die(mysql_error());  
  25. mysql_query("insert into fournisseur(entreprise_id, nom) values('".$id."', '".$fournisseur."')",$conn) or die(mysql_error());  
  26. mysql_query('COMMIT')or die(mysql_error());  
  27. //display the information  
  28. echo '<h3>LES INFORMATION DE L\'ENTREPRISE</h3>';  
  29. echo '<table style="width:80%; margin: auto 0">';  
  30. echo '<th style="width:40%"></th><th stype="width:60%"></th>';  
  31. echo '<tr class="line_even" >';  
  32. echo '<td>Entreprise :</td>';  
  33. echo '<td>'.$nom.'</td>';  
  34. echo '</tr>';  
  35. echo '<tr class="line_odd">';  
  36. echo '<td>Son secteur : </td>';  
  37. echo '<td>'.$secteur.'</td>';  
  38. echo '</tr>';  
  39. echo '<tr class="line_even">';  
  40. echo '<td>Son taille : </td>';  
  41. echo '<td>'.$taille.'</td>';  
  42. echo '</tr>';  
  43. echo '<tr class="line_odd">';  
  44. echo '<td>Son concurrent : </td>';  
  45. echo '<td>'.$concurrent.'</td>';  
  46. echo '</tr>';  
  47. echo '<tr class="line_even">';  
  48. echo '<td>Son concurrent : </td>';  
  49. echo '<td>'.$investisseur.'</td>';  
  50. echo '</tr>';  
  51. echo '<tr class="line_odd">';  
  52. echo '<td>Son partenaire : </td>';  
  53. echo '<td>'.$partenaire.'</td>';  
  54. echo '</tr>';  
  55. echo '<tr class="line_even">';  
  56. echo '<td>Son fournisseur : </td>';  
  57. echo '<td>'.$fournisseur.'</td>';  
  58. echo '</tr>';  
  59. echo '<tr class="line_odd">';  
  60. echo '<td>Son client : </td>';  
  61. echo '<td>'.$client.'</td>';  
  62. echo '</tr>';  
  63. echo '<tr class="line_even">';  
  64. echo '<td>Son information generale : </td>';  
  65. echo '<td>'.$info_general.'</td>';  
  66. echo '</tr>';  
  67. echo '</table>';  
  68. ?>  

 利用mysql_insert_id()函数可以返回上一步insert操作所返回的ID，这个时候可以用来执行先插入主表，再插入子表

   1. <?php  
   2. //mysql code  
   3. /* 
   4. create table table1(id int(10) not null auto_increment, name varchar(20), primary key(id)); 
   5. create table table2(id int(10) not null auto_increment, t1id int(10), name varchar(20), primary key(id)); 
   6. create table table3(id int(10) not null auto_increment, t1id int(10), name varchar(20), primary key(id)); 
   7. alter table table2 add foreign key (t1id) references table1(id) on delete cascade; 
   8. alter table table3 add foreign key (t1id) references table1(id) on delete cascade; 
   9. insert into table1(name) values('t1_name'); 
  10. insert into table2(t1id, name) values(last_insert_id(), 't2_name'); 
  11. insert into table3(t1id, name) values(last_insert_id(), 't3_name'); 
  12. */  
  13. //php实现  
  14. error_reporting(E_ALL ^ E_NOTICE);  
  15. $conn = mysql_connect("localhost","charles","charles");  
  16. mysql_select_db('test');  
  17. $link = 0;  
  18. mysql_query('START TRANSACTION',$link);  
  19. mysql_query("insert into table1(name) values('t1_name')",$link);  
  20. $id = mysql_insert_id($link);  
  21. mysql_query("insert into table2(t1id, name) values($id, 't2_name')",$link);  
  22. mysql_query("insert into table3(t1id, name) values($id, 't3_name')",$link);  
  23. mysql_query('COMMIT');  
  24.   
  25. //mysql 实现   
  26. set @temp=0;  
  27. insert into table1(name) values('t1_name');  
  28. select mysql_insert_id() into @temp;  
  29. insert into table2(t1id, name) values(@temp, 't2_name');  
  30. insert into table3(t1id, name) values(@temp, 't3_name');  
  31. ?>