Asked  7 Months ago    Answers:  5   Viewed   40 times

So I keep getting this error when I want to query something to the ms sql server..

The connection is made with the database but the queries seem to fail.

The error log contains this:

    PHP Fatal error:  Call to undefined function mssql_query()

The code on the php:

session_start();
include_once("connect.php");
if (isset($_POST['username'])) {
$username = $_POST['username'];
$password = $_POST['password'];
$sql = "SELECT * FROM test WHERE username='".$username."' AND password='".$password."'";
$res = mssql_query ($sql) or die(mssql_error());

if (mssql_num_rows($res) == 1) {
    $row = mssql_fetch_assoc($res);
    $_SESSION['uid'] = $row['id'];
    $_SESSION['username'] = $row['Username'];
    $_SESSION['afdeling'] = $row['Afdeling'];
    $_SESSION['mail'] = $row['Mail'];
              header("Location: test.php");
    exit();
} else {
    echo "Invalid login information. Please return to the previous page.";
    exit(); }  }  ?>

Does anybody knows what the problem is?

Thanks in advance!

connect.php code:

<?php
$serverName = "MTN-TEST"; //serverNameinstanceName
$connectionInfo = array( "Database"=>"PROCES_TEST", "UID"=>"blaaa", "PWD"=>"blooo");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
     echo "<span style='color:green;'>Connection established.</span><br />";
}else{
     echo "<span style='color:red;'>Connection could not be established.</span><br />";
    die( print_r( sqlsrv_errors(), true));
}
?>

 Answers

73

You don't have the MS SQL Drivers installed. You can check this with phpinfo();

On Linux you need mssql.so or sybase.so With debian its apt-get install php5-sybase

For windows take a look here: http://msdn.microsoft.com/en-US/library/cc793139%28v=SQL.90%29.aspx

Drivers need to be configured for PHP to find the function mssql_...

You could also look at PDO DB classes as they can connect to any DBS, you need the drivers installed tho.

Wednesday, March 31, 2021
 
Anele
answered 7 Months ago
37

Call to undefined function error clearly shows that its not getting your function where you have defined. Reason is you are attaching full path of settings.php file with http.

You need to include settings.php file without http path at the top of 'index.php' and make sure settings.php file is located in your project only. If it is located at the same folder with index.php, then simply include like below.

<?php include 'settings.php'; ?> 

If your settings.php file is located in some other website but in your same server then you can use $_SERVER['DOCUMENT_ROOT'] to include that file like below:

<?php 
$path = $_SERVER['DOCUMENT_ROOT'];
$path .= "/yourpath/settings.php";
include_once($path);
?>

If your settings.php file is located in some other website and some other server then you need to update allow_url_include=On in your php.ini that is the MAJOR SECURITY RISK. So, if you trust the website then only do it.

// Extremely insecure:
<?php include("http://sub.website.com/incl/Settings.php"); ?>
Wednesday, March 31, 2021
 
alioygur
answered 7 Months ago
31

1. How to retrieve Date and Time values from SQL Server:

1.1. Retrieve Date and Time values from SQL Server uisng the PDO_SQLSRV version of PHP Driver for SQL Server:

When you use PDO_SQLSRV driver, values from date and time columns are returned as strings before version 5.6.0 of the driver. In this case the only option is to reformat date and time values (from '2019-03-26 00:00:00.000' to '26-03-2019 00:00:00.000' for example). Starting with version 5.6.0, you can change this behavior using PDO::SQLSRV_ATTR_FETCHES_DATETIME_TYPE connection or statement attribute and retrieve date and time field values as PHP DateTime variable. Then you can easily format this variable as string using DateTime::format.

<?php
$server   = "serverinstanse";
$database = "database";
$username = "username";
$password = "password";

try {
   $conn = new PDO("sqlsrv:server = $server; Database = $database", $username, $password);
   $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch( PDOException $e ) {
   die( "Error connecting to SQL Server".$e->getMessage());
}

try {
   $query = "SELECT DateTimeColumn FROM OneTable";
   $stmt = $conn->prepare($query);
   $stmt->setAttribute(PDO::SQLSRV_ATTR_FETCHES_DATETIME_TYPE, true);
   $stmt->execute();
} catch( PDOException $e ) {
   die( "Error connecting to SQL Server".$e->getMessage());
}
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { 
   echo print_r($row, true);
}

$stmt = null;
$conn = null;
?>

1.2. Retrieve Date and Time values from SQL Server uisng the SQLSRV version of PHP Driver for SQL Server:

By default smalldatetime, datetime, date, time, datetime2, and datetimeoffset types will be returned as PHP DateTime objects, but this behaviour can be changed by setting the 'ReturnDatesAsStrings' option in the connection string or at the statement level:

<?php
$server   = "serverinstanse";
$database = "database";
$username = "username";
$password = "password";

$cinfo = array(
   "Database" => $database, 
   "UID" => $username, 
   "PWD" => $password
);
$conn = sqlsrv_connect($server, $cinfo);
if ($conn === false) {
   echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
   exit;
}

$query = "SELECT DateTimeColumn FROM OneTable";
$options = array('ReturnDatesAsStrings' => true);
$stmt = sqlsrv_query($conn, $query, null, $options);
if ($stmt === false) {
   echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
   exit;
}
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
   echo print_r($row, true);
}

sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);
?>

1.3. Retrieve Date and Time values from SQL Server uisng MSSQL PHP extension (note, that this extension was removed in PHP 7.0.0):

When you use MSSQL extension, date and time values are returned as text, but the format depends on mssql.datetimeconvert setting in php.ini file. When this options is ON, date and time values are converted based on SQL server settings, and when is OFF date and time values are converted to YYYY-MM-DD hh:mm:ss format.

<?php
$server   = "serverinstanse";
$database = "database";
$username = "username";
$password = "password";

$conn = mssql_connect($server);
if ($conn === false) {
    echo "Error (mssql_connect): ".mssql_get_last_message();
    exit;
}
mssql_select_db($database, $conn);

$query = "SELECT DateTimeColumn FROM OneTable";
$stmt = mssql_query($sql, $conn);
if ($stmt === false) {
    echo "Error (mssql_query): ".mssql_get_last_message();
    exit;
}

while ($row = mssql_fetch_assoc($stmt)) {
   echo print_r($row, true);
}

mssql_free_result($stmt);
mssql_close($conn);
?>

As an additional note, it seems that this setting is ON in your server environment, because you can send dates like '26-03-2019 00:00:00.000' without errors.

2. How to pass Date and Time values to SQL Server:

As a general rule, date and time values can be passed to SQL Server using unambiguous datetime format (yyyymmdd or yyyy-mm-ddThh:mm:ss) and parameterized statement.

2.1. Pass Date and Time values to SQL Server uisng the PDO_SQLSRV version of PHP Driver for SQL Server:

<?php
$server   = "serverinstanse";
$database = "database";
$username = "username";
$password = "password";

try {
   $conn = new PDO("sqlsrv:server = $server; Database = $database", $username, $password);
   $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch( PDOException $e ) {
   die( "Error connecting to SQL Server".$e->getMessage());
}

try {
   $query = "INSERT INTO OneTable (DateTimeColumn) VALUES (?)";
   $datetime = (new DateTime())->format("Y-m-dTH:i:s");
   $stmt = $conn->prepare($query);
   $stmt->bindParam(1, $datetime);
   $stmt->execute();
} catch( PDOException $e ) {
   die( "Error connecting to SQL Server".$e->getMessage());
}

$stmt = null;
$conn = null;
?>

2.2. Pass Date and Time values to SQL Server uisng the SQLSRV version of PHP Driver for SQL Server:

With this version of the driver, you may use the extended parameters syntax and pass the datetime value as PHP DateTime object with information about the PHP and SQL Server data types of the parameter.

<?php
$server   = "serverinstanse";
$database = "database";
$username = "username";
$password = "password";

$cinfo = array(
   "Database" => $database, 
   "UID" => $username, 
   "PWD" => $password
);
$conn = sqlsrv_connect($server, $cinfo);
if ($conn === false) {
   echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
   exit;
}

$query = "INSERT INTO OneTable (DateTimeColumn) VALUES (?)";
$datetime = new DateTime();
$params = array(
   array($datetime, SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_DATETIME, SQLSRV_SQLTYPE_DATETIME)
); 
// or as usual, pass datetime values as text
//$params = array($datetime->format("Y-m-dTH:i:s")); 
$stmt = sqlsrv_query($conn, $query, $params);
if ($stmt === false) {
   echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
   exit;
}

sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);
?>
Saturday, May 29, 2021
 
ManojGeek
answered 5 Months ago
26

The problem was the wrong permissions for the /etc/phpMyAdmin directory. The web server user, apache, had proper permissions for the session.save_path directory, but apache couldn't read from my config.inc.php file. Changing the owner of /etc/phpMyAdmin to the apache user and changing the permissions to 755 solved the problem.

Looking at the checkConfigSource() function in Config.class.php led me to believe that if the problem was with accessing the configuration file then I would have received the error 'Existing configuration file (%s) is not readable.' instead of Call to undefined function __() Does anyone know why that wasn't the case?

This was a pretty basic problem/solution, but unless someone suggests otherwise I think I'll leave it up since this exact problem/solution isn't addressed in other discussions of the Fatal error: Call to undefined function __() in /usr/share/phpMyAdmin/libraries/core.lib.php error when trying to start phpMyAdmin after installation.

Saturday, July 31, 2021
 
Simmeringc
answered 3 Months ago
82

As far as I am aware there is no 64bit PHP compatible SQL Server library. You will have to install WAMPServer 32bit. This is not a WAMPServer limitation, its just that Microsoft has not yet completed the 64 bit PHP extension.

Probably they were waiting for PHP7 which is the first truly 64bit version of PHP

Thursday, September 2, 2021
 
shx2
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :