cfoutput shows 0 instead of correct query data

Report · Oct 09, 2012

On my testing server, everything works fine. Exact same code and data. On the live server, ColdFusion displays 0 instead of correct values. Please see below.

RESULTS on my computer:

Division 1

TAHS
Xxxxxxx, Daniel
Xxxxx@gmail.com
Paid: 45.00
Division: 1

Division 2

SCHS
Xxxxxx, Deborah
Xxxxx@gmail.com
Paid: 45.00
Division: 2

Division 3

DHS
Xxxxxx, Steve

Xxxxx@gmail.com

Paid: 45.00
Division: 3

WDHS
Xxxxxx, Val
Xxxxx@gmail.com
Paid: 50.00
Division: 3

Results on Live Server:

Division 1

TAHS

Xxxxxxx, Daniel

Xxxxx@gmail.com

Paid: 45.00

Division: 0

Division 0

SCHS

Xxxxxxx, Deborah

Xxxxx@gmail.com

Paid: 45.00

Division: 0

DHS

Xxxxxx, Steve

Xxxxx@gmail.com

Paid: 45.00

Division: 0

WDHS
Xxxxxx, Val
Xxxxx@gmail.com
Paid: 50.00
Division: 0

My Query:

SELECT confirmed, academic_team_id, last_name, first_name, email, paid, division ,name

FROM academic_teams, coaches, schools

WHERE academic_teams.coach_id=coaches.coach_id

AND academic_teams.school_id=schools.school_id

ORDER BY division, name

</cfquery>

My CFOUTPUT:

<div style="font-weight:bold; background-color:##CCCCCC; width:300px;">Division #division#</div>

#last_name#, #first_name#<br />

#email#<br />

Paid: #paid# <br />

Division: #division#<br /><br />

</cfoutput>

My DATA

"confirmed"	"academic_team_id"	"last_name"	"first_name"	"email"	"paid"	"division"	"name"
"0"	"3"	"Xxxxxx"	"Daniel"	"Xxxxx@gmail.com"	"45.00"	"1"	"TAHS"
"1"	"4"	"Xxxxxx"	"Deborah"	"Xxxxx@gmail.com"	"45.00"	"2"	"SCHS"
"0"	"5"	"Xxxxxx"	"Steve"	"Xxxxx@gmail.com"	"45.00"	"3"	"DHS"
"0"	"6"	"Xxxxxx"	"Val"	"Xxxxx@gmail.com"	"50.00"	"3"	"WDHS"

Any help would be greatly appreciated.

Report · Oct 09, 2012

When you run your query against the live database without using ColdFusion, do you get the correct number of rows?

Report · Oct 09, 2012

I suspect your DSN on the live box is not pointing to where you think it is.

--

Adam

Report · Oct 09, 2012

Adam Cameron. wrote:
I suspect your DSN on the live box is not pointing to where you think it is.
--
Adam

It's correct. I use third party hosting. I only know one way in. This code previously worked on the live server. Could a mySQL or ColdFusion upgrade affect it?

Report · Oct 09, 2012

It's correct. I use third party hosting. I only know one way in. This code previously worked on the live server. Could a mySQL or ColdFusion upgrade affect it?

Well... in that case... I have no idea WTH is going on 😞

--

Adam

Report · Oct 09, 2012

Adam Cameron. wrote:
Well... in that case... I have no idea WTH is going on 😞

Bummer. Thanks for trying.

Report · Oct 09, 2012

Well... in that case... I have no idea WTH is going on 😞
Bummer. Thanks for trying.

Can you perhaps try a different MySQL driver than the default CF one? That's a longshot though.

And if no-one else has any ideas but you do work out what's going on, can you post back here?

--

Adam

Report · Oct 09, 2012

Dan Bracuk wrote:
When you run your query against the live database without using ColdFusion, do you get the correct number of rows?

Yes. The My DATA is the MySQL result.

Report · Oct 09, 2012

If the data is there when you run the query outside of coldfusion, what happens is you simply dump the recordcount or something when you run it inside coldfusion? Do you get 0 rows, or the expected number?

Report · Oct 09, 2012

Dan Bracuk wrote:
If the data is there when you run the query outside of coldfusion, what happens is you simply dump the recordcount or something when you run it inside coldfusion? Do you get 0 rows, or the expected number?

Dan, please forgive - either I'm not understanding you or I'm not explaining my problem well enough.

The rows are all returning, but the value of the field "division" is showing as 0 for 3 of the four rows. In reality, none of the four rows have a value of 0 in the division field. I'm grouping the results by division.

Thanks

Report · Oct 09, 2012

Earlier you said, "The My DATA is the MySql result." in response to, "what happens when you run the query outside of ColdFusion". Later you say, "In reality, none of the four rows have a value of 0 in the division field".

Those two statements appear to contradict each other.

Report · Oct 10, 2012

Dan Bracuk wrote:
Earlier you said, "The My DATA is the MySql result." in response to, "what happens when you run the query outside of ColdFusion". Later you say, "In reality, none of the four rows have a value of 0 in the division field".
Those two statements appear to contradict each other.

Dan, Sorry, I am still not following. There is a table up above right under the phrase My Data. The 7th column in the table is "Division". The values of each row are below and none of the values in the Division column are "0". When ColdFusion outputs the query, it displays the number zero for #division# for every row except those where the value of "Division" is "1".

I've had my web host working on this today and they don't seem to have any ideas yet either. I'll let you know if I figure it out.

Report · Oct 11, 2012

Resolution!

I'm not sure what the site was running on before, but I think CF8. My web host has now moved the site to Windows 2008 R2 server with ColdFusion 9, which has resolved the issue.

Thanks!