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.

you might want to wrap your entry 1 section in a <div> so that it matches the markup of the inserted lines. or possily put an id on the button and use insertBefore (insertAfter on your dynamicInput would cause new ones to be added to the top and not the bottom)
Yes, my javascript added lines are not inserting into the database. When you say wrap my entry 1 section in a div, is it not already? Can you clarify?
It is yes, however you are inserting your newdiv inside your first instead of after it. What you have currently <form><div1>entry1<br><select><div2>....</div2><div1></form> what I think you want <form><div1>entry1<br><select></div1><div2>....</div2></form>. If your question has been answered, you should accept one of them :-)
It doesn't seem to be making a difference, only the first line is being written to the database. I'm adding my php post to the original to see if maybe the problem lies there.
I have determined that the javascript is not posting to php. But I am unsure exactly how to fix the html form. You have made it clear I should add a div tag to the javascript, however I am unsure what to name it in order to get it to work with the html form.

NickSlash · Answer 1 · 2013-03-25 00:27:02Z

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.

Nick, it is not adding a line item with these changes, nothing happens when the add button is depressed. updated_list.php is working though when manually ran. I removed the if } to see if that would have any bearing but no. Thank you.
I had a typo. When I click the Add button, it jumps by 2. I changed it back to counter + 1. However, it stops at #2 and will go no further. Still no mysql data either. I'm going to keep working it and try and merge my original code with your code to see if I can get it to work. Thank you for any assistance.
I got it working. I will post an addendum to my original post. Thank you!

Adidi · Answer 2 · 2013-03-24 23:20:19Z

You cannot use server code inside a javascript function after the page is load. The server side code is like it's name - code that runs on the server. so after the page finish loading it "get back" to the client and only client code (javascript) can run now. so your line

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>";

is a bit mistake.

what you can do is to return the data from the database to the javascript (using json will be the best option) and then build the select box dynamically using the raw data that came from the server that is now a javascript data.

so let's say your data is

var data = [{name:'n1'},{name:'n2'},{name:'n3'}];

You can run this data using javascript and build your combo:

var newSelect = document.createElement('select');
for(var i=0;i<data.length;i++){
  var name = data[i].name;
  newSelect.options[newSelect.options.length] = new Option(name,name);
}

ChrisW · Answer 3 · 2013-03-24 23:32:33Z

This is a classic example for AJAX (Asynchronous Javascript And XML) - the basic concept is that when an action is carried out (e.g. a button clicked to show content), some JavaScript calls a PHP (asynchronously), the PHP runs, returns an XML file (although this can actually be any file format you want, some people now prefer to use JSON because it's easier to parse), and the contents of the this XML / JSON are displayed, also using JavaScript.

Although the example above is displaying content, there's no reason why any action where you need to run on the server (e.g. inserting data to a database) can't also be done asynchronously.

SO is not a place for code to be written for people - so now I've given you a hint as to where to start, and as an exercise for the reader ( / question asker!), have a look at some AJAX tutorials, make sure you understand the concept, write some code, and come back if still can't get it working!

Good luck :)

asked	4 months ago
viewed	1885 times
active	4 months ago

MySQL Query Within JavaScript

3 Answers

Your Answer

Not the answer you're looking for? Browse other questions tagged php javascript mysql forms invoice or ask your own question.

MySQL Query Within JavaScript

3 Answers

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged php javascript mysql forms invoice or ask your own question.

Related