CODEWITHSUNDEEP
mysqlphpinsert
Danny
Danny

Reputation:

INSERT and UPDATE(+ increase values) between two different tables php mysql

tbl_A

[id | facility_section | assigned_no]

tbl_B

[col1 | col2| issued_to | col3 | col_etc]

The drop down list will retrieve values of facility_section column of tbl_A table. Then, the drop down list values will be inserted into issued_to column of tbl_B. Code sample:-

<?php

include ('dbconnect.php');

@$i=$_POST['issued_to'];

if(@$_POST['submit'])
{
  $s="INSERT INTO tbl_B (issued_to) VALUES ('$i')";
  mysql_query($s);
}

@$facility_section = $_POST['facility_section'];

$result = mysql_query("SELECT facility_section FROM tbl_A");

?>

<select name="issued_to">
  <option selected disabled>-- Please Select --</option>
  <?php
    while ($row = mysql_fetch_array($result)) {
      echo "<option value='" . $row['facility_section'] . "'>" . $row['facility_section'] . "</option>";
    }
  ?>
</select>

What I want is that, each time the drop down list values is inserted into issued_to column of tbl_B, assigned_no column of tbl_A will be updated and increased by 1 where it will be increased accordingly:- facility_section=issued_to.

I know the code to update:-

UPDATE tbl_A SET assigned_no = assigned_no + 1 WHERE facility_section = '$facility_section'

But, I'm stuck on how to do this...

Upvotes: 1

Views: 238

Answers (1)

jwg
jwg

Reputation: 5827

I think you are going about this the wrong way. A fundamental principle of database design is to avoid having information that is redundant (or replicated by other information somewhere else). If same information is in two or more places, then it could be inconsistent (different in each of these places).

Firstly, is issued_to in table B a foreign key to table A? It should be.

Now, don't store assigned_no in Table A. What if someone changes this value without adding a new row to Table B? What if someone inserts to Table A, without updating the corresponding assigned_no. If you assume that the assigned_no corrseponds to the number of entries in Table B with that issued_to, as soon as this becomes invalid, you have a potential for lots of entries.

Instead, retrieve the number of assignments like this

SELECT 
    facility_section,
    (SELECT COUNT(*) FROM tbl_B WHERE issued_to = 4551) AS assigned_no
FROM tbl_A
WHERE id = 4551

or for the whole table at once like this:

SELECT 
    a.id,
    a.facility_section,
    COUNT(*)
FROM tbl_A AS a
INNER JOIN tbl_B AS b
ON a.id = b.issued_to
GROUP BY a.id, a.facility_section

Upvotes: 1

Related Questions