Databases
StackOverflow2013
Postsdbo
POPHP - mysql multiple table subqueries
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPHP - mysql multiple table subqueries
    primarykey
    data
    text
    <p>I create a table based using lots of foreign keys. What I want to do is how to use them properly according to their primary keys. If insert the values it will automatically insert it to subtables.</p> <p>The parent table would be the evaluation_tbl and its child table would be accomp_tbl,task_tbl, and employee_tbl, all these child tables are connected to the parent table via foreign key. While accomp_tbl has a child table called quality_tbl. Here's an example:</p> <pre><code>**| evaluation_tbl with 5 columns:|** *eval_id(pk) *percent *task_id(fk) *accomp_id(fk) *employee_id(fk) **| accomp_tbl with 2 columns |** *accomp_id(pk) *quality_id(fk) **| quality_tbl with 2 columns |** *quality_id(pk) *value **| task_tbl with 4 columns |** *task_id(pk) *task_name(fk) *task_sem(fk) *task_yr(fk) **| employee_tbl with 2 columns |** *employee_id(pk) *employee_name </code></pre> <p>All primary keys are auto-increment.</p> <p>Herees what I want to do:</p> <p>A <strong>form</strong> that represents the <strong>input for evaluation_tbl</strong>. I have to <strong>input the value for column name "weight", select a task_id, then input a value for column name "quality" which is a column in quality_tbl</strong>. Evaluation_tbl will query accomp_tbl while accomp_tbl will query quality_tbl. </p> <p><strong>If I perform an insert or delete for a particular evaluation_id, it will also do the action for accomp_id and quality_id</strong>. I believe this is a subquery with LEFT JOIN and foreign key constraint is cascade.</p> <p>But I don't know how exactly what to do. Please help.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USreggel
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COYou'll need to create views to link the multiple tables together into a single virtual table then you just insert/delete from the view as you would normally. If you attempt to do it on raw tables you'll have to do multiple inserts and multiple deletes etc
      singulars
      1. POPHP - mysql multiple table subqueries
      1. USDave
    2. COim thinking the same way because so that it can be controlled that way. but what if I delete a particular evaluation_id, it should automically delete the values to its child tables primarily accomp_tbl and quality_tbl. Can it be done simply?
      singulars
      1. POPHP - mysql multiple table subqueries
      1. USreggel
    3. COwell you'd delete evaulation_id from a master view that links accomp_tbl and quality_tbl into the view (so using that view all 3 tables would appear as one big table) and that would auto delete it from all the sub tables. See this post for an example of creating a view then you can follow it from there http://stackoverflow.com/questions/6038447/multiple-tables-in-one-view
      singulars
      1. POPHP - mysql multiple table subqueries
      1. USDave
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload