通过Javascript / PHP查询数据库 [英] Query Database through Javascript / PHP

问题描述

我无法尝试将PHP脚本的结果返回到我的HTML / Jquery应用程序中。

I am having trouble trying to return results from a PHP script, back into my HTML / Jquery application.

目前，我尝试连接到服务器，如下所示：

Currently, I am trying to connect to the server like so:

function getDatabaseRows()
{           
    var xmlHttp = new XMLHttpRequest();

    xmlHttp.onreadystatechange = function()
    {
        if(xmlHttp.readyState == 4)
        {
          HandleResponse(xmlHttp.responseText);
        }
    }

    xmlHttp.open("POST", "myDomain.com/subDirectory/getRowCounts.php", true);
    xmlHttp.send(null);
}

我的回应处理程式为：

function HandleResponse(response)
{   
    alert(response.length);

    if(response.length != 0)
        maxArray = JSON.parse(response);

    storage.set('currentID', maxArray);
}

目前，每当我运行代码时， getDatabaseRows ）总是最后调用 HandleResponse（response）。问题是 response.length 总是等于 0 。

Currently, whenever I run the code, getDatabaseRows() always ends up calling HandleResponse(response). The problem is that response.length always equals 0.

PHP脚本只是从数据库中收集最大行数并返回行数：

The PHP script is simply collecting the maximum row count from the database and returning the row counts:

<?php

   $rowCounts = [];

   $dbhost = 'host';
   $dbuser = 'host';
   $dbpass = 'host';

   $con = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error connecting to mysql');
   mysql_select_db("host") or die(mysql_error());

   $sql = "SELECT MAX(TID) AS MaxTID FROM TransData";
   $results = mysql_query($sql, $con);
   $transRow = mysql_fetch_array($results);
   $maxTrans = $transRow['MaxTID'];

   $sql = "SELECT MAX(FID) AS MaxFID FROM FullData";
   $results = mysql_query($sql, $con);
   $fullRow = mysql_fetch_array($results);
   $maxFull = $fullRow['MaxFID'];

   $sql = "SELECT MAX(SID) AS MaxSID FROM SalamanderData";
   $results = mysql_query($sql, $con);
   $salamanderRow = mysql_fetch_array($results);
   $maxSal = $salamanderRow['MaxSID'];

   $sql = "SELECT MAX(OID) AS MaxOID FROM OthersData";
   $results = mysql_query($sql, $con);
   $othersRow = mysql_fetch_array($results);
   $maxOther = $othersRow['MaxOID'];

   array_push($rowCounts, $maxTrans, $maxFull, $maxSal, $maxOther);

   echo json_encode($rowCounts);

   mysql_close($con);
?>

当我将浏览器直接指向脚本时，echo的行数正确。我只能通过AJAX尝试获取数据。

When I point my browser directly to the script, it echo's the row counts correctly for me. I am only having trouble trying to get data through AJAX.

编辑：

脚本连接，它与跨域脚本问题有关。现在排序。

Ok, I finally got the script connected, it had something to do with Cross-Domain scripting problems. All sorted out now.

新问题：

function HandleResponse(response)
{   

    if(response.length != 0)
        maxArray = JSON.parse(response);

    storage.set('currentID', maxArray);
}

HandleResponse在解析JSON时会抛出错误解析'<'，所以我假定它与Array编码的方式有关。

HandleResponse throws an error when parsing the JSON Error trying to parse '<', So I assume that it has something to do with the way that Array was encoded. How do I go about troubleshooting this problem and figuring out how to parse that array?

推荐答案

这是最简单的jQuery JavaScript我可以得到它（这使用GET方法）：

This is the simplest jQuery JavaScript I could get it down to (this uses the GET method):

function getDatabaseRows()
{           
  $.getJSON('myDomain.com/subDirectory/getRowCounts.php', function(data) {
    console.log(data);
  });
}

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

并对您的PHP使用 foreach {} 和 mysql_result（），虽然你可能想看看PHP的 mysqli 类以获得更加面向未来的解决方案。

And some enhancements to your PHP using a foreach {} and mysql_result(), although you may want to look into PHP's mysqli class for a more future-proof solution.

$rowCounts = array();

$dbhost = 'host';
$dbuser = 'host';
$dbpass = 'host';
$dbase  = 'host';

$fields = array(
    'MaxTID' => array('tab' => 'TransData',      'col' => 'TID'),
    'MaxSID' => array('tab' => 'FullData',       'col' => 'SID'),
    'MaxFID' => array('tab' => 'SalamanderData', 'col' => 'FID'),
    'MaxOID' => array('tab' => 'OthersData',     'col' => 'OID')
  );

$con = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error connecting to mysql');
mysql_select_db($dbase, $con) or die(mysql_error());

foreach ($fields as $id => $info) {
  $sql = "SELECT MAX({$info['col']}) FROM {$info['tab']}";
  $result = mysql_query($sql, $con);
  if (!$result) die('Could not query:' . mysql_error());
  $rowCounts[$id] = mysql_result($result, 0);
}

echo json_encode($rowCounts);

mysql_close($con);

此代码在GoDaddy托管网站上运行。对于记录，这是我的第一个Stack Overflow答案。请指教：）

This code worked on a GoDaddy hosted site. For the record, this is my first Stack Overflow answer. Please advise :)

这篇关于通过Javascript / PHP查询数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！