PHP - Php & Mssql

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?

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!!

Hi every one,
i am trying to connect with a remote SQL server on window platform.
i have hostname, username, password and database name, but when i try t connect using mssql_connect($server,$username,$password)

i got this Warning: mssql_connect() [function.mssql-connect]: Unable to connect to server:

Thanks

Hi,
I knew the way to connect to mysql but i don't know how to connect mssql.
Is it the same?

here's an example for mysql.
please teach me the mssql.

$con = new mysqli(DB_HOST,DB_USER,DB_PASS,DB_NAME);

$sql = "SELECT COUNT(*) FROM numbers";
$result = $con->query($sql);
$r = $result->fetch_row();



thanks

$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?

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";

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.

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>

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.

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.

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!

 

strings from a field(type: VARCHAR(4000)) are being truncated at 255 characters. Apparently this is how varchars work(how is that useful?). I've found these two solutions on the internet but neither of them seemed to work.

first, some people thought it might be a php.ini thing, so I put this at the top of my php file:

Code: [Select]
ini_set ( 'mssql.textlimit' , '65536' );
ini_set ( 'mssql.textsize' , '65536' );
that didn't work, so other people suggested using the text field type instead but these two queries return nothing, so I must be doing something wrong or this just isn't the answer:

Code: [Select]
$bquery="SELECT CONVERT(TEXT,description) FROM table WHERE userID='$row[userID]'";
$bquery="SELECT CAST(description AS TEXT) FROM table WHERE userID='$row[userID]'";

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?

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! 

I have this report that I have written and it looks fine on the screen but the printed output is to big for the page and very ugly. How would I reformat it so it prints better.

<html>
<head>
<title>Load Report</title>
</head>
<body>
<h1>Load Report</h1>


<?php
$load=$_POST["Load_ID"];
$connect =odbc_connect("removed");
if(!$connect) {
	exit("Connection Failed: " . $connect);
}

$sql="SELECT m1_DC.dbo.SalesOrders.UOMPDROPSEQUENCE
,m1_DC.dbo.organizationlocations.cmlPhoneNumber
,m1_DC.dbo.organizationlocations.cmlName
,m1_DC.dbo.organizationlocations.UCMLSTOREHOURSTXT
	,m1_DC.dbo.SalesOrders.OMPSHIPPINGMETHODID
	,m1_DC.dbo.SalesOrders.UOMPTRUCKNUMBER
	,m1_DC.dbo.SalesOrders.ompCustomerPO
	,m1_DC.dbo.SalesOrders.UOMPTOTALBOXCOUNT
	,m1_DC.dbo.SalesOrders.UOMPVOLUMETOTAL
	,m1_DC.dbo.organizationlocations.CMLADDRESSLINE1
	,m1_DC.dbo.organizationlocations.CMLADDRESSLINE2
	,m1_DC.dbo.organizationlocations.CMLCITY
	,m1_DC.dbo.organizationlocations.CMLSTATE
	 ,m1_DC.dbo.organizationlocations.CMLPOSTCODE
	,m1_DC.dbo.SalesOrders.OMPREQUESTEDSHIPDATE
	,m1_DC.dbo.SalesOrders.ompSalesOrderID
	,m1_DC.dbo.SalesOrders.ompOrderTotalBase
	,convert(varchar(10),m1_DC.dbo.SalesOrders.uompbuilddate,110) as BuildDate
FROM m1_DC.dbo.SalesOrders
LEFT OUTER JOIN m1_DC.dbo.organizationlocations ON ompshiporganizationid = cmlorganizationid
	AND ompshiplocationid = cmllocationid
WHERE 
	m1_DC.dbo.SalesOrders.UOMPTRUCKNUMBER='$load'
	Union All
	
	SELECT m1_KF.dbo.SalesOrders.UOMPDROPSEQUENCE
	,m1_KF.dbo.organizationlocations.cmlPhoneNumber
	,m1_KF.dbo.organizationlocations.cmlName
	,m1_KF.dbo.organizationlocations.UCMLSTOREHOURSTXT
	,m1_KF.dbo.SalesOrders.OMPSHIPPINGMETHODID
	,m1_KF.dbo.SalesOrders.UOMPTRUCKNUMBER
	,m1_KF.dbo.SalesOrders.ompCustomerPO
	,m1_KF.dbo.SalesOrders.UOMPTOTALBOXCOUNT
	,m1_KF.dbo.SalesOrders.UOMPVOLUMETOTAL
	,m1_KF.dbo.organizationlocations.CMLADDRESSLINE1
	,m1_KF.dbo.organizationlocations.CMLADDRESSLINE2
	,m1_KF.dbo.organizationlocations.CMLCITY
	,m1_KF.dbo.organizationlocations.CMLSTATE
	,m1_KF.dbo.organizationlocations.CMLPOSTCODE
	,m1_KF.dbo.SalesOrders.OMPREQUESTEDSHIPDATE
	,m1_KF.dbo.SalesOrders.ompSalesOrderID
	,m1_KF.dbo.SalesOrders.ompOrderTotalBase
	,convert(varchar(10),m1_KF.dbo.SalesOrders.uompbuilddate,110)
	
FROM m1_KF.dbo.SalesOrders
LEFT OUTER JOIN m1_KF.dbo.organizationlocations ON ompshiporganizationid = cmlorganizationid
	AND ompshiplocationid = cmllocationid
WHERE 
	m1_KF.dbo.SalesOrders.UOMPTRUCKNUMBER='$load'
	
	order by UOMPDROPSEQUENCE";
	
	

$result =odbc_exec($connect,$sql);
if(!$result){
exit("Error in SQL");
}



$data= '';	

while (odbc_fetch_row($result)) {
	$UOMPDROPSEQUENCE=odbc_result($result,"UOMPDROPSEQUENCE");
	$cmlName=odbc_result($result,"cmlName");
	$cmlPhoneNumber=odbc_result($result,"cmlPhoneNumber");
	$UCMLSTOREHOURSTXT=odbc_result($result,"UCMLSTOREHOURSTXT");
	$ompCustomerPO=odbc_result($result,"ompCustomerPO");
	$UOMPTOTALBOXCOUNT=odbc_result($result,"UOMPTOTALBOXCOUNT");
	$UOMPVOLUMETOTAL=odbc_result($result,"UOMPVOLUMETOTAL");
	$CMLADDRESSLINE1=odbc_result($result,"CMLADDRESSLINE1");
	$CMLADDRESSLINE2=odbc_result($result,"CMLADDRESSLINE2");
	$CMLCITY=odbc_result($result,"CMLCITY");
	$CMLSTATE=odbc_result($result,"CMLSTATE");
	$CMLPOSTCODE=odbc_result($result,"CMLPOSTCODE");
	$ompSalesOrderID=odbc_result($result,"ompSalesOrderID");
	$ompOrderTotalBase=odbc_result($result,"ompOrderTotalBase");
	$uompbuilddate=odbc_result($result,"BuildDate");
	
		{	
	{ if($UOMPDROPSEQUENCE % 2 == 0 )
   {$style = '#FFFAFA';}
    else {$style =  '#D3D3D3';}}
}

	$data .=  "<tr  bgcolor=$style><th>$UOMPDROPSEQUENCE</th>";
	$data .=  "<th style='text-align:left'  height='10'>$cmlName</th>";
  	$data .=  "<td style='text-align:left' height='10'>$UCMLSTOREHOURSTXT</td>";
  	$data .=  "<td style='text-align:center' height='10'>$ompCustomerPO</td>";
  	$data .=  "<td style='text-align:center' height='10'>$UOMPTOTALBOXCOUNT</td>";
  	$data .=  "<td style='text-align:center' height='10'>	$UOMPVOLUMETOTAL</td>";
  	$data .=  "<td style='text-align:center' height='10'>	$CMLADDRESSLINE1</td>";
  	$data .=  "<td style='text-align:center' height='10'>	$CMLADDRESSLINE2</td>";
  	$data .=  "<td style='text-align:center' height='10'>	$CMLCITY</td>";
  	$data .=  "<td style='text-align:center' height='10'>	$CMLSTATE</td>";
  	$data .=  "<td style='text-align:center' height='10'>	$CMLPOSTCODE</td>";
  	$data .=  "<td style='text-align:center' height='10'>$ompSalesOrderID</td>";
  		$data .=  "<td style='text-align:center' height='10'>$ompOrderTotalBase</td>";
  	//$data .=  "<td style='text-align:center'>$uompbuilddate</td>";
  	$data .=  "<td style='text-align:center' height='10'>$cmlPhoneNumber</td></tr>";
}
	
   echo "<table><tr>";
   echo "<th>Load $load</th><tr>";
   echo "<th>Drop</th>";
   echo "<th>Name</th>";
 	echo "<th>Note</th>";
	echo "<th>PO</th>";
	echo "<th>Box_count</th>";
	echo "<th>Cubes</th>";
	echo "<th>ADDRESSLINE</th>";
	echo "<th>ADDRESS2</th>";
	echo "<th>CITY</th>";
	echo "<th>STATE</th>";
	echo "<th>ZIP</th>";
	echo "<th>Order_ID</th>";
		echo "<th>Order_Total</th>";
	//echo "<th>Build-Date</th>";
	echo "<th> Phone </th></tr>";	
	echo $data;
  

odbc_close($connect);
?>
</body>
</html>