beridius

asked on 1/7/2013

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

Last Comment

beridius

8/22/2022 - Mon

Systech Admin

1/7/2013

can you provide the exact error you are getting.

gr8gonzo

1/7/2013

Have you selected the database before you query?

mssql_select_db("SQL01");

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

beridius

1/7/2013

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

1/7/2013

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

1/7/2013

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

beridius

1/7/2013

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

1/8/2013

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

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

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

beridius

1/9/2013

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