Sql Database? **New Question Please Read**

[Round]

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?

[code]<%

'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.movefirst
objRS("new_target") = request.form("new_target")
objRS.Update


set RS=Server.CreateObject("ADODB.recordset")
RS.Open "tbl_progreviewtarg", Con, 2,3
RS.AddNew
RS("target_id") = request.form("target_id")
rs("review_id")=review_id+1
RS("prev_target") = request.form("new_target")
RS.Update
%>[/code]


Thanks

[MCP]

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".

[Round]

Excellent! Thats a relief.

But in the asp code it says our provider is:- SQLOLEDB

Does that mean its something else?

Thanks for your help

[MCP]

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

[Round]

Ok thanks MCP thats great, I can stop worrying now!! (or at least for the time being  :))

Cheers again for all your help

[argoSquirrel]

I just wanted to say:  I write some ASP Classic for work and that code you have is convoluted as hell...

Edit: Not trying to be mean...

[Round]

What the asp code in the code box is convoluted? Because I didn't write that and the site isn't functioning properly, and as I don't understand asp 100% I decided to just re-write it in php.

[Round]

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.

[code]<?php
#delete cookies created if there are any
setcookie("stu_id",$stu_id, time()-60);
setcookie("auth","ok", time()-60);
header("Cache-Control:no-cache");

#call for cookies
$stcode = $_COOKIE['stcode'];
header("Cache-Control:no-cache");
if(!$stcode == "stcode")
{ header("Location:login.php");
exit();
}

  #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>" );
}
else
{ echo ("<br><center>You do not have any Students</center>");
}
?>
Search for another Student
<br>
<form action="staff_search_setcookies.php" method="post">
  <table>
<tr>
<td><p>Please enter the ID</p></td>
<td><input type="text" name="stu_id" size="20"></td>
</tr>
</table>
  <input type="submit" value="Search"></form><br><hr><br>
<form action="staff_search_all.php" method="post">
  <table>
<tr>
<td><p>Search all Students that do not have a Staff Code assigned to them</p></td>
<td><input type="submit" value="Search"></td>
</tr>
</table>
  </form>

</center>[/code]

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

Many Thanks

[MCP]

What's the error message?

What's $stcode ? Is it always equal to "stcode"? It seems as thought that's the case with your login header thing.

[Round]

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.

[MCP]

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..

Cheers
MCP

[Round]

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