Hi guys, can you explain to me how I can combine these statements into one please?
They all query the same table with different criteria so I guess it might be possible?
Thank you
Code:
'find how many passes there are in this section
set passRS=conn.execute("SELECT count(criteria_level) AS passNo FROM z_"&strSchool&"_marks INNER JOIN z_"&strSchool&"_criteria ON marks_criteria_id = criteria_id WHERE criteria_level = 1 AND marks_username = '"&userRS("user_username")&"' AND critieria_section_no = "&strSectionRS)
strPass = passRS("passNo")
passRS.CLOSE()
SET passRS = NOTHING
'How many did student get?
set passTotalRS=conn.execute("SELECT count(criteria_level) AS passNo FROM z_"&strSchool&"_criteria WHERE criteria_level = 1 AND critieria_section_no = "&strSectionRS)
strTotalPass = passTotalRS("passNo")
passTotalRS.CLOSE
SET passTotalRS = NOTHING
'find how many merits there are in this section
set meritRS=conn.execute("SELECT count(criteria_level) AS meritNo FROM z_"&strSchool&"_marks INNER JOIN z_"&strSchool&"_criteria ON marks_criteria_id = criteria_id WHERE criteria_level = 2 AND marks_username = '"&userRS("user_username")&"' AND critieria_section_no = "&strSectionRS)
strMerit = meritRS("meritNo")
meritRS.CLOSE
SET meritRS = NOTHING
'How many did student get?
set meritTotalRS=conn.execute("SELECT count(criteria_level) AS meritNo FROM z_"&strSchool&"_criteria WHERE criteria_level = 2 AND critieria_section_no = "&strSectionRS)
strTotalMerit = meritTotalRS("meritNo")
meritTotalRS.CLOSE
SET meritTotalRS = NOTHING
'find how many distinctions there are in this section
set distinctRS=conn.execute("SELECT count(criteria_level) AS distinctNo FROM z_"&strSchool&"_marks INNER JOIN z_"&strSchool&"_criteria ON marks_criteria_id = criteria_id WHERE criteria_level = 3 AND marks_username = '"&userRS("user_username")&"' AND critieria_section_no = "&strSectionRS)
strDistinct = distinctRS("distinctNo")
distinctRS.CLOSE
SET distinctRS = NOTHING
'How many did student get?
set distinctTotalRS=conn.execute("SELECT count(criteria_level) AS distinctNo FROM z_"&strSchool&"_criteria WHERE criteria_level = 3 AND critieria_section_no = "&strSectionRS)
strTotalDistinct = distinctTotalRS("distinctNo")
distinctTotalRS.CLOSE
SET distinctTotalRS = NOTHING
Probably still want to do two queries, since there are two really very separate cases.
But your DB appears badly designed. Why would you *EVER* create separate tables for different schools? Why not one table for all schools with just a field to say *which* school? If there were some kind of security question involved, it might make sense, except *THEN* you should be using separate databases with separate logins/passwords for each database. It makes no sense at all to have separate tables in a single database from any perspective I can think of.
Anyway:
There are two basic ways: Get all 3 criteria in one record or get 3 records, one with each criteria level.
So... first, all 3 in one record:
Code:
marksSQL = "SELECT SUM(IF(criteria_level=1,1,0)) AS passNo, " _
& " SUM(IF(criteria_level,2,1,0) AS meritNo, " _
& " SUM(IF(criteria_level,2,1,0) AS distinctNo " _
& " FROM z_"&strSchool&"_marks, z_"&strSchool&"_criteria " _
& " WHERE marks_criteria_id = criteria_id " _
& " AND marks_username = '"&userRS("user_username")&"' " _
& " AND critieria_section_no = "&strSectionRS
Set RS = conn.Execute( marksSql )
passNo = RS("passNo")
meritNo = RS("meritNo")
distnctNo = RS("distincNo")
RS.Close
marksSQL = "SELECT SUM(IF(criteria_level=1,1,0)) AS passNo, " _
& " SUM(IF(criteria_level,2,1,0) AS meritNo, " _
& " SUM(IF(criteria_level,2,1,0) AS distinctNo " _
& " FROM z_"&strSchool&"_criteria " _
& " WHERE critieria_section_no = "&strSectionRS
Set RS = conn.Execute( marksSql )
totalPassNo = RS("passNo")
totalMeritNo = RS("meritNo")
totalDistnctNo = RS("distincNo")
RS.Close
(I just could *NOT* bring myself to code that as strPassNo = RS("passNo") when *CLEARLY* the value of strPassNo would *NOT* be as string! It *WILL* be a number. A count (or sum) *MUST* be a number? Why would you ever named the variable str when it holds a number?
(Of course, I happen to be one of those who think that "Hungarian Notation" is an abomination. I have never opted to use prefixes on my variables such as "str" and "int" and "obj". Why MicroSLOP ever started that I will never know, but it's nice to know that even the Slop Shop has finally abandoned them, for the most part.)
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
marksSQL = "SELECT criteria_level, COUNT(*) AS criNo " _
& " FROM z_"&strSchool&"_marks, z_"&strSchool&"_criteria " _
& " WHERE marks_criteria_id = criteria_id " _
& " AND marks_username = '"&userRS("user_username")&"' " _
& " AND critieria_section_no = "&strSectionRS _
& " GROUP BY criteria_level ORDER BY criteria_level
Set RS = conn.Execute( marksSql )
passNo = RS("criNo")
RS.MoveNext
meritNo = RS("criNo")
RS.MoveNext
distnctNo = RS("criNo")
RS.Close
marksSQL = "SELECT criteria_level, COUNT(*) AS criNo " _
& " FROM z_"&strSchool&"_criteria " _
& " WHERE critieria_section_no = "&strSectionRS _
& " GROUP BY criteria_level ORDER BY criteria_level
Set RS = conn.Execute( marksSql )
totalPassNo = RS("criNo")
RS.MoveNext
totalMeritNo = RS("critNo")
RS.MoveNext
totalDistnctNo = RS("criNo")
RS.Close
Ugh...just noticed.
You even have
Code:
& " WHERE critieria_section_no = "&strSectionRS _
and again, strSectionRS is clearly a number. So why the "str" prefix?
If you are going to insist on using ugly Hungarian Notation, you should at least use it correctly: If a variable holds a number, it should never have a str prefix.
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Thank you - I think I understand what you have done and will experiment with your methods.
And WOW - no holds barred on the corrections - I will take them on board too, why 'Hungarian'? I am self taught (ouch!) and happily take these tips on board.
I just assumed you had been reading MS recommended practices from 10 or more years ago. MS was very strongly into the so-called Hungarian Notation until .NET came along, at which time they eased up quite a bit.
I've implemented your code, it has HALVED page load time - thank you very much!
Can you recommend a site I can look at this level of tutorials (or dare I say a book I could read...?) I can use to get my knowledge up please?
I actually came up with the SUM(IF(...),1,0) trick on my own maybe 12 years ago, when I was using Access (Access uses IIF instead of IF, but otherwise the same). Only to then find out a year or so later that it's a standard technique in SQL Server (where you have to use CASE WHEN instead off IIF, but concepts are the same). Anyway, when I started using MySQL heavily (starting in 2009) it was an easy transition.
MySQL has some impressively powerful features. Partitioned tables are a wonderful example. I used them to increase the performance of removing old records by a factor of maybe 100 or more. But it also has some strange limitiations. Its use of indexes, for example, is really really poor compared to SQL Server. (SQL Server will do index-only JOINs to get the number of records fetched way way down before it actually has to go get the records, per se. MySQL won't do that, at all. It's a huge limitation on performance.)
Anyway, what it comes down to is: practice, practice, practice. I actually *PLAY* with MySQL almost every day. (Well, I play with HTML and JavaScript every day, too, come to think of it.) That is, I purposely try things that I don't actually need as part of my work for that day, just to see if they work better. Then when I have to actually implement something "on the job", I have more weapons in my coding arsenal to choose from.
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Before you post, read our: 
