Convert PHP MySql Connection Script to PHP ODBC Connection Script

Hi EE,

I've been spending days trying to convert the below PHP MySql script into an ODBC connection script to insert details into a ms sql database.

I've tried the odbc_prepare statements but i've been doing it for so long, it is all starting to look like hieroglyphics.

can someone help me please


<?php

header('Content-type: application/json');
if($_POST) {
	$username   	= $_POST['username'];
	$password   	= $_POST['password'];
	$c_password 	= $_POST['c_password'];
	$gender		 	= $_POST['gender'];

	if($_POST['username']) {
		if ( $password == $c_password ) {

			$db_name                = '********';
			$db_user                  = '********';
			$db_password         = '********';
			$server_url               = '********';

			$mysqli = new mysqli($server_url, $db_user, $db_password, $db_name);

			/* check connection */
			if (mysqli_connect_errno()) {
				error_log("Connect failed: " . mysqli_connect_error());
				echo '{"success":0,"error_message":"' . mysqli_connect_error() . '"}';
			} else {
				$stmt = $mysqli->prepare("INSERT INTO users (username, password, gender) VALUES (?, ?, ?)");
				$password = md5($password);
				$stmt->bind_param('sss', $username, $password, $gender);

				/* execute prepared statement */
				$stmt->execute();

				if ($stmt->error) {error_log("Error: " . $stmt->error); }

				$success = $stmt->affected_rows;

				/* close statement and connection */
				$stmt->close();

				/* close connection */
				$mysqli->close();
				error_log("Success: $success");

				if ($success > 0) {
					error_log("User '$username' created.");
					echo '{"success":1}';
				} else {
					echo '{"success":0,"error_message":"Username Exist."}';
				}
			}
		} else {
			echo '{"success":0,"error_message":"Passwords does not match."}';
		}
	} else {
		echo '{"success":0,"error_message":"Invalid Username."}';
	}
}else {
	echo '{"success":0,"error_message":"Invalid Data."}';
}
?>

Open in new window

LVL 7
Ross TurnerManagement Information Support AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kanti PrasadCommented:
Hi

Please change your code accordingly

<?php



//set connection parameters


$user = " *********";

$password = "******";

$database_name = "******";

$hostname = "localhost";

//setting the msyql driver along with dbname and host

$dsn = "DRIVER={MySQL ODBC 3.51 Driver};Server=$hostname;Database=$database_name";

//connect to the database

$conn = odbc_connect($dsn, $user, $password);

//insert some rows
$rs = odbc_exec($conn, 'INSERT INTO `mytest` (`name`, `job`) VALUES  ("Roy","senator"), ("Bill","engineer");');
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
I use php with sql server 2012.    I use PDO for everything.   Also, do you  have the ms sql drivers in your php.ini?
Ross TurnerManagement Information Support AnalystAuthor Commented:
Hi Scott,

i'm using php 5.4 installed via Web Platforms on IIS 8 on window server 2012 and on a seperate server i'm using sql server 2012.

This is my ExtensionList

extension=php_mysql.dll
extension=php_mysqli.dll
extension=php_mbstring.dll
extension=php_gd2.dll
extension=php_gettext.dll
extension=php_curl.dll
extension=php_exif.dll
extension=php_xmlrpc.dll
extension=php_openssl.dll
extension=php_soap.dll
extension=php_pdo_mysql.dll
extension=php_pdo_sqlite.dll
extension=php_imap.dll
extension=php_tidy.dll
extension=php_ldap.dll
extension=php_pdo_sqlsrv.dll
extension=php_sqlsrv.dll

PDO?
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Ross TurnerManagement Information Support AnalystAuthor Commented:
HI Scott,

I've now added
extension=php_sqlsrv_54_ts.dll
extension=php_pdo_sqlsrv_54_ts.dll

And tested it connecting to my DB with the standard scripts under
http://php.net/manual/en/function.sqlsrv-connect.php

Does this make is possible to construct the same principle MySqli Script into PDO SQL ?
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
That looks right.

$db_host = "localhost"; 
$db_name = "dbname";       
$db_user = "db_username";
$db_word = "db_pass";
   
   
   $stmt = $db->prepare('INSERT INTO users (username, password, gender)

    VALUES(:username,:amount,:password,:gender)');
    $stmt->bindValue(':amount', $amount, PDO::PARAM_STR);
    $stmt->bindValue(':password',  $password,  PDO::PARAM_STR);
    $stmt->bindValue(':gender',    $gender,  PDO::PARAM_STR);
    
    $stmt->execute();
    /*
    //for error checking
    $arr = $stmt->errorInfo();
    print_r($arr);

    die;
   */
    
     $stmt = NULL;

Open in new window

Ray PaseurCommented:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ross TurnerManagement Information Support AnalystAuthor Commented:
I had to do some serious studying but managed to finally get this scenario to work, thank you for your advice and help on this matter.

Cheers

Ross
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.