Jump to content

Sql Database? **New Question Please Read**


Recommended Posts

I am currently writting a website to run from our main database which is a microsoft sql 2000 database.

At the moment I have apache on my machine with mysql DB so I can test queries and how the information is displayed on the page. I have created tables in the mysql database with the same names, column names and data stored within them as the actual main db.

As I have never ran a site querying a microsoft sql db, so will all my code work when I upload the site to run from the main server? Alot of my code is currently using mysql code:-

             #connect to db
$conn = @[i][b]mysql_connect[/b][/i](  "localhost", "username", "password" )
      or die( "Err:conn");
#select db
$rs = @[i][b]mysql_select_db[/b][/i]( "phptest", $conn)
or die( "ERR:Db");

#create query
$sql = "select * from vwStudent where student_id=\"$student_id\" ";

#exe query
$rs = [i][b]mysql_query[/b][/i]( $sql, $conn )
or die( "Could not execute Query");

#retrieve data
while ( $row = [i][b]mysql_fetch_array[/b][/i]( $rs ) )

Am I going to have to change this? If so how and what to?

I have found this code in some asp pages we are currently using, is any of this something I need to consider?


'Dimension variables
Dim Con        'Holds the Database Connection Object
Dim rsServers  'Holds the recordset for the records in the database
Dim strSQL     'Holds the SQL query to query the database

set con=Server.CreateObject("ADODB.Connection")
con.open "PROVIDER=SQLOLEDB;DATA SOURCE=servername;UID=username;PWD=password;DATABASE=dbname "

set rsStu=Server.CreateObject("ADODB.recordset")
strSQL = "SELECT * from Student where student_id='" & request.querystring("student_id") & "'"
rsStu.Open strSQL, Con,3,3

Set objRS = Server.CreateObject("ADODB.Recordset")
strSQL= "select * from ReviewTarg where id='" & id & "'"
objRS.Open strSQL, con, 2, 2

objRS("new_target") = request.form("new_target")

set RS=Server.CreateObject("ADODB.recordset")
RS.Open "tbl_progreviewtarg", Con, 2,3
RS("target_id") = request.form("target_id")
RS("prev_target") = request.form("new_target")

Link to comment
Share on other sites

MS SQL has same functions... literally you just need to replace mysql_<function> with mssql_<function>. The other thing you have to worry about is differences in SQL syntax. For example MySQL uses "select * from table limit [...]", whereas MS SQL uses "select top [...] * from table".
Link to comment
Share on other sites

Nope, you don't need to worry about that.. ASP uses the adodb object to interact with MS SQL. Then, you have to tell adodb which driver/provider to use.. Alternatives to SQLOLEDB are the jet engine to access ms excel or ms access files. That's all asp stuff though.

For PHP, just use the mssql_<function_name> functions to access MS SQL. Literally, for the code you have below, if it works with the mysql_ functions, it will work with the mssql_ functions.

Exceptions: different SQL syntax, column names restricted to 30 characters when you retrieve column name information (through mssql_fetch_field for example)... that's it from what I can remember
Link to comment
Share on other sites

  • 2 weeks later...
I have just changed all the mysql to mssql and its not working ???

Here is all the code from one of my pages to see if anyone can see my mistake.

#delete cookies created if there are any
setcookie("stu_id",$stu_id, time()-60);
setcookie("auth","ok", time()-60);

#call for cookies
$stcode = $_COOKIE['stcode'];
if(!$stcode == "stcode")
{ header("Location:login.php");

  #connect to db
$conn = @mssql_connect(  "server", "username", "password" )
      or die( "Err:conn");
#select db
$rs = @mssql_select_db( "db", $conn)
or die( "ERR:Db");

#create query
$sql = "SELECT distinct stu_id, forename, surname from stud where stcode=\"$stcode\" ";

$rs = mssql_query( $sql, $conn )
or die( "Could not execute query" );

#search for matches
$num = mssql_numrows ($rs);
if ($num !=0)
$list = "<table align=\"center\" border=\"0\" cellpadding=\"2\" width=\"50%\">";
$list .= "<tr>";
$list .= "<th class=table>Learner ID</th>";
$list .= "<th class=table>Learner Name</th>";
$list .= "<th class=table></th>";
$list .= "</tr>";

#retrieve data
while ( $row = mssql_fetch_array( $rs ) )
$list .= "<tr>";
$list .= "<td class=table>".$row["stu_id"]."</td>";
$list .= "<td class=table>".$row["forename"]."  ".$row["surname"]."</td>";
$list .= "<td class=table><br><form action=\"personal_details_setcookies.php\" method=\"post\"><input type=\"hidden\" name=\"stu_id\" value=\"".$row["stu_id"]."\"><input type=\"submit\" value=\"View\"></form></td></tr>";
$list .= "</tr>";
$list .= "</table>";

#list details if matches found or display no matches found msg
echo( "<p><b><br><center>These are your Students</center></b></p>". $list . "<br>" );
{ echo ("<br><center>You do not have any Students</center>");
Search for another Student
<form action="staff_search_setcookies.php" method="post">
<td><p>Please enter the ID</p></td>
<td><input type="text" name="stu_id" size="20"></td>
  <input type="submit" value="Search"></form><br><hr><br>
<form action="staff_search_all.php" method="post">
<td><p>Search all Students that do not have a Staff Code assigned to them</p></td>
<td><input type="submit" value="Search"></td>


What am I doing wrong? Is there some code that mssql doesn't understand??

Many Thanks
Link to comment
Share on other sites

I'm not getting an error message.
Staff enter their staff code on the login page before this one, which when they press submit they are navigated to this page and their staff code is created into a cookie ($stcode) by the previous page.
This page calls the cookie $stcode and uses it to query the db. It's meant to display all the students that have that code against their details.

This was all working on my mock mysql db of the main db. But now I've changed the mysql parts to mssql it doesn't work.

The header part just redirects them to the login page if there is no cookie present. eg if they try and access a page without logging in.

Link to comment
Share on other sites

You shall have to describe what happens that is not what you expect. It's hard to diagnose with "it doesn't work". We're here to help, but can't if we don't have enough info!

Also, it's mssql_num_rows, as opposed to mssql_numrows..

Link to comment
Share on other sites

Sorry I didn't explain it better sometimes it's really hard to explain things like this.
However you have helped me anyway beacause I changed mssql_numrows to mssql_num_rows and it's working great. ;D

Strange that mysql_numrows works in mysql though ???

Anyway cheers mcp for all your help with all my db q's

nice 1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.