Avatar of beridius
beridius
 asked on

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]
Microsoft SQL ServerPHPSQL

Avatar of undefined
Last Comment
beridius

8/22/2022 - Mon
Systech Admin

can you provide the exact error you are getting.
gr8gonzo

Have you selected the database before you query?

mssql_select_db("SQL01");

$results = mssql_query("SELECT id, sName, iStatus FROM KPIDay");
beridius

ASKER
I dont have a mssql_select_db I put that in now and come back to you
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
beridius

ASKER
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
gr8gonzo

Add something inside die(), like die("Problem querying!");
beridius

ASKER
I put print print var_dump(mssql_get_last_message());


and get
string(39) "Invalid object name 'TransferClient_T'."
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
rinfo

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
beridius

ASKER
I have just checked the connection to that table I put [databasename].[dbo].[tablename] and its working now thanks guys for all your help