PHP - Mssql Connect To Php

HI,

I've upgraded from PHP version 5.2 to PHP version 5.4 and are not able to load / find mssql extension ?

How do I use MSSQL in version 5.4 ??

Is MSSQL not supported in this version or am I (hopingly) missing something ???

Regards
Dieter

Hi,

I have been working with inserting unicode characters into mssql database from a php application.

My php web application displays the chinese characters as i entered and retrieves from database properly. But when I take a look at the database the fields are populated with some other values other than the characters i inserted.

When i developed the same application in ASP.net the database has correct values inserted into it.

Here is my code in php:

Code: [Select]
<?php

// direct insert
$var = mssql_connect('RAL-DEV-SQL01','TestDBSa','TestDB$@');

$selected = mssql_select_db('TestDB', $var);


$myVar="&#26085;&#24120;&#29983;&#27963;";


$myChineseVar=$myVar;
echo $myChineseVar;


$query = "insert into TestChinese (TestName) values (N'{$myChineseVar}')"; 

$result = mssql_query($query,$var);

echo "Inserted<br>";
echo $query;


echo "<br>Result<br>";
$result2 = mssql_query("SELECT TOP 10 * FROM [TestChinese] order by SeqNum desc",$var);
while($nt=mssql_fetch_array($result2)){
echo "$nt[SeqNum] - "."$nt[TestName] -";

echo "<br>";

} 

mssql_close($var);

?>
Can someone help me with this problem

Thanks

I'm having problems with enabling the sort functions of jqgrid.  I think I've narrowed the problem down to the way MSSQL is returning the data to the SQL query.

In an effort to troubleshoot, I've drastically simplified the problem.  The query I'm using is below.

Code: [Select]
SELECT * FROM dbo.test ORDER BY locationName
When I run the query directly in MSSQL Management Studio, the top 5 ID entries are 132, 1309, 1295, 1281, 1267.

When I run the corresponding code in PHP, the top 5 ID entries are 1266, 1267, 1268, 1269, 1270 - it seems to be ignoring my order by clause.  Is there something I need to do to force it to honor the ORDER BY locationName?

Code: [Select]
$kpiQuery = "SELECT * FROM dbo.test ORDER BY locationName";
$result = mssql_query($kpiQuery);
while($kpiRow = mssql_fetch_array($kpiResult)) {
echo $kpiRow[plDataID]."<br>";
}

I'm running: Windows Server 2003 IIS 6.0 Microsoft SQL 2005 PHP 5.3.28       Everything that i have read says, "5.3 got rid of mssql and now uses sqlsrv" so i added extension=php_sqlsrv_53_nts_vc9.dll  to my php.ini (and yes it is in the ext folder)   and when i run the script  

