I've create database connection to mySQL database with PHP. It works fine. Now, I want to change that connection into MS SQL Server Database with the same table from previous MySQL database.
我用PHP创建了与mySQL数据库的数据库连接。它工作正常。现在，我想使用先前MySQL数据库中的相同表将该连接更改为MS SQL Server数据库。
Here is the code:
config.php
这是代码：config.php
<?php
/**
* Database config variables
*/
define("DB_HOST", "localhost");
define("DB_USER", "root");
define("DB_PASSWORD", "");
define("DB_DATABASE", "android_api");
?>
DB_Connect.php
DB_Connect.php
<?php
class DB_Connect {
// constructor
function __construct() {
}
// destructor
function __destruct() {
// $this->close();
}
// Connecting to database
public function connect() {
require_once 'include/config.php';
// connecting to mysql
$con = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
// selecting database
mysql_select_db(DB_DATABASE);
// return database handler
return $con;
}
// Closing database connection
public function close() {
mysql_close();
}
}
?>
DB_Functions.php
DB_Functions.php
<?php
class DB_Functions {
private $db;
//put your code here
// constructor
function __construct() {
require_once 'DB_Connect.php';
// connecting to database
$this->db = new DB_Connect();
$this->db->connect();
}
// destructor
function __destruct() {
}
/**
* Storing new user
* returns user details
*/
public function storeUser($name, $email, $password) {
$uuid = uniqid('', true);
$hash = $this->hashSSHA($password);
$encrypted_password = $hash["encrypted"]; // encrypted password
$salt = $hash["salt"]; // salt
$result = mysql_query("INSERT INTO users(unique_id, name, email, encrypted_password, salt, created_at) VALUES('$uuid', '$name', '$email', '$encrypted_password', '$salt', NOW())");
// check for successful store
if ($result) {
// get user details
$uid = mysql_insert_id(); // last inserted id
$result = mysql_query("SELECT * FROM users WHERE uid = $uid");
// return user details
return mysql_fetch_array($result);
} else {
return false;
}
}
/**
* Get user by email and password
*/
public function getUserByEmailAndPassword($email, $password) {
$result = mysql_query("SELECT * FROM users WHERE email = '$email'") or die(mysql_error());
// check for result
$no_of_rows = mysql_num_rows($result);
if ($no_of_rows > 0) {
$result = mysql_fetch_array($result);
$salt = $result['salt'];
$encrypted_password = $result['encrypted_password'];
$hash = $this->checkhashSSHA($salt, $password);
// check for password equality
if ($encrypted_password == $hash) {
// user authentication details are correct
return $result;
}
} else {
// user not found
return false;
}
}
/**
* Check user is existed or not
*/
public function isUserExisted($email) {
$result = mysql_query("SELECT email from users WHERE email = '$email'");
$no_of_rows = mysql_num_rows($result);
if ($no_of_rows > 0) {
// user existed
return true;
} else {
// user not existed
return false;
}
}
/**
* Encrypting password
* @param password
* returns salt and encrypted password
*/
public function hashSSHA($password) {
$salt = sha1(rand());
$salt = substr($salt, 0, 10);
$encrypted = base64_encode(sha1($password . $salt, true) . $salt);
$hash = array("salt" => $salt, "encrypted" => $encrypted);
return $hash;
}
/**
* Decrypting password
* @param salt, password
* returns hash string
*/
public function checkhashSSHA($salt, $password) {
$hash = base64_encode(sha1($password . $salt, true) . $salt);
return $hash;
}
}
?>
index.php
的index.php
<?php
/**
* File to handle all API requests
* Accepts GET and POST
*
* Each request will be identified by TAG
* Response will be JSON data
/**
* check for POST request
*/
if (isset($_POST['tag']) && $_POST['tag'] != '') {
// get tag
$tag = $_POST['tag'];
// include db handler
require_once 'include/DB_Functions.php';
$db = new DB_Functions();
// response Array
$response = array("tag" => $tag, "success" => 0, "error" => 0);
// check for tag type
if ($tag == 'login') {
// Request type is check Login
$email = $_POST['email'];
$password = $_POST['password'];
// check for user
$user = $db->getUserByEmailAndPassword($email, $password);
if ($user != false) {
// user found
// echo json with success = 1
$response["success"] = 1;
$response["uid"] = $user["unique_id"];
$response["user"]["name"] = $user["name"];
$response["user"]["email"] = $user["email"];
$response["user"]["created_at"] = $user["created_at"];
$response["user"]["updated_at"] = $user["updated_at"];
echo json_encode($response);
} else {
// user not found
// echo json with error = 1
$response["error"] = 1;
$response["error_msg"] = "Incorrect email or password!";
echo json_encode($response);
}
} else if ($tag == 'register') {
// Request type is Register new user
$name = $_POST['name'];
$email = $_POST['email'];
$password = $_POST['password'];
// check if user is already existed
if ($db->isUserExisted($email)) {
// user is already existed - error response
$response["error"] = 2;
$response["error_msg"] = "User already existed";
echo json_encode($response);
} else {
// store user
$user = $db->storeUser($name, $email, $password);
if ($user) {
// user stored successfully
$response["success"] = 1;
$response["uid"] = $user["unique_id"];
$response["user"]["name"] = $user["name"];
$response["user"]["email"] = $user["email"];
$response["user"]["created_at"] = $user["created_at"];
$response["user"]["updated_at"] = $user["updated_at"];
echo json_encode($response);
} else {
// user failed to store
$response["error"] = 1;
$response["error_msg"] = "Error occured in Registartion";
echo json_encode($response);
}
}
} else {
echo "Invalid Request";
}
} else {
echo "Access Denied";
}
?>
1 个解决方案
#1
5
If I were you, I would write two different database classes, looking somewhat like this:
如果我是你，我会写两个不同的数据库类，看起来有点像这样：
interface DBConnection {
public function connect();
public function query($sql);
public function fetch($query);
// etc whatever else you need
}
class MySQLConnection implements DBConnection {
// implement these using all the MySQL funcs
}
class MSSQLConnection implements DBConnection {
// implement these using all the MSSQL funcs
}
And then, don't use mysql_query(), mysql_fetch_array() and the such in DB_Functions, use $db->query() and $db->fetch(), whereas $db contains either an instance of MySQLConnection or MSSQLConnection, depending on which one you want to use. The interface will make sure that you implement all the same functions in both classes.
然后，不要在DB_Functions中使用mysql_query（），mysql_fetch_array（）等，使用$ db-> query（）和$ db-> fetch（），而$ db包含MySQLConnection或MSSQLConnection的实例，具体取决于你想要使用哪一个。该接口将确保您在两个类中实现所有相同的功能。
/edit: Of course, I'm stupid: Tieso T is right, PDO should be used. That could be even more effective...
/编辑：当然，我很愚蠢：Tieso T是对的，应该使用PDO。这可能会更有效......
#1
5
If I were you, I would write two different database classes, looking somewhat like this:
如果我是你，我会写两个不同的数据库类，看起来有点像这样：
interface DBConnection {
public function connect();
public function query($sql);
public function fetch($query);
// etc whatever else you need
}
class MySQLConnection implements DBConnection {
// implement these using all the MySQL funcs
}
class MSSQLConnection implements DBConnection {
// implement these using all the MSSQL funcs
}
And then, don't use mysql_query(), mysql_fetch_array() and the such in DB_Functions, use $db->query() and $db->fetch(), whereas $db contains either an instance of MySQLConnection or MSSQLConnection, depending on which one you want to use. The interface will make sure that you implement all the same functions in both classes.
然后，不要在DB_Functions中使用mysql_query（），mysql_fetch_array（）等，使用$ db-> query（）和$ db-> fetch（），而$ db包含MySQLConnection或MSSQLConnection的实例，具体取决于你想要使用哪一个。该接口将确保您在两个类中实现所有相同的功能。
/edit: Of course, I'm stupid: Tieso T is right, PDO should be used. That could be even more effective...
/编辑：当然，我很愚蠢：Tieso T是对的，应该使用PDO。这可能会更有效......