This page describes one method by which the PHP Graphing Solutions can be configured to pull data directly from a MS SQL Server Database.
In order for PHP to be able to connect to MS SQL the MSSQL extension must be installed in your PHP environment.
For further information on this please see:-
PHP Manual - Function Reference - MS SQL Server »The PHP Graphing functions allow custom built functions to be supplied as the data gathering process. In this method we are going to use this feature to connect and retrieve data from a MS SQL Server database.
The basic format of a data script function is as follows:-
function datascript () { return $lines; }
where $lines is just a one dimensional text array which is populated with the data parameters. Each data parameter (ie. $lines array element) should be in the following format:-
dataNseriesM: [value]where N represents the position of the data item in the series and M represents the series number.
We now need to add the following functionality to the datascript() function:-
1. Connecting to the MS SQL Server is done via the command:-
mssql_connect ( [string servername [, string username [, string password]]] )
For example lets suppose our MS SQL server is running on localhost and we have a username of "user" and password "pass123". Our statement would then be:-
$conn = mssql_connect(localhost,"user","pass123");2. Now we select our database with the command:-
mssql_select_db ( string database_name [, resource link_identifier])
For example lets suppose we have a database "salesdb" then our select db command will be:-
mssql_select_db("salesdb",$conn);3. Issue the Query
Queries are issued with the command:-
mssql_query(string query, resource link)
Lets suppose we have a table "SalesByMonth" which holds sales figures by month with the following fields:-
Here is our PHP code to issue this query:-
$query = "SELECT Month,Value FROM SalesByMonth ORDER BY Month";
$result = mssql_query($query,$conn);
4. The result of the query is now in the array $result. We now need to convert this into the format for the graphing component. This we do as follows:-
$count = 0;
while (
$datarow = mssql_fetch_assoc($result)
) {
$value = $datarow["Value"];
$lines[$count] = "data".$count."series1: ".$value;
$count++;
}
5. Finally we close the DB connection with:-
mssql_close ( [resource link_identifier] )
mssql_close($conn);
Here is our complete data function:-
function datascript() {
// Connect to MS SQL Server
$conn = mssql_connect("localhost","user","pass123");// Select the Database
mssql_select_db("salesdb",$conn);// Issue the Query
$query = "SELECT Month,Value FROM SalesByMonth ORDER BY Month";$result = mssql_query($query,$conn);
// Read the Results and populate the data
// array $lines
$count = 0;
while ( $datarow = mssql_fetch_assoc($result) ) {
$value = $datarow["Value"];
$lines[$count] = "data".$count."series1: ".$value;
$count++;}
// Close the DB Connection
mssql_close($conn);// Return the Data to the Graph process
return $lines;
}
The only thing left to do is to instruct the graph process to use this data function. This done via the "datascript" parameter. For example if we save the above function in a file named "datascript.php" then the <IMG> tag for the Line Graph would be :-
<img src="line-graph.php?datascript=datascript.php"
