php JavaScript 中的 MySQL 查询

MySQL Query Within JavaScript

提问by Tom

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.

我正在使用 JavaScript 来添加另一个行项目的表单。目的是将行项目添加到发票。在我的表单中使用文本框时，我已经成功地让它正常工作，但我坚持如何让它与下拉框一起工作，该下拉框调用 mysql 以获取下拉框数据。

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.

所以这里有一些关于正在发生的事情的信息。现在，上面的 JavaScript 代码显示了其中的 MySQL 查询。这会杀死添加行项目功能。如果我只是删除查询但保留其他 php，则添加行项目再次开始工作，但当然下拉列表中没有数据。

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

在表单本身中，它在不使用 javascript 的情况下从数据库中获取第一行项目，这工作正常。

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

然后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 帖子，它不适用于 javascript 添加行项目，仅适用于从表单创建的原始条目。（除了下拉菜单，我还有其他字段）。

 <?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.

谢谢，如果我能更好地清理它，请告诉我。

回答by NickSlash

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

遗憾的是，由于 PHP 的性质，您尝试这样做的方式将不起作用。

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

当客户端从服务器请求您的页面时，所有 php 都会执行。

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

所以你的 javascript 函数中的 php 块实际上只是你的 php 的静态结果。

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

您需要使用 XMLHttpRequests/Ajax 从服务器请求新数据。

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

这是一个快速（可能相当糟糕）的例子！

Modification to your javascript function:

修改你的 javascript 函数：

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 文件：(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.

我确实说过这是一个不好的例子:) 我的原始代码覆盖了你的 div 的内容，而不是添加到它，我更新了。这只是一个示例，您应该阅读 AJAX 以及如何从服务器请求数据。

回答by ChrisW

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.

这是AJAX(Asynchronous Javascript And XML) 的一个经典例子——基本概念是当一个动作被执行时（例如一个按钮点击显示内容），一些 JavaScript 调用一个 PHP（异步），PHP 运行，返回一个XML 文件（虽然这实际上可以是您想要的任何文件格式，但现在有些人更喜欢使用 JSON，因为它更容易解析），并且显示此 XML/JSON 的内容，也使用 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!

SO 不是为人们编写代码的地方 - 所以现在我已经给了你一个关于从哪里开始的提示，作为读者的练习（/提问者！），看看一些 AJAX 教程，确保你理解这个概念，写一些代码，如果仍然不能让它工作，请回来！

Good luck :)

祝你好运 ：）

回答by Adidi

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

页面加载后，您不能在 javascript 函数中使用服务器代码。服务器端代码就像它的名字 - 在服务器上运行的代码。所以在页面加载完成后，它“返回”到客户端，现在只有客户端代码 (javascript) 可以运行。所以你的线路

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.

您可以做的是将数据从数据库返回到 javascript（使用 json 将是最佳选择），然后使用来自服务器的原始数据（现在是 javascript 数据）动态构建选择框。

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:

您可以使用 javascript 运行此数据并构建您的组合：

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);
}