Member Avatar for deucalion0

So I have Microsoft SQL Server 2008 R2 installed and an instance set up with a database an user setup on that database with a password. I have installed PHP, and IIS and got PH working from the WWWROOT folder, but when tryng to connect to my database it is not just not working. I have spent two days straight on this, and pulling my hair out. I was able to connect to the database using ColdFusion and its web server, PHP was meant to be easier! Here is the code i am using to connect to my database:

<?php
mysql_connect("MYINSTANCE", "MYUSER", "MYPASSWORD") or die(mysql_error());
echo "Connected to MySQL<br />";
mysql_select_db("test") or die(mysql_error());
echo "Connected to Database";
?>

Obviously I have omitted my server, user and password from the above code. I have installed the drivers from MS and installed into the PHP EXT folder, still no luck, I will appreciate any help from you guys!

Thanks!!

  • 3 Contributors
  • 8 Replies
  • 297 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Biiim

Dani AI

Generated

A short, focused follow‑up that ties the thread together and shows a safe, working approach.

The root cause in the OP’s code was mixing APIs: the SQL Server connection was made with the Microsoft driver, then the MySQL functions (mysql_*) were used for the INSERT. That combination will not execute SQL Server statements. correctly pointed to the SQLSRV/PDOSQLSRV drivers; ’s note about older mssql* functions is historical but not applicable when the Microsoft SQLSRV drivers are in use. The official drivers provide both the native sqlsrv API and a PDO interface. (learn.microsoft.com)

Example (PDO + parameterized insert — avoids SQL injection and works with the Microsoft PDO driver):

<?php
$dsn = 'sqlsrv:Server=SERVERNAME\\INSTANCE;Database=TutorialTwo';
$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION];

try {
    $pdo = new PDO($dsn, 'dbuser', 'dbpass', $options);
    $stmt = $pdo->prepare(
        'INSERT INTO BRANCH (DIV, DIVNAME, CITY) VALUES (:div, :divname, :city)'
    );
    $stmt->execute([':div'=>'60', ':divname'=>'ADMIN', ':city'=>'DUNDEE']);
    echo $stmt->rowCount() . " row(s) inserted\n";
} catch (PDOException $e) {
    echo "DB error: " . $e->getMessage() . "\n";
}

Use the PDO_SQLSRV or sqlsrv API for execution; sqlsrv_query supports parameter arrays for the same purpose. (learn.microsoft.com)

Quick troubleshooting checklist (common, fast wins)

  • Confirm the correct driver DLLs match PHP’s version/VC build/bitness and are enabled in php.ini; verify with phpinfo().
  • Ensure the Microsoft ODBC driver is installed (drivers require it).
  • Check error output after each DB call: use sqlsrv_errors()/sqlsrvconfigure for sqlsrv or catch PDOException for PDO. That will surface syntax/permission problems the old mysql* calls hid. (documentation.help)
  • After statement execution, check the affected row count with sqlsrv_rows_affected() or PDO’s rowCount()/exec return value to confirm the INSERT actually ran. (documentation.help)

If a connection succeeds but an INSERT doesn’t appear: confirm the correct connection resource/object was used (no $con vs $conn mixups), verify the SQL literals are quoted correctly, and refresh the table view in SSMS (or re-query) after performing the insert.

Recommended Answers

All 8 Replies

Member Avatar for deucalion0

MySQL is not the same as MSSQL. You should not be using the mysql* functions to connect to a microsoft sql server.You want to be using the SQL Server driver (http://msdn.microsoft.com/en-us/sqlserver/ff657782.aspx)

Hi there many thanks for your reply. My mistake, i am using Microsoft SQL server 2008 R2 and not MySQL, I always get confused! ARRGH! The driver you pointed out is in fact the one i downloaded and installed to my PHP/ext directory, I don't know if there was more configuration to do after this, as the tutorial i was following didn't say.

Thanks!

Member Avatar for deucalion0

These might be of help:
http://social.technet.microsoft.com/wiki/contents/articles/accessing-sql-server-databases-from-php.aspx#Using_the_SQLSRV_Driver
http://social.technet.microsoft.com/wiki/contents/articles/accessing-sql-server-databases-from-php.aspx#Using_the_PDO_Driver

Wow that actually helped a lot, I eventually got it to connect! YES!!!

But bit of an anti climax as I was excited to be able to insert into tables, but I cannot get this to work, have you any ideas? I am using this code:

<?php
$serverName = "myinstance";
$connectionInfo = array("UID" => "myuser", "PWD" => "mypassword", "Database"=>"mydatabase");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn === false ) // note the format of ‘equals’
{
     echo "Could not connect.\n";
     die( print_r( sqlsrv_errors(), true));
}

mysql_select_db("TutorialTwo", $con);


mysql_query("INSERT INTO BRANCH (DIV, DIVNAME, CITY)
VALUES ('60', 'ADMIN', DUNDEE')");
mysql_close($con);
?>

I get no errors, but when I refresh the table in ms sql there is no new addition, I wonder what i am doing wrong??

Thanks for all your help!!!!

Member Avatar for mschroeder

You're back to using mysql functions. You can't mix them together.

Member Avatar for Biiim

mysql_select_db() --> mssql_select_db()
mysql_query() --> mssql_query()

Member Avatar for mschroeder


The op is not using the mssql driver, they are using the sqlsrv driver provided my Microsoft for php on windows.


Here is php.net documentation that has examples. My suggestion it to start reading through them to get a better understanding of how the driver is used.
http://us3.php.net/manual/en/book.sqlsrv.php

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.