1

I have a web program where the goal is plot data points for a certain Kiln that the user has selected. My problem is when a user wants to select a new Kiln, how can I update all the separate JSON pages to where the data is pulled from the new table they selected?

Here is my drop down list creater code.

<p class="navleft">
Kiln Number:<br>
<select name="kilns" id="kilns">
<?php
$sql = "SHOW TABLES FROM history";
$result = mysqli_query($con,$sql);

while($table = mysqli_fetch_array($result)) { // go through each row that was returned in $result
 echo ("<option value='". $table[0] . "'>" . $table[0] . "</option>");
}
?>
</select>
</p>

And here is one of the php pages where I select all the data from a value in a table and turn it into a JSON file.

<?php
$con = mysqli_connect("localhost","KilnAdmin","KilnAdmin","history");

// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

mysqli_select_db($con,"history") or die ("no database");   

//Fetch Data
$query = "SELECT * FROM k1_history LIMIT 1000";
$result = mysqli_query($con,$query);

if ($result) {
 $data = array();
 while($row = mysqli_fetch_assoc($result)) {
  //$data[] = $row;
        $data[] = array(
    "date" => $row[ 'Timestamp' ],
    "value" => $row[ 'DryBulbFront' ]
  );
    }
 echo json_encode($data);
}
else {
 echo "Error";
}
?>

Where is says k1_history, how can I get that to be the selection from the user in the dropbox menu from the other page?

Sarah
  • 669
  • 2
  • 8
  • 21
  • not really getting the question, but cant you just save the selected value in a db\file? –  May 17 '16 at 21:56
  • Your question is rather strange, because the code above shows a level of knowledge you wouldn't get without knowing how to pass variables between scripts. To understand that you have to understand forms, actions, server variables or ajax. It's tricky to create an answer to make you understand all these concepts. Specially given the fact above. Where did you get this code ? – Nelson Teixeira May 17 '16 at 21:59

2 Answers2

1

In this kind of scenario you have to strongly pay attention to avoid SQL injection. Use a whitelist approach as mentioned by Konstantinos Vytiniotis and check this out How can I prevent SQL injection in PHP?

Community
  • 1
  • 1
utnaf
  • 377
  • 1
  • 10
0

If I understand correctly what you want, then what you need is Ajax.

You have to populate the select like you do and on each select, make an Ajax call to a .php where you will handle what the user has chosen. In your case this .php file is going to take the table name the user chose, run a query and return some results back to the html. For demonstration purposes, I'll explain with an example.

Let's say in your .html you have a select like this:

Select Value: 
    <select name="kilns" id="kilns">
        <option value="1">Option 1</option>
        <option value="2">Option 2</option>
        <option value="3">Option 3</option>
    </select>

What defined in the value property of the option is what you are gonna pass to the .php file I mentioned. To do that, you use Ajax, so inside some script tags you have:

$('#kilns').on('change', function(e) {
    var data = {'kilns': this.value};
    $.ajax({
        type: 'POST',
        url: 'submit.php',
        data: data,
        dataType: 'json'
    }).done(function(msg) {
        alert(msg);
    });
});

What this does is that every time a user selects something from the select, then this function is called, where the select's value (var data = {'kilns': this.value};) is being sent to a file named submit.php, via POST. The submit.php could look like this:

if ( $_SERVER['REQUEST_METHOD'] == 'POST' ) {
    $kilns_error = 0;

    if (isset($_POST['kilns']) && !empty($_POST['kilns'])) {
        $kilns = $_POST['kilns'];
    } else {
        $kilns = null;
        $kilns_error = 1;
    }

    if ($kilns_error != 1) {
        echo json_encode($kilns);
    }
}

What happens here is after we check we have indeed a POST REQUEST, we check whether the value is undefined or empty. After this simple check, we proceed to echo json_encode($kilns); where we return the value that we initially sent to the .php script, which in fact is the value the user selected.

In your case, what you have to do it to actually do some things in the .php script and not just return the value that you called it with. Also, make sure to pass the value you take through a whitelist to ensure that the user selects an actual table and is not trying to create problems for your database, cause it would be really easy to just change the value of what he is going to select before actually selecting it. Have a look at the prepared statements of the mysqli and PDO.

Konstantinos Vytiniotis
  • 150
  • 8