[RESOLVED] Fill textbox from query page

[chris.cavage]

I am trying to fill a textbox with the price of the item when the menu that has the items for sale changes.

As a test, I have it set up to query my db through my getprice.php page and echo the info in a table. It works great! I cannot seem to get the price though to show in a textbox.

Here's my page that has the price textbox:

....
mysql_select_db($database_connectMichels, $connectMichels);
$query_molds = "SELECT * FROM types_earmold";
$molds = mysql_query($query_molds, $connectMichels) or die(mysql_error());
$row_molds = mysql_fetch_assoc($molds);
$totalRows_molds = mysql_num_rows($molds);

?>

<script type="text/javascript">
function showUser(str)
{
if (str=="")
  {
  document.getElementById("txtHint").innerHTML="";
  return;
  } 
if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
    }
  }
xmlhttp.open("GET","admin_getprice.php?item_field=Earmold&table=types_earmold&q="+str,true);
xmlhttp.send();

var textbox1 = document.getElementById("price");
    
    textbox1.value = <?php echo $price; ?>;  <--- this is how I was thinking I'd need to change the price textbox with the selected result. Doesn't work!  In fact, it stops the table that has the fetched data to not show also.


}
</script>

   <form id="form1" method="post" action="">
      <select name="types_earmolds" id="types_earmolds" onchange="showUser(this.value)">
        <?php
do {  
?>
        <option value="<?php echo $row_molds['ID']?>"><?php echo $row_molds['Earmold']?></option>
        <?php
} while ($row_molds = mysql_fetch_assoc($molds));
  $rows = mysql_num_rows($molds);
  if($rows > 0) {
      mysql_data_seek($molds, 0);
	  $row_molds = mysql_fetch_assoc($molds);
  }
?>
      </select> <--- this is a dynamically filled menu that has the item info.
    </form>
    <p>
<div id="txtHint"><b>Price info will be listed here in the table.</b></div>    
</p>
<label>
  <input name="price" type="text" id="name" value="" /> <--- I want the price in here also!
</label>
</script>

Here's my admin_getprice.php page:

<?php 
$q=$_GET["q"];
$table =$_GET["table"];
$item_field =$_GET["item_field"];


mysql_select_db($database_connectMichels, $connectMichels);
$sql="SELECT $item_field as Item, Item_Price, Taxable FROM $table WHERE ID = '".$q."'";
$result = mysql_query($sql, $connectMichels) or die(mysql_error());

echo "<table border='1' width=\"60%\">
<tr>
<th>Item</th>
<th>Item_Price</th>
<th>Taxable</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td width=\"20%\" align=\"center\">" . $row['Item'] . "</td>";
  echo "<td width=\"20%\" align=\"center\">" . "$" . $row['Item_Price'] . "</td>";
  echo "<td width=\"20%\" align=\"center\">" . $row['Taxable'] . "</td>";
  echo "</tr>";
  $price = $row['Item_Price'];
  }
echo "</table>";

$price = $row['Item_Price'];

?>

[kows]

First of all, you need to understand that these scripts are completely separate. Once a PHP script finishes executing, it has absolutely finished executing. None of those variables exist anymore. So, the $price you're creating in admin_getprice.php doesn't actually do .. anything. You're essentially assigning a value to a variable in a while loop and then never doing anything with that variable.

Furthermore, it seems like all you're actually trying to do is get the price. But admin_getprice.php outputs an entire table of data, not the price. You want to have a price (a single, plain-text value) in your text box field, but the data being returned by your XMLHTTPRequest looks more like:

<table border='1' width="60&#37;">
<tr>
<th>Item</th>
... And so on

What you basically need to create is a script that returns only what you want, or at least provides you back with data in a JavaScript-friendly format, like JSON or XML (I prefer JSON). But, for your benefit and because you seem to be experimenting, forget about formatting. Just make your AJAX script output the value you want.

<?php 
$q=$_GET["q"];
$table =$_GET["table"];
$item_field =$_GET["item_field"];


