php - How to turn MySQL strings into SQL Server equivalents
Get the solution ↓↓↓I am currently working on a clients web site and I ran into a bit of a problem. I am using a log in system that was shown to me by a colleague that uses MySQL strings. I plugged the client's info into it and loaded it and got a connection error. After combing through my code I found some errors but fixing them did not fix the connection issue. After digging around on the client's web host I noticed that their database is hosted on a Microsoft SQL server instead of a MySQL server. Now here comes the questions:
1.) How do I change my MySQL strings into SQL Server strings?
2.) Can I still use PHP to manipulate the SQL Server strings?
3.) Are there any good tutorials that you guys recommend that will help me understand this better?
Reference
Here is the MySQL strings I'm using including the lines that are manipulated by PHP:
// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
// Define $myusername and $mypassword
$myusername=$_POST['myusername'];
$mypassword=$_POST['mypassword'];
// To protect MySQL injection (more detail about MySQL injection)
$myusername = stripslashes($myusername);
$mypassword = stripslashes($mypassword);
$myusername = mysql_real_escape_string($myusername);
$mypassword = mysql_real_escape_string($mypassword);
$sql="SELECT * FROM $tbl_name WHERE user_name='$myusername' and user_pswd='$mypassword'";
$result=mysql_query($sql);
// Mysql_num_row is counting table row
$count=mysql_num_rows($result);
// If result matched $myusername and $mypassword, table row must be 1 row
if($count==1){
// Register $myusername, $mypassword and redirect to file "login_success.php"
$_SESSION['myusername'];
$_SESSION['mypassword'];
header("location:Inside.php");
} else {
echo "Wrong Username or Password";
}
Any suggestions would be greatly appreciated. Thanks in advance!
Answer
Solution:
Since the database is no longer MySQL, you will have to rewrite some of the code that uses MySQL functions. This is easily done with PDO (PHP Data Objects) and is far more portable for future changes.
Look at this SQL Server example:
<?php
$user = 'myUsername';
$pass = 'myPassword';
// Connect to mssql database
$conn = new PDO('mssql:host=127.0.0.1; dbname=tempdb;', $user, $pass);
$query = "SELECT * FROM table1";
// Prepare query and run it. This is where you can use prepared statements
// to avoid SQL injection
$sth = $conn->prepare($query);
$sth->execute();
// Fetch the returned db rows and dump them as output
$retRows = $sth->fetchAll();
var_dump($retRows);
// Clean up resources
unset($sth); unset($conn);
?>
Anywhere you find a function likemysql_*
in your code, you will want to lookup the proper way to do that using PDO.
Answer
Solution:
You need to change PHP functions callled for mysql to equivalent Sql Server functions.
mysql_connect()
=>mssql_connect()
mysql_select_db()
=>mssql_select_db()
mysql_num_rows()
=>mssql_num_rows()
Here is a list of all functions for SQL Server. http://php.net/manual/en/book.mssql.php
Answer
Solution:
Some of it are quite easy to translate to SQL Server, but some other things are a bit more tricky.
For an example let's take LIMIT. There is no such thing as LIMIT in SQL Server. And GROUP BY acts so much more different, and a lot of aggregating queries needs to be rewritten.
On the other hand, SQL Server does definitely provide some really nice functions that MySQL does not provide.
If you're struggling with LIMIT, you should read up on TOP or ROW_NUMBER(). And for GROUP BY queries, you should read up on GROUP BY obviously, and combine it with any ranking function, like (ROW_NUMBER(), DENSE_RANK()... etc) with OVER(PARTITION BY col ORDER BY col)...
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: call to a member function format() on string
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.