动态JavaScript变量通过Click事件处理程序使用PHP / SQL

Question

I'm populating a select tag with options gathered by using PHP to import all rows of a certain field in an SQL table. A row in my table follows this format:

id | wcat | wtype | was | wcc | wbdmin | wbdmax

I've completed this part successfully but the next task at hand is what I'm having trouble with as I am a newbie when it comes to PHP and Javascript.

Within my click event handler I define a variable 'wtype' which I set to the value of the select option:

wtype = $("select[name='wtype'] :selected").val() || 0;

What I wish to do after is to import the corresponding fields for the row with a matching 'wtype' and set them as their own variables, ie. 'was', 'wcc', 'wbdmin', & 'wbdmax'. Afterwards I use these values to do some math, etc.

After doing some research/reading, I am currently attempting the following in my js file after I define the 'wtype' variable (even though I don't really have a clue what I'm doing):

var data = {w : wtype};
var url = "get.php";
$.post(url, data).done(function(data) {
    var winfo = $.parseJSON(data);
    was = winfo[3];
    wcc = winfo[4];
    wbdmin = winfo[5];
    wbdmax = winfo[6];
    $("#result").html(winfo);
});

And in get.php:

<?php
  $host = "****";
  $dbname = "****";
  $user = "****";
  $pass = "****";
  try {
    $conn = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
  }
  catch(PDOException $e) {
    echo $e->getMessage();
  }
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $name = $_POST["w"];
  $sql = "SELECT wtype, was, wcc, wbdmin, wbdmax FROM items WHERE wtype='".$name."'";
  $stmt = $conn->prepare($sql);
  $stmt->setFetchMode(PDO::FETCH_ASSOC);
  if ($stmt) {
    try {
      $stmt->execute();
      while ($row = $stmt->fetch()) {
        $data[] = $row;
      }
      $data = json_encode($data);
    }
    catch (PDOException $e) {
      var_dump($e);
    }
  }
?>

This obviously does not work as my math results in NaN/undefined whereas before with test variable values it worked.

Answer 1

You have some index out of range errors in your javascript. In your select clause you select five columns which causes the first value (which is the content of the 'wtype' column) of the array you receive in your javascript to be at index zero. So what you want to do is this:

var data = {w : wtype};
var url = "get.php";
$.post(url, data).done(function(data) {
    var winfo = $.parseJSON(data);
    was = winfo[1]; 
    wcc = winfo[2];
    wbdmin = winfo[3];
    wbdmax = winfo[4];
    $("#result").html(winfo);
});

Also, if you are not using the wtype column you can leave that out of your query: $sql = "SELECT was, wcc, wbdmin, wbdmax FROM items WHERE wtype='".$name."'";

Be careful to change your indexes correspondingly to your SELECT clause.

I can also see that you wrote a loop to iterate over your results. If I understand you correctly every wtype is unique and only returns one row. If so, you want to change this:

$stmt->execute();
while ($row = $stmt->fetch()) {
  $data[] = $row;
}
$data = json_encode($data);

to this:

$stmt->execute();
$row = $stmt->fetch();
$data = json_encode($row);

Please let me know if this helped you. Or if I misinterpreted your question.