Convert sql to sqlsrv code

Question

Post reply

Convert sql to sqlsrv code

allanstarr023

SSC Veteran

Points: 265
More actions
August 14, 2014 at 11:10 am

#294546

Hi All
Can anyone assist in me converting this snippet of code from mysql to ms sql.
I am using the sqlsrv function
$check_for_con_ContractNumber = mysql_query("SELECT con_Id FROM legalcontracts WHERE con_ContractNumber='$con_ContractNumber'");
//Query to check if con_ContractNumber is available or not
if(mysql_num_rows($check_for_con_ContractNumber))
{
echo '1';//If there is a record match in the Database - Not Available
}
else
{
echo '0';//No Record Found - con_ContractNumber is available
}

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

IF EXISTS (SELECT 1 FROM legalcontracts WHERE con_ContractNumber= @con_ContractNumber)

PRINT '1'; --If there is a record match in the Database - Not Available

ELSE

PRINT '0'; --No Record Found - con_ContractNumber is available

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

I've never used it, but is that really what MySQL looks like?

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Andrew Kernodle SSCertifiable Points: 5314 More actions · Answer 3

I could be wrong, but I think that's PHP coding; the mysql_query(...) bit matches one of PHP's functions, which prepares and executes a MySQL query against a target server. I've had to work with PHP pages linked to SQL Server, and the posted code's syntax looks quite similar.

- 😀

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

Thanks Andrew. Just curious.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

allanstarr023 SSC Veteran Points: 265 More actions · Answer 5

Thanks

I will definately try it, because I nearly went crazy trying to figure out the correct syntax...

allanstarr023 SSC Veteran Points: 265 More actions · Answer 6

@LutzM

It works when i run it direct in the sql server however when i try integrating the sqlsrv function to make it work with my php code it does not work...

I cannot figure out what I am doing wrong..

Below is my updated mssql and php code

$check_for_con_ContractNumber = IF EXISTS (SELECT 1 FROM LegalContracts WHERE con_ContractNumber = '$con_ContractNumber');

$result=sqlsrv_query($conn, $check_for_con_ContractNumber);

$count = sqlsrv_num_rows($result);

if($count = 1)

{

echo '1';//If there is a record match in the Database - Not Available

}

else

{

echo '0';//No Record Found - con_ContractNumber is available

}

Andrew Kernodle SSCertifiable Points: 5314 More actions · Answer 7

allanstarr023 (8/15/2014)
@LutzM
It works when i run it direct in the sql server however when i try integrating the sqlsrv function to make it work with my php code it does not work...
I cannot figure out what I am doing wrong..
Below is my updated mssql and php code
$check_for_con_ContractNumber = IF EXISTS (SELECT 1 FROM LegalContracts WHERE con_ContractNumber = '$con_ContractNumber');
$result=sqlsrv_query($conn, $check_for_con_ContractNumber);
$count = sqlsrv_num_rows($result);
if($count = 1)
{
echo '1';//If there is a record match in the Database - Not Available
}
else
{
echo '0';//No Record Found - con_ContractNumber is available
}

I believe your query needs to be enclosed in either single or double quotes.

So, your query should look like so:

$check_for_con_ContractNumber = "IF EXISTS (SELECT 1 FROM LegalContracts WHERE con_ContractNumber = '$con_ContractNumber')";

However, you're also attempting to pass in a PHP variable to the query; as such, you'll need to parameterize your PHP query statement, like so:

$check_for_con_ContractNumber = "IF EXISTS (SELECT 1 FROM LegalContracts WHERE con_ContractNumber = ?)";

$result = sqlsrv_query($conn,$check_for_con_ContractNumber,$con_ContractNumber);

I think that should do it, but it's admittedly been a few months since I last wrote this kind of code. Let us know if this doesn't pan out quite right.

- 😀