Trying to create dynamic sql loop - help!
Moderator: General Moderators
Re: Trying to create dynamic sql loop - help!
Miko: I think you are on the right track, sorry for explaining what I need to do so poorly.
Could I write something like this?
SELECT Distinct course_title, course_dept_num ELT((date < 2010-01-21), 'FALL 2009')) FROM MCS;
If this is the case I'm not sure how the FALL 2009 value is stored in relation to the other data in the record and how do I pull that out and display it in the option block?
You'll notice it's not so important I capture an exact date, just that I LUMP all records of surveys taken BEFORE a certain date and AFTER a certain date into groups and THEN make a DISTINCT call on the data to return distinct course titles for that date range with the appropriate caption next to the course title in the option block saying FALL 2009, or SPRING 2010, or whatever the semester happens to be based on a DATE RANGE from the mysql database (because students can take the survey on any day of the year).
Does this make more sense?
Could I write something like this?
SELECT Distinct course_title, course_dept_num ELT((date < 2010-01-21), 'FALL 2009')) FROM MCS;
If this is the case I'm not sure how the FALL 2009 value is stored in relation to the other data in the record and how do I pull that out and display it in the option block?
You'll notice it's not so important I capture an exact date, just that I LUMP all records of surveys taken BEFORE a certain date and AFTER a certain date into groups and THEN make a DISTINCT call on the data to return distinct course titles for that date range with the appropriate caption next to the course title in the option block saying FALL 2009, or SPRING 2010, or whatever the semester happens to be based on a DATE RANGE from the mysql database (because students can take the survey on any day of the year).
Does this make more sense?
Re: Trying to create dynamic sql loop - help!
I must admit, I learned something new today: I had never known the ELT() function in MySQL. But I don't think it's appropriate for this use.
Now we're getting into the core of your application and I can't go any further without understanding your data structure. If you would provide the schema of your database in substantially this format, I could try to offer some help:
Also, can you confirm that the data in your "date" field always reflects the relation that month "01" relates to "Winter", month "06" relates to "Spring", and any other month refers to "Summer"? And always WILL? This is hugely important. In case there is anything "soft" about this relationship, what do you want to happen when something occurs to change these relationships? Such as a record with a "date" of "2010-02-01" or "2010-05-31" or "0000-00-00"?
Now we're getting into the core of your application and I can't go any further without understanding your data structure. If you would provide the schema of your database in substantially this format, I could try to offer some help:
Code: Select all
[b]MidCourseSurveys[/b]:
MCS_ID INT (Autoincrement)
course_title VARCHAR
course_dept_num VARCHAR
date DATE
...Re: Trying to create dynamic sql loop - help!
califdon: No problem, here ya go:
MidCourseSurveys:
survey_id int auto_increment
course_title var_char
course_dept_num var_char
date date (default: 0000-00-00)
username var_char
question1 var_char
question2 var_char
etc.
Now keep in mind there are only 3 dates I need to test against. Example: 2009-01-21 is the demarcation point for SPRING 2010 semester, any survey that is in the database with a date less than that value is a FALL 2009 semester course. 2010-06-01 is the demarcation point for SUMMER 2010 semester, and 2010-09-01 is the demarcation point for FALL 2010 semester, and so on until infinity. But those three dates are the demarcation points every year and do not change. Surveys however can be submitted at any time by the user and in fact there are a range of submitted survey dates with accompanying values in the db now.
What do you think?
MidCourseSurveys:
survey_id int auto_increment
course_title var_char
course_dept_num var_char
date date (default: 0000-00-00)
username var_char
question1 var_char
question2 var_char
etc.
Now keep in mind there are only 3 dates I need to test against. Example: 2009-01-21 is the demarcation point for SPRING 2010 semester, any survey that is in the database with a date less than that value is a FALL 2009 semester course. 2010-06-01 is the demarcation point for SUMMER 2010 semester, and 2010-09-01 is the demarcation point for FALL 2010 semester, and so on until infinity. But those three dates are the demarcation points every year and do not change. Surveys however can be submitted at any time by the user and in fact there are a range of submitted survey dates with accompanying values in the db now.
What do you think?
Re: Trying to create dynamic sql loop - help!
Me thinking that my original question and doubts about the date classification in semesters has been answered now... I was right!!junestag wrote:califdon: No problem, here ya go:
MidCourseSurveys:
survey_id int auto_increment
course_title var_char
course_dept_num var_char
date date (default: 0000-00-00)
username var_char
question1 var_char
question2 var_char
etc.
Now keep in mind there are only 3 dates I need to test against. Example: 2009-01-21 is the demarcation point for SPRING 2010 semester, any survey that is in the database with a date less than that value is a FALL 2009 semester course. 2010-06-01 is the demarcation point for SUMMER 2010 semester, and 2010-09-01 is the demarcation point for FALL 2010 semester, and so on until infinity. But those three dates are the demarcation points every year and do not change. Surveys however can be submitted at any time by the user and in fact there are a range of submitted survey dates with accompanying values in the db now.
What do you think?
I'm sure that with a little imagination, creativity and ingenuity and with the help of ELT, IF and probably the CASE function you can do the right select... but... in your case I'll select a different and in the long term easy and helpful solution... my first alternative shall be:
- Create an additional field in you table that store the semester... varchar with a value per example "SUMMER-2009"... value that will be calculated... how?
- Creating 2 triggers associated to the TABLE (BEFORE INSERT AND BEFORE UPDATE) and write the logic to determine the value of the "SEMESTER" field based on the vale of the "date" field.
- after that... any select that need to deal with semester classification will be already solved .
but that is only my solution.... your mileage could be different
Miko
Re: Trying to create dynamic sql loop - help!
Mikosiko's approach may be the right one. I had given that same concept some thought, too. It is slightly denormalizing the data, but there can be a good argument for doing so in this kind of case. Put the calculated data (the year/semester) in the record, to begin with. That does simplify later queries. If you do use a query, though, I would not recommend using the ELT function. If you read the manual closely, that's not what it does, it only returns one of several strings, depending on a raw sequence value, which doesn't seem to fit your needs.
I haven't replied today because I was testing a MySQL table with similar fields to see what kind of query would deliver what you want. I got pretty close, using Union queries, along these lines:
As it happens, the above contains an error, but just before that, I had a version that worked, except that it only returned Spring and Summer data at that point. But my sense of it is that, with a little work, it would return what you want more efficiently than using Group By's and Distinct. If I can spare some time over the next few days, I may be able to refine the above.
I haven't replied today because I was testing a MySQL table with similar fields to see what kind of query would deliver what you want. I got pretty close, using Union queries, along these lines:
Code: Select all
SELECT course, semester, yr
FROM
(SELECT course, year(surveydate) as yr, 'SPRING' as semester FROM namephone
WHERE (month(surveydate) = 1 AND day(surveydate) > 20) OR
(month(surveydate) IN (2, 3, 4, 5)) OR
(month(surveydate) = 6 AND day(surveydate) < 16)) as spring
UNION ALL SELECT course, semester
FROM
(SELECT course, year(surveydate) as yr, 'SUMMER' as semester
FROM namephone
WHERE (month(surveydate) = 6 AND day(surveydate) > 15) OR
(month(surveydate) IN (7,8))) as summer
UNION ALL SELECT course, semester
FROM
(SELECT course, year(surveydate) as yr, 'FALL' as semester
FROM namephone
WHERE (month(surveydate) IN (9, 10, 11, 12)) OR
(month(surveydate) = 1 AND day(surveydate) < 20)) as fallRe: Trying to create dynamic sql loop - help!
as I said... ELT or IF or CASE + imagination 
here something to test ... I did some quick test... seems to be good
replace the fields/table name with your and try
Miko
here something to test ... I did some quick test... seems to be good
Code: Select all
select tbname, tbdate,
(CASE WHEN tbdate BETWEEN concat(year(tbdate)-1,'-09-01') AND concat(year(tbdate),'-01-20') THEN concat('FALL-',year(tbdate))
WHEN tbdate BETWEEN concat(year(tbdate),'-01-20') AND concat(year(tbdate),'-06-01') THEN concat('SPRING-',year(tbdate))
WHEN tbdate BETWEEN concat(year(tbdate),'-06-01') AND concat(year(tbdate),'-09-01') THEN concat('SUMMER-',year(tbdate))
WHEN tbdate BETWEEN concat(year(tbdate),'-09-01') AND concat(year(tbdate)+1,'-01-20') THEN concat('FALL-',year(tbdate))
ELSE 'SOMETHING-WRONG' END) from tbtest;Miko
Re: Trying to create dynamic sql loop - help!
Miko, that looks good. Is this something you're saying I should run in MySQL or do in PHP?
Re: Trying to create dynamic sql loop - help!
That select should gave you the records (courses and the assigned semester)... please adjust it with your table and fields name and test it in all the scenarios (in Mysql Query)... I did some test but I'm not sure if I covered all the cases (specially the border lines).junestag wrote:Miko, that looks good. Is this something you're saying I should run in MySQL or do in PHP?
If the select work you have 2 options:
- Use that query and incorporate it in you original code (review your code first.. it has several details).
- Or ... as I did suggest before go with the long term y better solution creating "semester" field in your table and 2 associated triggers... in those triggers you can use the CASE portion of the select to populate that field properly.
One last advice... I did notice that in you table you have a field named "date" as DATE type... please change the name of that field for something else... "date" is a reserved word in many languages and soner or later it is going to cause problems to you if you are not carefull with your code.
hope now you will be in the right track.
Miko
Re: Trying to create dynamic sql loop - help!
Maybe:
[sql]... ELT(MONTH(`tbdate`), 'WINTER', 'WINTER', 'SPRING', 'SPRING', 'SPRING', 'SUMMER', ...., 'WINTER') ...[/sql]
Though, I'd strongly advice you NOT to use SQL as an output formatting tool. Use PHP instead.
[sql]... ELT(MONTH(`tbdate`), 'WINTER', 'WINTER', 'SPRING', 'SPRING', 'SPRING', 'SUMMER', ...., 'WINTER') ...[/sql]
Though, I'd strongly advice you NOT to use SQL as an output formatting tool. Use PHP instead.
There are 10 types of people in this world, those who understand binary and those who don't
Re: Trying to create dynamic sql loop - help!
VladSun wrote:Maybe:
[sql]... ELT(MONTH(`tbdate`), 'WINTER', 'WINTER', 'SPRING', 'SPRING', 'SPRING', 'SUMMER', ...., 'WINTER') ...[/sql]
Though, I'd strongly advice you NOT to use SQL as an output formatting tool. Use PHP instead.
that doesn't work.... read previous posts... "Semester" is not based only in the value of Month... is based on range limits.
And I will like to read the backup information about your strongly advice... maybe I can learn something new.. thank you
Miko
Re: Trying to create dynamic sql loop - help!
Formatting is in the presentation layer and it should stay there.mikosiko wrote:And I will like to read the backup information about your strongly advice... maybe I can learn something new.. thank youVladSun wrote:Though, I'd strongly advice you NOT to use SQL as an output formatting tool. Use PHP instead.
Miko
In this particular case junestag has decided to have an HTML SELECT element that will display:
Code: Select all
CourseX - Fall 2009
CourseX - Spring 2010
CourseX - Summer 2010
CourseX - Fall 2010
CourseY - Fall 2009
CourseY - Spring 2010
CourseY - Summer 2010Code: Select all
CourseX - Fall'09
CourseX - Spring'10
CourseX - Summer'10
CourseX - Fall'10
CourseY - Fall'09
CourseY - Spring'10
CourseY - Summer'10So, you'll rewrite your SQL queries every time the format has been changed? What about having Excel exports that have 3 columns: course, semester, year - you will write yet another SQL query? And what if a PDF export needs the exact dates and not semesters? Etc...
Use SQL to provide raw data - don't use it for output formatting purposes. Don't use it as a simple calculator also (a kind of formatting too).
Last edited by VladSun on Fri Feb 26, 2010 6:07 pm, edited 1 time in total.
There are 10 types of people in this world, those who understand binary and those who don't
Re: Trying to create dynamic sql loop - help!
Miko: alright, i like your idea of just creating a trigger. Any idea how to do that inside phpmyadmin?
Re: Trying to create dynamic sql loop - help!
Do you really need a `date` column in your MidCourseSurveys table? Or a simple `semester` column should be enough?
I'd never denormalize a DB table just to have some easy output formatting ...
I'd never denormalize a DB table just to have some easy output formatting ...
There are 10 types of people in this world, those who understand binary and those who don't
Re: Trying to create dynamic sql loop - help!
Vlad: you're probably right, i should just include a semester column inside the survey itself as a hidden variable...but alas it's too late for that as all the surveys are being dumped into the same database from last semester and this semester too.
I'm interested to know more about what kind of solution you could help me with that fixes the problem I'm having with this code. Is there a PHP-only way to parse these dates correctly and get the drop down I want?
thanks,
sage
I'm interested to know more about what kind of solution you could help me with that fixes the problem I'm having with this code. Is there a PHP-only way to parse these dates correctly and get the drop down I want?
thanks,
sage
Re: Trying to create dynamic sql loop - help!
I understand your point.. thank you