$serverName = "localhost\phonebook"; //serverName\instanceName
// Since UID and PWD are not specified in the $connectionInfo array,
// The connection will be attempted using Windows Authentication.
$connectionInfo = array( "Database"=>"XXXXX", "UID"=>"XXXXX", "PWD"=>'XXXXXXXX');
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn ) {
     echo "Connection established.<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}
 

  i get  
Connection could not be established.
Array ( [0] => Array ( [0] => IMSSP [SQLSTATE] => IMSSP [1] => -49 [code=auto:0] => -49 [2] => This extension requires either the Microsoft SQL Server 2008 Native Client (SP1 or later) or the Microsoft SQL Server 2008 R2 Native Client ODBC Driver to communicate with SQL Server. Neither of those ODBC Drivers are currently installed. Access the following URL to download the Microsoft SQL Server 2008 R2 Native Client ODBC driver for x86: http://go.microsoft..../?LinkId=163712 [message] => This extension requires either the Microsoft SQL Server 2008 Native Client (SP1 or later) or the Microsoft SQL Server 2008 R2 Native Client ODBC Driver to communicate with SQL Server. Neither of those ODBC Drivers are currently installed. Access the following URL to download the Microsoft SQL Server 2008 R2 Native Client ODBC driver for x86: http://go.microsoft..../?LinkId=163712 ) [1] => Array ( [0] => IM002 [SQLSTATE] => IM002 [1] => 0 [code=auto:0] => 0 [2] => [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified [message] => [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified ) ) What can i do to fix this? I assume use an older .dll?

Hello,   I am trying to run a report as per the below mssql query:  

$query = "SELECT tblWJCItem.AddedDescription, tblWJC.WJCPrefix, tblWJC.WJCNo, tblWJCItem.MaterialName, tblStockFamily.StockFamily, tblWJCItem.WeightToSend, tblWJC.DateCreated, tblWJC.WJCStatusID FROM tblWJC INNER JOIN tblCustomer ON tblWJC.CustomerID = tblCustomer.CustomerID INNER JOIN tblWJCStockStatus ON tblWJC.WJCStockStatusID = tblWJCStockStatus.WJCStockStatusID INNER JOIN tblStockStatus ON tblWJC.WJCID = tblStockStatus.WJCID LEFT OUTER JOIN tblWJCProductLine ON tblWJC.WJCID = tblWJCProductLine.WJCID
LEFT OUTER JOIN tblWJCStockItem ON tblWJCProductLine.WJCProductLineID = tblWJCStockItem.tblWJCStockItem INNER JOIN tblStockFamily ON tblWJCItem.ProductFamilyID = tblStockFamily.StockFamilyID WHERE tblCustomer.CustomerName = 'NAME' AND tblWJCStockStatus.WJCStockStatus <> 'Stock Usage Confirmed' ORDER BY tblWJC.WJCID";

$query = "SELECT
Table1.*,
Table2.A,
Table2.B,
Table2.C,
Table2.D,
Table2.E
FROM Table1
INNER JOIN
Table2 ON Table1.A = Table2.A

Issue is that there are multiple matches for some instances, like so:

Table1.A value = 1777 (only once since it is a UniqueID)
Table2.A value = 1777 (3 records with this ID value)

So I will get 3 records returned since Table1.A matches 3 records in Table2.A. I want to merge the 3 records in Table2 into 1 record and then match that with Table1 where the UniqueID is the same. Make sense?

I've got some PHP code that accesses SQL on an Amazon cloud server. Other machines running Unix and Windows can perform accesses fine using the server name (IP for Windows, label for Unix), database name (string) and a password (string).  However, the same code FAILS on OSX (my Macbook Pro).

SQL managers on my MBP have no problem accessing it, since I've made the proper exceptions on the AWS firewall.  But PHP can't do any MS SQL reads of this database.

Is there something else I don't know?  Is there some other key information being exchanged that my MBP needs to send that I can't see?

TIA!!

I have a stored procedure on MSSQL that returns a list of results.  This works fine.  The results that are returned may or may not be allowed to be seen by the end user.  So, if 10 results are returned, maybe 7 of them are allowed to be viewed by the end user.  There is a PHP SESSION variable that contains a bunch of codes that is compared to a variable returned from the search result. When these match, the result can be seen by the user.

So, I am using a php if statement that echos the result based on the result of the if statement.  That works fine.  What does not work  is the found count for the array.  For example, if 10 are found, it returns 10, but the if statement removes 3 making 7 of the results viewable. 

So, is there a way to iterate through an array result set in PHP and remove results based on an if statement creating a new array?

Hope this is not too confusing! 

We're moving to a new web server and we recently migrated to a new MS SQL server, as well. All PHP functions on the old web server were switched to point to the new SQL server and all was well.

But..... Now I''m trying to move my PHP functions to the new web server and it won't connect to the database. PHP works fine because I can echo "Testing" or whatever. I've downloaded the SQL Server PHP drivers from Microsoft, changed the connection string from MSSQL to SQLSRV, updated the PHP.ini file, and taken several other steps. It still can't see the database.

Ideas?

Oh, yeah. And both the new web server and MS SQL server are Server 2008 R2.

Hello everyone,   

<?php
$sql = ";WITH TOTAL_KWH_WINTER AS
(
SELECT CONVERT(VARCHAR(10),cdate,111)AS trans_date, datepart(hh, cdate) as trans_hour, comm_id,
MIN((total_energy_a+total_energy_b+total_energy_c)/100) AS minUsage,
MAX((total_energy_a+total_energy_b+total_energy_c)/100) AS maxUsage, meter_multiplier
  FROM [radiogates].[dbo].[purge_data] LEFT OUTER JOIN [radiogates].[dbo].[ops_invoice] on [radiogates].[dbo].[purge_data].[comm_id] = [radiogates].[dbo].[ops_invoice].[meter_id] 
  where comm_id='$comm_id'and meter_multiplier is not null 
  group by comm_id, CONVERT(VARCHAR(10),cdate,111), datepart(hh, cdate), meter_multiplier
)
SELECT *, datepart(weekday, trans_date) as trans_date_day, datepart(month, trans_date) as 

trans_date_month, ((maxUsage - minUsage)*meter_multiplier) as totalUsage
FROM TOTAL_KWH_WINTER 

where datepart(weekday, trans_date) IN ('1', '2', '3', '4', '5', '6', '7') 
AND DATEPART(MONTH, trans_date) IN ('10','11','12','1','2','3','4') 
and trans_date BETWEEN '$startdate2 00:00:01' AND '$enddate2 24:00:00' ";
$query = sqlsrv_query($conn, $sql);if ($query === false){  exit("<pre>".print_r(sqlsrv_errors(), true));}while ($row = sqlsrv_fetch_array($query)){  	$sumUsageKWH += $row[totalUsage];}sqlsrv_free_stmt($query);?> 

**************For whatever reason, my browser will not let me put this code where it needs to go, so its up top.  Sorry for the inconvenience.*************   My concern are code lines 4 through 15   I have PHP code that queryies MSSQL for data called kilowatt hours (kWh), set in a combination of three columns.  kWh is set into the db table once every minute and it a value that starts at zero (0) at the beginning of the meters life, then continues until it reaches 10,000,000 before resetting back to zero (0).  This happens over weeks, months or years, but it does happen.  The code I have listed here makes no consideration of this reset.  Since we are alsways querying between a start date and a stop date, we need to know the kWh usage between that period.    So, we used  maxUsage - minUsage and called that the totalUsage.  That works great until the data reset at 10M.  At that point, max usage will be some value (ex. 1 kWh), where min usage will be some value (ex. 9,999,999 kWh).  the system breaks down at that point.  I need to find an algorithm that helps me take into consideration this rolling number set.    Thanks for any help! 
Edited by Butterbean, 16 January 2015 - 09:28 AM.

This is posting to any row in mssql that has a matching 'start_date'.  I would like it to post rows where start_date = '$start_date' and it also matches the 'meter_id' .  It should perform this check of both parameters before posting to mssql. 

<?php
$session_id = $_SESSION['id'];
$comm_id = $_GET['meter_id'];
$start_date = $_GET['start_date'];
$payment_date = $_POST['payment_date'];
$amount_paid = $_POST['amount_paid'];
$check_number = $_POST['check_number'];

$sql = "UPDATE [radiogates].[dbo].[ops_invoice_history] SET payment_date = '$payment_date', amount_paid = '$amount_paid', check_number = '$check_number' where start_date = '$start_date'";

$query = sqlsrv_query($conn, $sql);

Thank you for any help.

Hi all,   I am trying to do a query on a database that takes a variable from a html drop down box. I've tried so many different forums and can't find the answers.    HTML PAGE:       <html>

I am using PHP with ODBC to connect to an existing MSSQL database to query for some names.  When I query a name that has an apostrophe in it, I get an error.

Example typing O'Malley as the name:

Quote

Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'malley'., SQL state 37000 in SQLExecDirect



I have used addslashes to the string but get the same result:

Code: [Select]
$string = addslashes($_POST['string']);

$connect = odbc_connect("$mssql_name", "$mssql_user", "$mssql_pass");

$query_mssql = "SELECT pr.NameFirst, pr.NameLast, pr.NameMiddle, pr.Birthdate, p.Barcode, p.OrganizationID
FROM PatronRegistration pr
JOIN Patrons p ON (pr.PatronID = p.PatronID)
WHERE pr.NameLast = '$string'";

$result = odbc_exec($connect, $query_mssql);

Any ideas?

Hi All,

I'm hitting brick walls while trying to run a stored procedure query with PDO (linking to MSSQL)

If I run the query from SSMS (SQL Server Management Studio) I get a result every time (one single row is returned, as expected) - I'm placing the same query into PHP without any dynamic variables etc - its just a straight query... when it runs the query in PDO it fails every time with this error:

PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[IMSSP]: The active result for the query contains no fields.' in index.php:195
Stack trace:
#0 index.php(195): PDOStatement->fetch(2)
#1 {main}
  thrown in index.php on line 195

The code is as follows:

	$SQLSTMTA="exec pEmployeeGetData @EmployeeID='1',@Year='2020'";
	$STHA = $DBH2->prepare($SQLSTMTA);
	//$STH->bindParam(':EMPID', $EmployeeID);
	$STHA->execute();
		while($result = $STHA->fetchAll()) {              //(row 195)
		var_dump($result);
	}

I've also tried a few variations on the above such as ->Fetch (as opposed to FetchAll) without any luck.

Any ideas on how I can get around this?

Thanks in Advance!