mysql_select_db($database_connectMichels, $connectMichels);
$sql="SELECT $item_field as Item, Item_Price, Taxable FROM $table WHERE ID = '".$q."'";
$result = mysql_query($sql, $connectMichels) or die(mysql_error());

// I've added these lines:
if(mysql_num_rows($result) == 1){
  $row = mysql_fetch_assoc($result);
  echo $row['Item_Price'];
}else{
  echo "-1";
}
?>

Now, this script outputs one of two things. If the item was found in the database, it outputs its Item_Price value. If it wasn't found, it outputs -1 (an arbitrary value to let you know something went wrong). In your JavaScript, you can now figure out whether or not a price was returned by checking if the value is -1 or not.

xmlhttp.onreadystatechange=function()
{
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
  {
      if(xmlhttp.responseText == "-1"){
        alert("A price couldn't be returned");
      }else{
        document.getElementById("price").value = xmlhttp.responseText;
      }
  }
}

Hopefully, this is making some sense. I'm also having a bit of trouble figuring out what you're actually trying to do other than filling in the price textbox. So, here's some more stuff.

As far as the text you want to put into txtHint, I'm assuming that's where you want this table thing to go. So, rather than using two scripts and two requests, you can use the same script to print out JSON that holds a value for the price as well as an HTML value. JSON stands for JavaScript Object Notation, and simply represents a JavaScript variable. Your JSON will look similar to this:

{
  "price": "6.99",
  "hintHtml": "<table border=\"1\" width=\"60%\"> .... </table>"
}

PHP can output JSON easily by using json_encode() on an array/object, so let's get to it. Taking the admin_getprice.php file I made above, we can add the HTML back in, in a variable, and then put the price and that HTML into an object to turn into JSON:

<?php 
$q=$_GET["q"];
$table =$_GET["table"];
$item_field =$_GET["item_field"];


mysql_select_db($database_connectMichels, $connectMichels);
$sql="SELECT $item_field as Item, Item_Price, Taxable FROM $table WHERE ID = '".$q."'";
$result = mysql_query($sql, $connectMichels) or die(mysql_error());

if(mysql_num_rows($result) == 1){
  $row = mysql_fetch_assoc($result);
  $price = $row['Item_Price'];
  $html = <<<HTML
<table border="1" width="60%">
  <!-- Put the rest of your HTML here! -->
</table>
HTML;

  $error = 0;
}else{
  $price = -1;
  $html = "";
  $error = "The item requested could not be found"
}
// Now, create a PHP object out of an array
$jsonObject = (object) array(
  "price" => $price,
  "hintHtml" => $html,
  "error" => $error
);

// Convert it to JSON, and print
echo json_encode($jsonObject);
?>

Now, admin_getprice.php prints out a JSON object containing three sets of values: price, hintHtml, and error. I added the error in as an easier way to figure out if something went wrong, but you could continue using the price variable instead. So, anyway, now your xmlhttp.responseText will contain a string of JSON. We need to convert that JSON string to actual JavaScript, and you can technically do this by just calling eval(), but you should really probably never do that. This is a JavaScript JSON parser which will parse properly formed JSON (like ours), and it's just a JavaScript library that you can include.

Now, our onReadyStateChange event will look more like this:

xmlhttp.onreadystatechange=function()
{
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
  {
      var response = JSON.parse(xmlhttp.responseText);
      
      // We now have a JavaScript object to manipulate instead of plain-text. Wooo.
      // We can access our data by using dot-notation:
      // response.price returns price
      // response.hintHtml returns the hint HTML
      // response.error returns the error -- let's check that first

      if(response.error == 0){
        document.getElementById("price").value = response.price;
        document.getElementById("txtHint").innerHTML = response.hintHtml;
      }else{
        // There was an error: display it.
        alert("ERROR: " + response.error);
      }
  }
}

Okay. All done.

Mind you, I just wrote all of this code off the top of my head, and so it's just kind of a guide. Also, I might have went a bit too fast for you. Feel free to ask questions, of course, or just point out entire sections of this post that may have made no sense at all. ;)

