javascript - 我如何修改脚本以打印 mysql 查询中的所有元素 - php mysql javascript ajax

Question

使用 Ajax 的多选下拉列表

下面显示的是在具有多个下拉选择的网页上显示的文本连接到 mysql 数据库。通过选择下拉站点、菜单和分类，查询返回表中称为链接的最后一列，该列在页面 test.php 上使用 ajax 显示。 我想显示查询中的所有字段？

选择:站点:菜单:类别:

实际结果示例

coursesweb.net/php-mysql/writing-php-scripts    
coursesweb.net/php-mysql/arrays  
coursesweb.net/php-mysql/php-mysql-using-mysqli

我需要的结果示例 下面是我想要实现的实际输出

站点、站点、菜单、类别

coursesweb.net, PHP-MySQL, Lessons, coursesweb.net/php-mysql/writing-php-scripts  
coursesweb.net, PHP-MySQL, Lessons, coursesweb.net/php-mysql/arrays  
coursesweb.net, PHP-MySQL, Lessons, coursesweb.net/php-mysql/php-mysql-using-mysqli

研究我已经完成了下面链接中的所有教程 multi dropdown select search form (jQuery,php,mysql)搜索了这个网站和其他各种网站，这是我的应用程序的第 8 次修订，这个版本只使用一个 mysql 表而不是不同的表来创建下拉列表 ajax。

我怎样才能产生所需的结果？

我尝试过的

我在 select_list.php 中创建了一个名为 $where_2 的变量，其中包含下拉菜单的选定值，但我不确定如何将它附加到输出中，我最好的尝试是将它加入变量 $re_html最后一行select_list.php 这打印它但不是我想要的格式。作为新手非常感谢任何帮助能不能简单点。

脚本

我在下面包含了工作脚本以供引用，它们可以在 http://coursesweb.net/ 找到

    // ajax_select.js    

     // Multiple select lists - http://coursesweb.net/ajax/

    // function used to remove the next lists already displayed when it chooses other options
    function removeLists(colid) {
      var z = 0;
      // removes data in elements with the id stored in the "ar_cols" variable
      // starting with the element with the id value passed in colid
      for(var i=1; i<ar_cols.length; i++) {
        if(ar_cols[i]==null) continue;
        if(ar_cols[i]==colid) z = 1;
        if(z==1) document.getElementById(preid+ar_cols[i]).innerHTML = '';
      }
    }

    // create the XMLHttpRequest object, according browser
    function get_XmlHttp() {
      // create the variable that will contain the instance 
          // of the XMLHttpRequest object (initially with null value)
      var xmlHttp = null;
      // for Forefox, IE7+, Opera, Safari
      if(window.XMLHttpRequest) { xmlHttp = new XMLHttpRequest(); }
          // IE5 or 6
      else if(window.ActiveXObject) { xmlHttp = new ActiveXObject("Microsoft.XMLHTTP"); } 

      return xmlHttp;
    }

    // sends data to a php file, via POST, and displays the received answer
    function ajaxReq(col, wval) {
      removeLists(col);           // removes the already next selects displayed

      // if the value of wval is not '- - -' and '' (the first option)
      if(wval!='- - -' && wval!='') {
        var request =  get_XmlHttp(); // call the function with the XMLHttpRequest instance
        var php_file = 'select_list.php';     // path and name of the php file

        // create pairs index=value with data that must be sent to server
        var  data_send = 'col='+col+'&wval='+wval;

        request.open("POST", php_file, true);           // set the request

        document.getElementById(preid+col).innerHTML = 'Loadding...';   // display a loading notification

        // adds a header to tell the PHP script to recognize the data as is sent via POST
        request.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
        request.send(data_send);            // calls the send() method with data_send

        // Check request status
        // If the response is received completely, will be added into the tag with id value of "col"
        request.onreadystatechange = function() {
          if (request.readyState==4) {
            document.getElementById(preid+col).innerHTML = request.responseText;
          }
        }
      }
    }


