• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 385
  • Last Modified:

php mssql connection

I have a connection to MSSQL database which I use mssql_query and mssql_fetch_assoc

I have problem when I use a join in the statement.
when just connecting to a single table in the SQL part
SELECT        id, sName, iStatus
FROM            [SQL01].[dbo].[KPIDay]

Open in new window


If i do not put the [sqldatabase].[dbo].[tablename]
I get an internal error

but I am stuck with is statement  work in sql builder but no in php

SELECT        Lead_T.sName AS Expr1, LeadSource_T.sName, TransferClient_T.sName AS Client, TransferSubscription_T.sName AS Subs, LeadTransfer_T.zXUpdtdWhn AS Date, 
                         LeadTransfer_T.zXUpdtdBy AS [Updated By], TransferClient_T.id
FROM            TransferClient_T INNER JOIN
                         LeadSource_T INNER JOIN
                         LeadTransfer_T ON LeadSource_T.id = LeadTransfer_T.FKiSourceID INNER JOIN
                         Lead_T ON LeadTransfer_T.FKiLeadID = Lead_T.id ON TransferClient_T.id = LeadTransfer_T.FKiClientID INNER JOIN
                         TransferSubscription_T ON LeadTransfer_T.FKiSubscriptionID = TransferSubscription_T.id
WHERE        (LeadTransfer_T.zXUpdtdWhn BETWEEN CONVERT(DATETIME, '2012-10-29 00:00:00', 102) AND CONVERT(DATETIME, '2012-10-29 23:59:59', 102)) AND 
                         (TransferClient_T.id = 27)
ORDER BY TransferClient_T.zXUpdtdWhn, Date, LeadSource_T.sName DESC

Open in new window

not sure where I need the [sqldatabase].[dbo].[tablename]
0
beridius
Asked:
beridius
2 Solutions
 
Gaurav SinghConsultantCommented:
can you provide the exact error you are getting.
0
 
gr8gonzoConsultantCommented:
Have you selected the database before you query?

mssql_select_db("SQL01");

$results = mssql_query("SELECT id, sName, iStatus FROM KPIDay");
0
 
beridiusAuthor Commented:
I dont have a mssql_select_db I put that in now and come back to you
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
beridiusAuthor Commented:
I just get a blank screen no errors but when I try and echo a column I dont get anything
$query_Recordset1 = "SELECT        Lead_T.sName AS Expr1, LeadSource_T.sName, TransferClient_T.sName AS Client, TransferSubscription_T.sName AS Subs, LeadTransfer_T.zXUpdtdWhn AS Date, 
                         LeadTransfer_T.zXUpdtdBy AS [Updated By], TransferClient_T.id
FROM            TransferClient_T INNER JOIN
                         LeadSource_T INNER JOIN
                         LeadTransfer_T ON LeadSource_T.id = LeadTransfer_T.FKiSourceID INNER JOIN
                         Lead_T ON LeadTransfer_T.FKiLeadID = Lead_T.id ON TransferClient_T.id = LeadTransfer_T.FKiClientID INNER JOIN
                         TransferSubscription_T ON LeadTransfer_T.FKiSubscriptionID = TransferSubscription_T.id
WHERE        (LeadTransfer_T.zXUpdtdWhn BETWEEN CONVERT(DATETIME, '2012-10-29 00:00:00', 102) AND CONVERT(DATETIME, '2012-10-29 23:59:59', 102)) AND 
                         (TransferClient_T.id = 27)
ORDER BY TransferClient_T.zXUpdtdWhn, Date, LeadSource_T.sName DESC
";
mssql_select_db('LM',$conn);
$Recordset1 = mssql_query($query_Recordset1) or die();
$row_Recordset1 = mssql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mssql_num_rows($Recordset1);

Open in new window

not sure where I am going wrong
0
 
gr8gonzoConsultantCommented:
Add something inside die(), like die("Problem querying!");
0
 
beridiusAuthor Commented:
I put print print var_dump(mssql_get_last_message());


and get
string(39) "Invalid object name 'TransferClient_T'."
0
 
rinfoCommented:
I think this is wrong

FROM            TransferClient_T
INNER JOIN   LeadSource_T   /*(no mention of connection field) */
INNER JOIN   LeadTransfer_T ON LeadSource_T.id = LeadTransfer_T.FKiSourceID
INNER JOIN   Lead_T ON LeadTransfer_T.FKiLeadID = Lead_T.id ON TransferClient_T.id = Transfer_T.FKiClientID
INNER JOIN    TransferSubscription_T ON LeadTransfer_T.FKiSubscriptionID = TransferSubscription_T.id
But that should not generate the error message you have generated.
At the same it has been often mentioned that mssql_get_last_message is not a reliable and
indicative of the reason for failing query/operation.
0
 
gr8gonzoConsultantCommented:
Is the TransferClient_T table not inside the database you selected, or is it owned by a different user? You can use owner.tablename to specify if need be.
0
 
beridiusAuthor Commented:
I have just checked the connection to that table I put [databasename].[dbo].[tablename] and its working now thanks guys for all your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now