Copy link to clipboard
Copied
I am doing a query on a table that pulls data like the customers number. Than, on my output on screen, I need to DISPLAY the clients name from the customers number. However, the clients name is inside another table under clientsinfo.
I need the output to look like this: Client Number: 123 (John Smith)
This doesn't work below? Any idea?
<cfquery name="Checkclientnumber" datasource="allsales">
SELECT * FROM clientsales
WHERE clientnumber = '#clientnumber#'
</cfquery>
<cfoutput query ="checkclientnumber">Client Number: #clientnumber#
<cfquery name="client" datasource="clientsinfo">
SELECT lastname, firstname FROM clients
WHERE clientnumber = '#clientnumber#'
</cfquery>
<cfoutput query="client"> ( #firstname# #lastname# )</cfoutput>
</cfoutput>
Copy link to clipboard
Copied
How does it not work? Errors, wrong data, hums out of tune?
Copy link to clipboard
Copied
Other then you can not nest two <cfouput query...> loops like that.
Try
<cfquery name="Checkclientnumber" datasource="allsales">
SELECT * FROM clientsales
WHERE clientnumber = '#clientnumber#'
</cfquery>
<cfoutput>
<cfloop query ="checkclientnumber">Client Number: #clientnumber#
<cfquery name="client" datasource="clientsinfo">
SELECT lastname, firstname FROM clients
WHERE clientnumber = '#clientnumber#'
</cfquery>
<cfloop query="client"> ( #firstname# #lastname# )</cfloop>
</cfloop>
</cfoutput>
But I would really try not to loop over queries like that. It is often a poor solution.
You may want to look into using a IN clause in a single query with the ColdFusion valueList() function, if you can not make use of some type of JOIN clause.
Copy link to clipboard
Copied
I get this error message:
Attribute validation error for tag cfloop. | |
The value of the attribute query, which is currently client, is invalid. | |
The error occurred in xxx\managerview.cfm: line 96 | |
94 : <cfoutput> 95 : 96 : <cfloop query = "client"> 97 : 98 : <cfquery name="client" datasource="data"> | |
I am not sure what to do or the best way of doing this... again, I have two tables - One is a ClientInfo and the Other is Sales. When I run a report, I need to query the list of sales of that day - query the sales and it only shows the client id.. I need to get the client id and convert to the clients name. So, sales for the day will have 50+ transacations, which will show client id plus the clients name.
What do you think?
Copy link to clipboard
Copied
Your specific error is caused by looping through a query before you run it.
On a more general note, you only need a single query for what you are trying to do. If you don't know how to select from more than one table, I've heard good things about the book, Teach Yourself SQL in 10 Minutes by Ben Forta.
Copy link to clipboard
Copied
Thanks Dan. I do have books and in all honesty, the Forums are usually the last place I go because I know
I can figure it out with all of my references. Sometimes, the project at hand is a little more different or difficult as some of the examples in the books. I'll check out that book too. Thanks for your help and everyone else.
Sometimes, you reach a point that you (I) have to ask for help.
Copy link to clipboard
Copied
Try doing it all in one query:
<cfquery name="Checkclientnumber" datasource="allsales">
SELECT clientsales.*,clients.lastname,clients.firstname
FROM clientsales,clients
WHERE clientnumber = '#clientnumber#'
AND clients.clientnumber=clientsales.clientnumber
</cfquery>
I think that your latest problem with the CFLOOP and query="client" is because you are calling it "client" which is a variables scope. Try calling it "clientStuff" or whatever.
-reed
Copy link to clipboard
Copied
Even easier: Just get rid of the nested CFOUTPUT - you don't need it. You're inner CFQUERY is only returning 1 row, so you don't need to loop over it. You're already inside of a CFOUTPUT, so the #xxx# variables will be interpreted correctly.
-reed
Copy link to clipboard
Copied
Thanks for all your help... in the same course I learned something.. The last answer with the tablename.fieldname,tablename.fieldname calls multiple tables in one query.
It did pull the data, but I am trying to figure out why the 2nd call didn't show the users names. It shows the items purchased and users ID, but the lastname,firstname doesn't appear. I am sure I should be able to figure it out.
Copy link to clipboard
Copied
FYI:
The reason (still odd) the lastname,firstname didn't appear on my display is both tables had these fields. The sales tables were blank where the client table had obviously their name. I had to delete the fields - lastname,firstname From the sales table (which is the 1st table) and I did a select * which probably override the client.firstname... etc..
THANK YOU !!! I will keep an eye out to help others too.