• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • 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 SinghSolution ArchitectCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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