PHP - Mssql And Php Query Issues

I have this code that i tried running it on my phpmyadmin it works fine ,but  when i use it on my php code to query its not working.What could be the issue?

 SELECT * FROM `roombook` ORDER BY (CASE stat
   WHEN 'Checked in' 	 THEN 1
   WHEN 'Booked' 	 THEN 2
   WHEN 'Deposit Confirmation' 	 THEN 3
   WHEN 'Email/phone' THEN 4
   WHEN 'Checked Out' 	THEN 5
   ELSE 'Cancelled' END) ASC, stat ASC

 

I get this error when I run the query:  and I have no idea how to fix..  I have been staring at this for days.


Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, null given in /onlinebanking/viewtransactions.php on line 65

This is my view transactions page
Code: [Select]
<?php
 //viewtransaction 
  require_once('../websiteconfig.inc.php');
    require_once(ABSOLUTE_PATH . 'class/database.class.php');
require_once(ABSOLUTE_PATH . 'class/person.class.php'); 
require_once(ABSOLUTE_PATH . 'class/bankaccount.class.php');



/*START SESSION*/
session_start();

$currentMember = unserialize($_SESSION['currentMember']);
$currentMember->connection = $conn;

/*Database*/
$db = new Database;
$conn = $db->connection;

$bank_account = $_GET['q'];

?>


       
        </div>
        <link href="../_assets/stylesheets/css_dd_bankingnav.css" rel="stylesheet" type="text/css" />
        <link href="../_assets/stylesheets/style.css" rel="stylesheet" type="text/css" />
        <div class="container" id="shadow">
        <div>
      <?php 

                include(ABSOLUTE_PATH . 'header.inc.php');
            ?>


                <div>
                <h1 class="body" align="center">Accounts</h1>
               
                <?php
                include(ABSOLUTE_PATH . 'onlinebanking/onlinebankingnav.inc.php');
               ?>
               
               

                <table id="accounts" summary="Bank Account Balance Information" border="3" align="center">
                <thead>
                <tr>
                        <th bgcolor="#CCCCCC">Account ID</th>
                        <th bgcolor="#CCCCCC">Transaction Date</th>
                        <th bgcolor="#CCCCCC">Transaction Type</th>
                        <th bgcolor="#CCCCCC">Transaction Amount</th>
                        <th bgcolor="#CCCCCC">Current Balance</th>
                     </tr>                     
                </thead>
                <tbody>
<?php

$bankaccount = new Bankaccount($account['BankAccountID']);
$bankaccount->connection = $conn;

/*Loop thorugh account - Grabs data*/
while($transaction = mysqli_fetch_assoc($transactionbalance)) {
$transactionbalance = $bankaccount->retrieve_transactions($transaction['BankAccountID']);

//looks for account data
$querydata = mysqli_num_rows($transactionbalance);

//Looks for transcations
if ($querydata == 0) {
echo ('There is no transaction history on this account');
}
else {
}
echo '<tr>' . "\n";
echo "\t" . '<td>' . $transaction['BankAccountID'] . '</td>' . "\n";
echo "\t" . '<td>' . $accounttype['TransactionDate'] . '</td>' . "\n";
echo "\t" . '<td>' . $accounttype['TransactionType'] . '</td>' . "\n";
echo "\t" . '<td>$' . number_format($accounttype['TransactionAmount'], 2) . '</td>' . "\n";
echo "\t" . '<td>$' . number_format($transaction['CurrentBalance'], 2) . '</td>' . "\n";
echo '<tr>' . "\n";

}


/*Close DB*/
mysqli_close($db->connection);
?>

</tbody>
        </table>
        <br>

               
                </div><!--End of main content-->
<?php 
                include(ABSOLUTE_PATH . 'footer.inc.php');
            ?>

</div><!--end of header-->




This is my bankaccount.class page


