MySQL Query Within JavaScript

Question

I am working on a form whereby JavaScript is used to add another line item. The purpose is to add line items to an invoice. I have successfully got this to work ok when using text boxes in my form, but am stuck on how to get this to work with a dropdown box that makes a call to mysql to get the dropdown box data.

Here is what I have.

 <?php
     include $_SERVER['DOCUMENT_ROOT']."/connect.php";
     include $_SERVER['DOCUMENT_ROOT']."/includes/header.php";
 ?>

 <script type="text/javascript">
     var counter = 1;
     function addInput(divName){
          var newdiv = document.createElement('div');
          newdiv.innerHTML = "Entry " + (counter + 1) + " <br><select name='myInputs[]'><?php $result = mysql_query("SELECT * FROM salesitem"); while($row = mysql_fetch_array($result)) { echo "<option value=\"".$row['name']."\">".$row['name']."</option>";} ?></select>";
          document.getElementById(divName).appendChild(newdiv);
     }
 </script>

 <form method="POST">
     <div id="dynamicInput">
          Entry 1<br><select name="myInputs[]"><?php $result = mysql_query("SELECT * FROM salesitem"); while($row = mysql_fetch_array($result)) { echo "<option value=\"".$row['name']."\">".$row['name']."</option>";} ?></select>
     </div>
     <input type="button" value="Add another text input" onClick="addInput('dynamicInput');">
 </form>

So here is some info on what is going on. Right now, the JavaScript code above shows the MySQL query in it. This kills the add line item functionality. If I simply remove the query but leave the other php in, the add line item begins to work again, but of course there is no data in the drop down.

In the form itself, it gets the first line item from the database without using javascript and this is working ok.

I feel like I am getting close, but don't know where to go from here.

Thanks in advance.

EDIT: Thanks to Nick, I got this working. Here is the code.

 <?php
 include $_SERVER['DOCUMENT_ROOT']."/connect.php";
 include $_SERVER['DOCUMENT_ROOT']."/includes/header.php";
 ?>

 <script type="text/javascript">
 var counter = 1;
 function addInput(div){
    xmlhttp=new XMLHttpRequest();
    xmlhttp.onreadystatechange = function() {
     if (xmlhttp.readyState == 4 && xmlhttp.status == 200){
      var newdiv = document.createElement('div');
      newdiv.innerHTML = "Entry " + (++counter) + " <br><select name='myInputs[]'>" + xmlhttp.responseText + "</select>";
     }
      document.getElementById(div).appendChild(newdiv);
   }

 xmlhttp.open("GET", "update_text.php", false);
 xmlhttp.send();
 }
 </script>

 <form method="POST">
 <div id="dynamicInput">
      Entry 1<br><select name="myInputs[]"><?php $result = mysql_query("SELECT * FROM salesitem"); while($row = mysql_fetch_array($result)) { echo "<option value=\"".$row['name']."\">".$row['name']."</option>";} ?></select>
 </div>
 <input type="button" value="Add" onClick="addInput('dynamicInput');">
 </form>

Then the update_text.php

 <?php
 include $_SERVER['DOCUMENT_ROOT']."/connect.php";
 $result = mysql_query("SELECT * FROM salesitem");
 while($row = mysql_fetch_array($result)) {
 echo "<option value=\"".$row['name']."\">".$row['name']."</option>";
 }
 ?>

And here is my php post into the database which is not working for the javascript add line item, only for the original entry that is created from the form. (I have other fields besides the dropdown).

 <?php

 include $_SERVER['DOCUMENT_ROOT']."/connect.php";

 $company = mysql_real_escape_string($_POST['company']);

 foreach($_POST['item'] as $i => $item)
 {
   $item = mysql_real_escape_string($item);
 $quantity = mysql_real_escape_string($_POST['quantity'][$i]);

 mysql_query("INSERT INTO invoice (company, item, quantity) VALUES ('$company', '".$item."', '".$quantity."') ") or die(mysql_error());
 }
 echo "<br><font color=\"green\"><b>Invoice added</b></font>";

 ?>

Thanks, please let me know if I can clean this up better.

Answer 1

Sadly the way you'r trying to do it wont work due to the nature of PHP.

When a client requests your page from the server ALL the php is executed.

So the php block inside your javascript function is actually just the static result of your php.

You'll need to use XMLHttpRequests/Ajax to request the new data from the server.

Here's a quick (and probably fairly bad) example!

Modification to your javascript function:

var counter = 1;
function addInput(div) {
    xmlhttp=new XMLHttpRequest();
    xmlhttp.onreadystatechange = function() {
        if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
                    var d = document.getElementById(div);
                    d.innerHTML=d.innerHTML  +"<div>Entry " + (++counter) + " <br><select name='myInputs[]'>" + xmlhttp.responseText + "</select></div>";
        }
    }
    xmlhttp.open("GET", "updated_list.php", false);
    xmlhttp.send();
}

A new php file on your server: (updated_list.php)

<?php 
// you'll need to include stuff to connect to your database here
$result = mysql_query("SELECT * FROM salesitem"); 
while($row = mysql_fetch_array($result)) { 
    echo "<option value=\"".$row['name']."\">".$row['name']."</option>";
} 
?>

update

I did say it was a bad example :) my original code overwrote the contents of your div instead of adding to it, which ive updated. this was only an example, you should read up on AJAX and how to request data from the server.