**select_list.php**

     <?php
    // Multiple select lists - http://coursesweb.net/ajax/
    if(!isset($_SESSION)) session_start();

    // Here add your own data for connecting to MySQL database
    $host = 'xxxxxx';
    $user = 'xxxx';
    $passwd = 'xxxxxxx';
    $dbname = 'xxxxxxxx';

    // Here add the name of the table and columns that will be used for select lists, in their order
    // Add null for 'links' if you don`t want to display their data too
    $table = 'sites';
    $ar_cols = array('site', 'menu', 'categ', 'links');

    $preid = 'slo_';        // a prefix used for element's ID, in which Ajax will add <select>
    $col = $ar_cols[0];     // the variable used for the column that wil be selected
    $re_html = '';          // will store the returned html code

    // if there is data sent via POST, with index 'col' and 'wval'
    if(isset($_POST['col']) && isset($_POST['wval'])) {
      // set the $col that will be selected and the value for WHERE (delete tags and external spaces in $_POST)
      $col = trim(strip_tags($_POST['col']));
      $wval = "'".trim(strip_tags($_POST['wval']))."'";
    }

    $key = array_search($col, $ar_cols);            // get the key associated with the value of $col in $ar_cols
    $wcol = $key===0 ? $col : $ar_cols[$key-1];     // gets the column for the WHERE clause
    $_SESSION['ar_cols'][$wcol] = isset($wval) ? $wval : $wcol;    // store in SESSION the column and its value for WHERE

    // gets the next element in $ar_cols (needed in the onchange() function in <select> tag)
    $last_key = count($ar_cols)-1;
    $next_col = $key<$last_key ? $ar_cols[$key+1] : '';

    $conn = new mysqli($host, $user, $passwd, $dbname);     // connect to the MySQL database

    if (mysqli_connect_errno()) { exit('Connect failed: '. mysqli_connect_error()); }     // check connection

    // sets an array with data of the WHERE condition (column=value) for SELECT query
    for($i=1; $i<=$key; $i++) {
      $ar_where[] = '`'.$ar_cols[$i-1].'`='.$_SESSION['ar_cols'][$ar_cols[$i-1]];
    }

    // define a string with the WHERE condition, and then the SELECT query
    $where = isset($ar_where) ? ' WHERE '. implode($ar_where, ' AND ') : '';
    $where_2 = isset($ar_where) ? ''. implode($ar_where, ',') : '';
    // DISTINCT  only shows individual elements no duplicates in the drop down
        $sql = "SELECT DISTINCT `$col` FROM `$table`".$where; 



    $result = $conn->query($sql);       // perform the query and store the result
    //print_r($result); // DEBUG
    // if the $result contains at least one row
    if ($result->num_rows > 0) {
      // sets the "onchange" event, which is added in <select> tag
      $onchg = $next_col!==null ? " onchange=\"ajaxReq('$next_col', this.value);\"" : '';

      // sets the select tag list (and the first <option>), if it's not the last column
      if($col!=$ar_cols[$last_key]) $re_html = $col. ': <select name="'. $col. '"'. $onchg. '><option>- - -</option>';

      while($row = $result->fetch_assoc()) {
        //printf ("%s (%s)\n", $row["site"], $row["menu"]); // DEBUG
        // if its the last column, reurns its data, else, adds data in OPTION tags
        if($col==$ar_cols[$last_key]) $re_html .= '<br/>'. $row[$col]; 
        else $re_html .= '<option value="'. $row[$col]. '">'. $row[$col]. '</option>';

      }

      if($col!=$ar_cols[$last_key]) $re_html .= '</select> ';        // ends the Select list
    }
    else { $re_html = '0 results'; }

    $conn->close();

    // if the selected column, $col, is the first column in $ar_cols
    if($col==$ar_cols[0]) {
      // adds html code with SPAN (or DIV for last item) where Ajax will add the select dropdown lists
      // with ID in each SPAN, according to the columns added in $ar_cols
      for($i=1; $i<count($ar_cols); $i++) {

        if($ar_cols[$i]===null) continue;

        if($i==$last_key) $re_html .= '<div id="'.$preid.$ar_cols[$i]. '"> </div>';
        else $re_html .= '<span id="'.$preid.$ar_cols[$i]. '"></span>'; // $where_2
        //print_r($ar_cols); // DEBUG
        //echo $re_html; // DEBUG
      }

      // adds the columns in JS (used in removeLists() 
          // to remove the next displayed lists when makes other selects)
      $re_html .= '<script type="text/javascript">var ar_cols = '.json_encode($ar_cols).'; var preid = "'. $preid. '";</script>';
    }
    else 
        echo $re_html . ",". $where_2."<br>"; // DEBUG 

    ?>

**test.php**  

    <?php include 'select_list.php'; ?>
    <!doctype html>
    <html>
    <head>
    <meta charset="utf-8" />
    <title>Multiple Select Dropdown list with Ajax</title>
    <script src="ajax_select.js" type="text/javascript"></script>
    </head>
    <body>
    <h1>Multiple Select Dropdown list with Ajax</h1><br/>

    <form action="" method="post">
    Select: <?php echo $re_html; ?>
    </form>

    </body>
    </html>

Answer 1

这看起来是一个等待发生的 SQL 注入(inject)攻击:

 // define a string with the WHERE condition, and then the SELECT query
$where = isset($ar_where) ? ' WHERE '. implode($ar_where, ' AND ') : '';
$where_2 = isset($ar_where) ? ''. implode($ar_where, ',') : '';
// DISTINCT  only shows individual elements no duplicates in the drop down
    $sql = "SELECT DISTINCT `$col` FROM `$table`".$where; 

使用准备好的语句:http://php.net/manual/en/mysqli.prepare.php

一旦你解决了这个问题，就你的格式问题而言，只需 str_replace() 输出。

echo str_replace('/',', ',$re_html);