Disclaimer: It's great to do all of this sort of stuff in plain JavaScript (so that you understand), but for future cases you will want to look into using a JavaScript library to do the brunt of the work for you. jQuery, for example, includes a JSON parser and has great AJAX methods. And DOM manipulation is ridiculously easy.

[chris.cavage]

Wow, kows, this is a fantastic tutorial!! I appreciate your time.

I have slowly read your post, and it makes sense (the logic). I need to experiment with your code when I am at home with my php files.

Let me just get a question out of the way first...

Disclaimer: It's great to do all of this sort of stuff in plain JavaScript (so that you understand), but for future cases you will want to look into using a JavaScript library to do the brunt of the work for you. jQuery, for example, includes a JSON parser and has great AJAX methods.

I have been using some jQuery for random things in my pages (like a datepicker, for instance). In my research before I posted this thread, I came across the .post method (or function, whatever it's called...).

So, I am guessing that the .post approach to jQuery would eliminate the need for:

xmlhttp.onreadystatechange=function()
{
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
  {
      var response = JSON.parse(xmlhttp.responseText);
      
      // We now have a JavaScript object to manipulate instead of plain-text. Wooo.
      // We can access our data by using dot-notation:
      // response.price returns price
      // response.hintHtml returns the hint HTML
      // response.error returns the error -- let's check that first

      if(response.error == 0){
        document.getElementById("price").value = response.price;
        document.getElementById("txtHint").innerHTML = response.hintHtml;
      }else{
        // There was an error: display it.
        alert("ERROR: " + response.error);
      }
  }
}

... as jQuery would perform the above code using minimal jQuery commands? Is that true? I'm guessing, yes. Which makes sense why jQuery exists. I wondered what it was really and why it was popular... I can see from this small example just why!

I'll post back once I play around with your lesson.

[kows]

Actually, jQuery wouldn't remove most of what I added (which you quoted), but would remove the need for you to create an XMLHTTPRequest manually and then interact with it directly. You could continue using a GET request (jQuery.get), or you could use POST instead (jQuery.post). I would probably use POST, because you're sending variables and all. The entire process (and I mean the whole thing) would look like:

$(function(){
  // Change event for <select>
  $('#types_earmolds').change(function(e){
    // Get selected value
    var selectedValue = $(this).val();

    // Send POST request
    $.post(
      // URL to POST to
      'admin_getprice.php', 
      // JSON object representing data to POST
      {
        "item_field": "Earmold",
        "table": "types_earmold",
        "q": selectedValue
      },
      // This function will be called upon successful POST request
      function(response){
        if(response.error == 0){
          // Set price textbox
          $('#price').val(response.price);
          // Set hint HTML
          $('#txtHint').html(response.hintHtml);
        }else{
          // Display error
          alert("ERROR: " + response.error);
        }
      },
      // Type of data we're expecting back (this will pre-parse our response to JSON for us)
      "json");
  });
});

As you can see, it's a lot shorter. But, there's still a lot going on. We create an onchange event using JavaScript here (you have it in your mark-up on your <select>, which should be removed), and in that event we do an AJAX JSON POST request. Meaning, we send a POST request using AJAX and specifically tell jQuery that we're expecting JSON back, so it parses our responseText for us into JSON. There is also a manual JSON parser in jQuery, but it's unnecessary in this case. In the callback, we have basically the same 6 lines of code as in the other example. We're just setting values of our elements to the data from our response.

Make sense?

But, since you're using POST, make sure you use $_POST instead of $_GET in your PHP script. Also, some validation would be nice. Even just checking the request method would be a great start to stop unnecessary processing from rogue requests by curious users:

if($_SERVER['REQUEST_METHOD'] == "POST"){
  // all your code
}else{
  echo "Bad request.";
} 

[chris.cavage]

Makes sense!

Here's what I was reading/referencing when I was attemping my jQuery the other day:

http://api.jquery.com/jQuery.post/

$.ajax({
  type: 'POST',
  url: url,
  data: data,
  success: success,
  dataType: dataType
});

So I understand the placement of your .post code.
For the "success" part of it, you have:

function(response) { if *success code - change textbox value* else *fail alert* }

So I'm wondering: why use a function here?

Upon success of .post, the function runs automatically? It must, as it isn't called elsewhere that I see. I thought functions have to be called/initiated (not sure of the programming words here).

I wouldn't have thought to place a function there. Either way, what made you say, "Insert function now..." at the success portion of the code, instead of just entered the "if then else" statement for the success part only?

Hope that question came across clearly.

I didn't attempt the code yet, now that I'm home. I still want to wait til I make more sense of it...

[chris.cavage]

Oh.... back the cart up.

It is *required* as part of the jQuery .post method.

This is from the link I just shared:

success(data, textStatus, jqXHR)A callback function that is executed if the request succeeds.

That could have been why I was unsuccessful trying the post method via jQuery the other night...

[chris.cavage]

So I am working on this now.
I am scrapping the table temporarily to see if I can get Item_Price to populate the price field.

Here's my new experiment page:

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>
<script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1/jquery-ui.min.js"></script>

<script type="text/javascript">
						   
$(function(){
  // Change event for <select>
  $('#types_earmolds').change(function(e){
    // Get selected value
    var selectedValue = $(this).val();

    // Send POST request
    $.post(
      // URL to POST to
      'admin_getprice.php', 
      // JSON object representing data to POST
      {
        "item_field": "Earmold",
        "table": "types_earmold",
        "q": selectedValue
      },
      // This function will be called upon successful POST request
      function(response){
        if(response.error == 0){
          // Set price textbox
          $('#price').val(response.price);
          // Set hint HTML
          //$('#txtHint').html(response.hintHtml);
        }else{
          // Display error
          alert("ERROR: " + response.error);
        }
      },
      // Type of data we're expecting back (this will pre-parse our response to JSON for us)
      "json");
  });
});
</script>


      <select name="types_earmolds" id="types_earmolds">
        <?php
do {  
?>
        <option value="<?php echo $row_molds['ID']?>"><?php echo $row_molds['Earmold']?></option>
        <?php
} while ($row_molds = mysql_fetch_assoc($molds));
  $rows = mysql_num_rows($molds);
  if($rows > 0) {
      mysql_data_seek($molds, 0);
	  $row_molds = mysql_fetch_assoc($molds);
  }
?>
      </select>

Here's my admin_getprice.php page:

<?php require_once('pathtoconnection.php'); ?>
<?php 
if($_SERVER['REQUEST_METHOD'] == "POST"){
	
$q= $_POST["q"];
$table = $_POST["table"];
$item_field = $_POST["item_field"];

mysql_select_db($database_connectMichels, $connectMichels);
$sql="SELECT $item_field as Item, Item_Price, Taxable FROM $table WHERE ID = '".$q."'";
$result = mysql_query($sql, $connectMichels) or die(mysql_error());

if(mysql_num_rows($result) == 1){
  $row = mysql_fetch_assoc($result);
  $price = $row['Item_Price'];
  //$html = "";
  $error = 0;
}else{
  $price = -1;
  //$html = "";
  $error = "The item requested could not be found"
}
// Now, create a PHP object out of an array
$jsonObject = (object) array(
  "price" => $price,
  "hintHtml" => $html,
  "error" => $error
);

// Convert it to JSON, and print
echo json_encode($jsonObject);

}else{
  echo "Bad request.";
} 
?>

When the menu is changed, unfortunately nothing happens here. Nothing appears in the price textbox as of now. No error message either.

Hrm...

[kows]

Your admin_getprice script has a syntax error (which was my fault, but I did warn you!). Missing a semi-colon on this line:

$error = "The item requested could not be found" 

This looks like the only error I can see, and the test I made using a slightly modified version of your code seems to work fine (I just don't have an actual database connection).

Let me know if that works out.

[chris.cavage]

AMAZING! Yes, so simple!

I am super pumped.

I don't know how to thank you for the great lesson and error-find!

I am going to build off this now - try to add more textboxes, expand the query, etc.