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

Question

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...

If that is your whole code you got a few problems there. 1.) You are accessing $_POST without using a form that has the method=POST tag which means $_POST will be empty forever. 2.) You really should use prepared sql statements — Thomas, Oct 13 '15 at 08:01
@LoganWayne and Thomas, No, this is not my full working code. — Danny, Oct 13 '15 at 08:06
You shouldn't use mysql_* functions. http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — Michas, Oct 13 '15 at 08:07

score 1 · Accepted Answer · edited May 23 '17 at 12:15

Declaring of $i should be inside your if() condition, or else it will give an undefined variable error message.
What is the purpose of @$facility_section = $_POST['facility_section'];? Where did you use it? I think it should also be inside your if() condition.
You are prone to SQL Injection, so you should be using mysql_real_escape_string() when binding a variable, especially when it is a string, into your query.
mysql_* API is already deprecated, and you should be using mysqli_* API instead. Read here about prepared statement.

Your code should at least look like this (inside if() condition):

if(isset($_POST['submit'])){

  $i = mysql_real_escape_string($_POST["issued_to"]);

  $s="INSERT INTO tbl_B (issued_to) VALUES ('$i')";
  mysql_query($s);

  /* GET TOTAL OF tbl_B BASED FROM THE SELECTED facility_section */
  $res = mysql_query("SELECT * FROM tbl_B WHERE issued_to = '$i'");
  $totalrows = mysql_num_rows($res);

  /* UPDATE THE assigned_no COLUMN WITH THE UPDATED NUMBER OF ROWS */
  mysql_query("UPDATE tbl_A assigned_no = '$totalrows' WHERE facility_section='$i'");

} /* END OF ISSET */

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

1 Answers1