Want to refresh page with updated SQL statement after choosing from dropdown menu-CodePudding

I've tried to google A LOT, and searched in other stackoverflow similar post, but i can't really find something that's helping me -.-

I'm trying to make my site refresh and update my tables values, and only show the ones where it's equal to the dropdown menu. When i enter the page i want it to show everything like it does now, and then update the site and my SQL statement, when choosing CustomerCountryCode I only want to show the 'DK', when choosing from the dropdown menu

Menu shown here

History.php (the WHERE should be something like $_POST['SelectedValue'] instead of 'DK')

if (isset($_POST['SelectedValue'])) {
$sql = "SELECT * FROM [CampaignModule].[dbo].[RetailCampaign] WHERE CampaignNo <> '' AND 
CustomerCountryCode like 'DK' ORDER BY ID DESC"
}else {
$sql = "SELECT * FROM [CampaignModule].[dbo].[RetailCampaign] WHERE 
CampaignNo <> '' ORDER BY 
ID DESC";
}

$sqlFilter = "SELECT DISTINCT CustomerCountryCode FROM [CampaignModule]. 
[dbo].[RetailCampaign]";
$params = array();
$options =  array("Scrollable" => SQLSRV_CURSOR_KEYSET);
$stmt = sqlsrv_query($conn, $sql, $params, $options);
$stmt2 = sqlsrv_query($conn, $sqlColumns, $params, $options);
$stmtFilter = sqlsrv_query($conn, $sqlFilter, $params, $options);

echo '<select name="Filter" id="FilterSelect" >
<option>Choose Country Filter</option>';
while ($row = sqlsrv_fetch_array($stmtFilter, SQLSRV_FETCH_NUMERIC)) {
echo "<option value='$row[0]'>$row[0]</option>";
}
echo '</select>';

HistoryControl.js - I tried to create a post function to insert the data, and reload the page when the menu is updated - I tried af few other things.

$(document).ready(function () {
$("#FilterSelect").change(function () {
var value = $("#FilterSelect").val();
$.ajax({
  url: "../Model/History.php",
  type: "POST",
  data: { 'SelectedValue=': value },
  success: function () {
      location.reload();
  }
});
});
});

Any form for optimization and other 'best practices' is much appreciated as well. if more information or code needed tell me please

UPDATE - SOLUTION

With help and ideas from @Lam Tran Duc & @Manashree Shah

History.php

 if (isset($_SESSION['SelectedValue'])) {
 $_POST['SelectedValue'] = $_SESSION['SelectedValue'];
 }

if (isset($_POST['SelectedValue'])) {
$_SESSION['SelectedValue'] = $_POST['SelectedValue'];

$sql = "SELECT * FROM [CampaignModule].[dbo].[RetailCampaign] WHERE 
CampaignNo <> '' 
AND CustomerCountryCode='" . $_POST["SelectedValue"] . "'";
} else {
unset($_SESSION['SelectedValue']);
$sql = "SELECT * FROM [CampaignModule].[dbo].[RetailCampaign] WHERE 
CampaignNo <> '' 
ORDER BY ID DESC";
}

HistoryControl.js

$(document).ready(function () {
$("#FilterSelect").change(function () {
var value = $("#FilterSelect").val();
$.ajax({
  url: "../Model/History.php",
  type: "POST",
  data: { SelectedValue: value }, // remove the '=' character
  success: function (data) {
    //alert(data)
    location.reload();
  }
});
});
});

When i was doing this it's working, but i needed it to clear the $_SESSION variable which i had a lot of trouble with, and ended up with the solution to clear the session in me historyPage.php

<?php
include "../Model/History.php";
session_unset();
?>

CodePudding user response：

As far as I understand you are experiencing:

Drop-down list (Country filter)
A data display table.

When a value is selected in the drop-down list, the data in the table is filtered by the value of the selected drop-down list.

If I have understood correctly then I have a few ideas for you as follows:

History.php

I see it has 2 separate parts that have nothing to do with it:

In the first if / else , you rely on the $_POST['SelectedValue'] variable to define a query that gets the data displayed on the table.
The rest of the code is used to get the data for the dropdown. I think this code should be in another file.

HistoryControl.js

I think this code is not very correct: data: {'SelectedValue =': value}. Since when you assign data like that when ajax calls History.php file, variable name will be $_POST['SelectedValue='] so in if (isset($_POST['SelectedValue'])) will always return false . This leads to an unexpected result.

Edit it like this:

$.ajax({
    url: "../Model/History.php",
    type: "POST",
    data: { 'SelectedValue': value }, // remove the '=' character
    success: function () {
        location.reload();
    }
});

CodePudding user response：

How about you use PHP session variable like below:

if (isset($_POST['SelectedValue'])) {
  $_SESSION['SelectedValue'] = $_POST['SelectedValue'];
  $sql = "SELECT * FROM [CampaignModule].[dbo].[RetailCampaign] WHERE CampaignNo <> '' AND 
CustomerCountryCode like 'DK' ORDER BY ID DESC"
}else {
  $_SESSION['SelectedValue'] = "";
  $sql = "SELECT * FROM [CampaignModule].[dbo].[RetailCampaign] WHERE 
CampaignNo <> '' ORDER BY 
ID DESC";
}

and then you can use the session variable to preselect the option:

echo '<select name="Filter" id="FilterSelect" >
<option>Choose Country Filter</option>';
while ($row = sqlsrv_fetch_array($stmtFilter, SQLSRV_FETCH_NUMERIC)) {
  if($row[0] == $_SESSION['SelectedValue']){
   echo "<option value='$row[0]' selected>$row[0]</option>";
  }else{
   echo "<option value='$row[0]'>$row[0]</option>";
  }
}
echo '</select>';