How to insert into multiple table in mysql database with single query??

Question

How can i read and insert data from multiple table....and how can i relate those foreign key together??

Answer 1

Hello,

You can read from several tables with one statement, just join the tables e.g. on PK <=> FK.

But with one statement you can only insert data into one table, so you have to write separate insert statements for each table.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

----read data

SELECT <columns> FROM t1 JOIN t2 ON t1.pk=t2.fk JOIN t3 ON.....

---insert data

INSERT INTO t5 (columns) SELECT <columns> FROM t1 JOIN t2 ON t1.pk=t2.fk JOIN t3 ON.....

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 3

Okay thank you but how can i relate pk with fk ?? if i could do it, i can get thru this ..... cheers

Answer 4

<?php

  require_once "db_function.php";  

  $student[id]=$_POST['id'];
  $student[fname]=$_POST['fname'];
  $student[surname]=$_POST['surname'];
  $student[student_email]=$_POST['student_email'];
  $student[passwd]=$_POST['passwd'];
  $student[account_lock]=$_POST['account_lock'];
  $student1[student_id]=$_POST['id'];
  $student1[date_enrolled]=$_POST['date_enrolled'];
  $student1[date_completed]=$_POST['date_completed'];
  $student1[date_expected_finish]=$_POST['date_expected_finish'];
  $student1[scholarship]=$_POST['scholarship'];
  $student1[date_thesis_intend_submit]=$_POST['date_thesis_intend_submit'];
  $student1[date_thesis_submit]=$_POST['date_thesis_submit'];
  $student1[thesis_title]=$_POST['thesis_title'];
  $student1[fk_degree_type]=$_POST['fk_degree_type'];
  $student1[date_confirmation_intended]=$_POST['date_confirmation_intended'];
  $student1[date_confirmation_completed]=$_POST['date_confirmation_completed'];

  insertRecord($student);
  enrollStudent($student1);
  header("location:student_list.php");
  exit();
?>

is this statement okay to insert data into different table??

        

Answer 5

$sql = "INSERT into student_degree(student_id, date_enrolled, date_completed, date_expected_finish, scholarship, date_thesis_intend_submit, date_thesis_submit, thesis_title, fk_degree_type, date_confirmation_intended, date_confirmation_completed)   values('{$student1[student_id]}','{$student1[date_enrolled]}','$student1[date_completed]','$student1[date_expected_finish]' , '{$student1[scholarship]}','{$student1[date_thesis_intend_submit]}' ,'{$student1[date_thesis_submit]}','$student1[thesis_title]','$student1[fk_degree_type]' ,'$student1[date_confirmation_intended]','$student1[date_confirmation_completed]')";

is there any mistake on this query?? I have been trying to figure it out but couldn't

Answer 6

Are you attempting this query and receiving an error? If so, what is the error?

---

Regards, Matt Bowler MCITP, My blog