Populate multiple sql queries based on Drop down selection

Question

I'm new to PHP and SQL and would like to know what is the best solution to my issue.

I currently have a PHP page (profile.php) that contains the following three select queries:

$academic = "SELECT * FROM t12019 WHERE `Student ID` = {$id}";
$resultb = mysqli_query($conn,$academic); 

$stoplight = "SELECT * FROM t1stoplight19 WHERE `Student ID` = {$id}";
$resultc = mysqli_query($conn,$stoplight); 

$attendance = "SELECT * FROM t1attendance2019 WHERE `Student ID` = {$id}";
$resultd = mysqli_query($conn,$attendance);

$id comes from a $_POST selection from the previous page.

the tables for each query is coded in, however, I would like them to be a variable that can be updated from a dropdown selection on the page.

What I imagine, is a selection box on the profiles.php where a user can select one of 'Term 1','Term 2', 'Term 3', 'Term 4'. The tables for each query will then be updated accordingly for the selection.

Any help appreciated.

Please read carefully about SQL injections before doing such things! As a first start I would suggest: https://en.wikipedia.org/wiki/SQL_injection — PowerStat, Apr 15 '19 at 06:09
First, to prevent sql injection refer this article. https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Sangita Kendre, Apr 15 '19 at 07:17
you need to passed selected `value` to your query ,didn't understand your question? — Swati, Apr 15 '19 at 07:29
**Warning:** You are wide open to [SQL Injections](http://php.net/manual/en/security.database.sql-injection.php) and should really use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](http://php.net/manual/en/pdo.prepared-statements.php) or by [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Never trust any kind of input, especially that which comes from the client side. Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). — Dharman, Apr 15 '19 at 18:49

score 0 · Accepted Answer · answered Apr 15 '19 at 07:38

On dropdown selection, by javascript submit form and update the respective code. Please refer below sample code.

HTML code:

<form action="update.php" id="updateFrm" method="post">
    <input type="hidden" name="id" value="<?php echo $id; ?>">
    <select name="term" id="term" onchange="updateTable()">
        <option value="term_1">Term 1</option>
        <option value="term_2">Term 2</option>
        <option value="term_3">Term 3</option>
        <option value="term_4">Term 4</option>
    </select>
</form>

Jquery Code:

<script type="text/javascript">
  function updateTable() {
      document.getElementById("updateFrm").submit();

  }

</script>

PHP Code:

<?php

$id = $_POST['id'];
$term = $_POST['term'];

if($term == 'term_1'){
  $sql = "UPDATE t12019 ". "SET field1 = $term ".
    "WHERE `Student ID` = $id" ;
  $retval = mysqli_query( $conn, $sql );

  if(! $retval ) {
    die('Could not update data: ' . mysqli_error($conn));
  }
  echo "Updated data successfully\n";
}else if($term == 'term_2'){
  $sql = "UPDATE t1stoplight19 ". "SET field1 = $term ".
    "WHERE `Student ID` = $id" ;
  $retval = mysqli_query( $conn, $sql );

  if(! $retval ) {
    die('Could not update data: ' . mysqli_error($conn));
  }
  echo "Updated data successfully\n";
}.....and so on.


?>

**Warning:** You are wide open to [SQL Injections](http://php.net/manual/en/security.database.sql-injection.php) and should really use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](http://php.net/manual/en/pdo.prepared-statements.php) or by [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Never trust any kind of input, especially that which comes from the client side. Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). — Dharman, Apr 15 '19 at 18:49
Thank you so much @Sangita. This is exactly what I was looking for. — Juniortear, Apr 16 '19 at 01:33

Populate multiple sql queries based on Drop down selection

1 Answers1