$myServer = "mynb";
$myUser = "welove";
$myPass = "love";
$myDB = "Daily";
//connection to the database
$dbhandle = mssql_connect($myServer, $myUser, $myPass) or die("Couldn't connect to SQL Server on $myServer");
$selected = mssql_select_db($myDB, $dbhandle)
or die("Couldn't open database $myDB");
$query = "SELECT Date , Type , Location , SUM(Number) AS n1 , SUM(Amount) AS n2 ";
$query .= "FROM CA ";
$query .= "WHERE Location='zone_a' and Type='A' ORDER BY Date GROUP BY Date ";
$result = mssql_query($query);
$numRows = mssql_num_rows($result);
echo "<h1>" . $numRows . " Row" . ($numRows == 1 ? "" : "s") . " Returned </h1>";
while($row = mssql_fetch_array($result))
{
echo "<li>" . $row["Date"] . $row["Type"] . $row["Location"] . $row["n1"] . $row["n2"] ."</li>";
}
Error แบบเนี่ยคะ
Warning: mssql_query(): message: Column 'CA.Type' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. (severity 16) in c:\appserv\www\daily\index5.php on line 51
Warning: mssql_query(): message: Column 'CA.Location' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. (severity 16) in c:\appserv\www\daily\index5.php on line 51
Warning: mssql_query(): Query failed in c:\appserv\www\daily\index5.php on line 51
Warning: mssql_num_rows(): supplied argument is not a valid MS SQL-result resource in c:\appserv\www\daily\index5.php on line 52
คือเค้าห้ามตั้งชื่อฟิลด์ซ้ำกับ reserve word ครับ แต่ถ้าทำไปแล้วเวลาเรียกให้ใช้แบบนี้ครับ (Date กับ Type เนี่ยห้ามน่ะครับ)
select `Date`, `Type` , Location
..................
WHERE Location='zone_a' and `Type`='A' GROUP BY `Date` ORDER BY `Date`
ข้อสำคัญต้อง group by ก่อนค่อยตามด้วย order by ครับ
Load balance : Server 05
