Member Avatar for jeffersonalomia

How to insert data in sql server 2012 express using php pdo in wamp server 3? What connection string I need to use to connect in the database?

Edited by jeffersonalomia
  • 4 Contributors
  • 3 Replies
  • 451 Views
  • 10 Months Discussion Span
  • Latest Post Latest Post by pritaeas

Dani AI

Generated

Short summary and the right direction (for ): you should use the Microsoft PDO driver (pdo_sqlsrv), not the MySQL DSN shown by . is correct to call that out, and is also right that XAMPP/WAMP requires extra setup when you want to talk to SQL Server instead of the bundled MySQL. The minimal flow is: install the Microsoft PHP drivers + the Microsoft ODBC client, enable the pdo_sqlsrv extension in php.ini, make sure SQL Server accepts TCP connections (and Browser service is running for named instances), then use a sqlsrv DSN with PDO and prepared statements.

A simple PDO_SQLSRV connection + prepared insert looks like this (adjust server/instance, database and credentials):

<?php
$dsn  = "sqlsrv:Server=localhost\\SQLEXPRESS;Database=MyDatabase";
$user = "appuser";
$pass = "secret";

$pdo = new PDO($dsn, $user, $pass, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

$stmt = $pdo->prepare("INSERT INTO People (FirstName, LastName, Email) VALUES (?, ?, ?)");
$stmt->execute(['Jane', 'Doe', 'jane.doe@example.com']);

The PDO_SQLSRV DSN syntax, named-instance handling and port form (server,port) are documented on the PDO_SQLSRV pages. (php.net)

Practical install and driver notes: use the Microsoft Drivers for PHP for SQL Server (pdo_sqlsrv / sqlsrv) that match your PHP version, thread-safety and architecture. You also need a matching Microsoft ODBC driver installed on Windows; the msphpsql project docs and release notes list supported PHP/ODBC combinations and how to enable the php_pdo_sqlsrv extension in php.ini. Pay attention to ODBC Driver 18’s default encryption behavior (it may require adding Encrypt=Optional or configuring certificates). (github.com)

Quick troubleshooting checklist (most common causes): enable TCP/IP for the SQL instance (SQL Express often has it disabled by default), restart the SQL service, start SQL Server Browser if you connect by instance name (or use server,port), open the SQL port (default 1433) and UDP 1434 in the Windows firewall when appropriate, and confirm you’re using SQL Authentication if Windows auth isn’t configured for the web server. Use SQL Server Management Studio or sqlcmd to verify the server accepts remote/TCP connections before testing from PHP. (learn.microsoft.com)

Quick checklist to finish: pick the matching driver release, drop the pdo_sqlsrv DLL into PHP’s ext folder, add the extension line to php.ini, restart Apache, then validate the connection with a tiny script like the example above.

Recommended Answers

All 3 Replies

Member Avatar for rproffitt

While I would have stuck with MySQL that's in WAMP, it appears that setup for WAMP 3 with MS SQL is a lot more work. Check that you got it all done by reading

commented: Thanks. I would just shift to mysql +3
Member Avatar for anand9796

here,it is 

<?php
$servername = "localhost";
$username = "username";
$password = " ";
$dbname = " ";//your database name

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "INSERT INTO MyGuests (firstname, lastname, email)
    VALUES ('Nevil', 'Roy', 'nevilroy@example.com')";
    $conn->exec($sql);
    echo "Record Inserted successfully";
    }
catch(PDOException $e)
    {
    echo $sql . "<br>" . $e->getMessage();
    }

$conn = null;
?>
Edited by anand9796
Member Avatar for pritaeas

$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);

He asked for Microsoft SQL Server, not MySQL

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.