Code: [Select]
<?php
/*Bankaccount Class*/
class Bankaccount {

/*attributes for the first and last name of the class*/
private $accountid;
private $memberid;
private $accounttypeid;
private $accounttypename;
public  $connection;

/*Constructs the function*/
function __construct($accountid, $accounttypeid){
$this->accountid = $accountid;
$this->accounttypeid = $accounttypeid;
} //end constructor

/*Destroys the function*/
function __destruct(){
} //ends destructor 

/*Get funtion*/
public function __get($name) {
return $this->$name;
} // Ends get funtction

/*Use the set function*/
public function __set($name, $value) {
$this->$name=$value;
} //End set function


/*This is what retrieves the values from memmory*/
public function retrieve_current_balance() {
$balance_query = "SELECT CurrentBalance UserID FROM BankAccount WHERE BankAccountID = " . $this->accountid . " LIMIT 0,1";
$result = mysqli_query($this->connection, $balance_query);
return $result;
}

This is the part of my bankaccount.class that actually involves viewing my transactions
Code: [Select]
<?php
//query for transactions
public function retrieve_transactions() {
$transaction_query = "SELECT tl.TransactionDate, tt.TransactionType, tl.TransactionAmount FROM TransactionLog tl INNER JOIN TransactionType tt ON tl.TransactionTypeID = tt.TransactionTypeID WHERE BankAccountID = " . $this->accountid . " LIMIT 0,1";
$result = mysqli_query($this->connection, $transaction_query);
$db_array = mysqli_fetch_array($results);
return $result;
}
?>
Code: [Select]
<?php
/*Function validates user account for user ID*/
public function validate_useraccount(){
$account_query = "SELECT UserID FROM BankAccount WHERE UserID = " . $this->accountid;
$results = mysqli_query($this->connection, $account_query);
$db_array = mysqli_fetch_array($results);
$return_value = 1;

if(count($db_array()) < 1) 
{ 
$return_value = 0; 
}  
return $return_value;
}

/*Deposit*/
public function deposit($UserID=0, $BankAccountID=0, $DepositAmount=0){
$getbalance = mysqli_fetch_assoc($this->retrieve_current_balance()); $currentbalance = $getbalance['CurrentBalance'];
$today = date('Y-m-d', mktime());

/*Deposit Funds*/
$newbalance = $currentbalance + $DepositAmount;
$deposit = mysqli_query($this->connection, "UPDATE BankAccount SET CurrentBalance = $newbalance WHERE BankAccountID = $BankAccountID");

if($deposit){ //transaction completed

/*Update Transaction log*/
$log_query = "INSERT INTO TransactionLog (TransactionTypeID, BankAccountID, UserID, TransactionAmount, TransactionDate) VALUES (1, $BankAccountID, $UserID, $DepositAmount, $today')";

mysqli_query($this->connection, $log_query);
}

/*New Balance*/
return $newbalance;

}
} //End of class

?>

Hey all,

First off, if this is in the wrong section I apologize. I wasn't sure if it should be here or the mySQL section.

What's going on is, I'm in the process of learning the Prepared Statement way of doing things and am changing / updating my code to reflect the changes. Everything was going fine until I attempted to do what I could do using old MySQL methods and that is display the queried results on the same page.


I can place a query and display the results as they should be displayed if I only use one block of code. However, if I try to do any additional queries on the same page, they get killed and do not display anything even though I know the query is fine because I can test the exact same syntax below one a different page and it works.

Here's a code snippet for an example:
Code: [Select]

Code:
<table>
<tr>
<td>
// The below code will display a selection box containing various strings such as "hello world", "great to be here", "Wowserz", "this is mind blowing" etc. that are stored in the database.

<?php

   echo "<select = \"SpecialConditions\">";
   if($stmt->num_rows == NULL){
      echo "No results found.";
   }else{
      while($stmt->fetch()){
   
       echo "<option value=\"$specialId\">$specialcondition</option>";
   }
}
   echo "</select>";
?>
</td>
<td>
// If I place another fetch query below the above fetch() query, this one will not show up. This one is supposed to display values 1 - 20 that have been stored in the DB.

<?php
echo "<select = \"NumberSets\">";
   if($stmt->num_rows == NULL){
      echo "No results found.";
   }else{
      while($stmt->fetch()){
      
          echo "<option value=\"".$numbers."\">".$numbers."</option>";
      }
   }
echo "</select>";

?>

</td>
</tr>
</table>


What am I doing wrong with this? When I use regular SQL queries I can display multiple results on the same page.

The results are being pulled from two separate joined tables but I don't think that's the issue.

Hello,

am having problem with connection to mssql server and database. Am using Xampp and i think there is some problem about his configuration.
All the time am getting error : missing driver php_mssql.dll in xampp/php/ext/  ....and file php_mssql.dll is there (same problem with php_pdo_mssql.dll).
Also am getting this error: "Fatal error: Call to undefined function mssql_connect() "
I tried everything i could find on forums, blogs, and php documentation...but always same.

btw... i removed ";"  in php.ini from necessary lines and i copy ntwdblib.dll in necessary folders.
This is my connection code:
Code: [Select]
   $dbHost = 'Workstation:<port>';
   $dbUser = 'user';
   $dbPass = 'pass';
   $dbName = 'database';
$con = mssql_connect ($dbHost, $dbUser, $dbPass) or die ('MsSQL connect failed. ' . mssql_error());
mssql_select_db($dbName) or die('Cannot select database. ' . mssql_error());
I also tried with:
Code: [Select]
$connection = odbc_connect("Driver={SQL Server Native Client 10.0};Server=$server;Database=$database;", $user, $password);but always same....


Can anybody please provide me some working solution ?


Thanks.

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?

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

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

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

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

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

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

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.