Page 1 of 3
Trying to create dynamic sql loop - help!
Posted: Fri Feb 19, 2010 12:42 pm
by junestag
Here's the code I came up with to populate a drop-down select box based on the date value in a mysql database...however it's not populating the drop down box with anything...why?
Code: Select all
$option_block1 = "";
$username = $_SESSION['username'];
$counter = 0;
$datearr[0] = 2010-01-21;
$sqlarr[]='';
$resultarr[]='';
$sqleoc = "SELECT * FROM MidCourseSurveys ORDER BY date";
$resultseoc = mysql_query($sqleoc,$connection);
while ($row = mysql_fetch_array($resultseoc, MYSQL_ASSOC)) {
foreach($datearr[$counter] as $value) {
$sqlarr[$counter] = "SELECT DISTINCT course_title, course_dept_num FROM MidCourseSurveys WHERE date < '$value' ORDER BY course_dept_num";
$resultarr[$counter] = mysql_query($sqlarr[$counter],$connection);
while ($row = mysql_fetch_array($resultarr[$counter], MYSQL_ASSOC)) {
$course_title = $row['course_title'];
$course_dept_num = $row['course_dept_num'];
if (substr($value, 5, 2) == '01') {
$semester = "FALL" . (substr($value, 0, 3) - 1);
} elseif (substr($value, 5, 2) == '06') {
$semester = "SPRING" . (substr($value, 0, 3));
} else {
$semester = "SUMMER" . (substr($value, 0, 3));
}
$option_block1 .= "<option value=\"$course_dept_num\">$course_dept_num: $course_title - $semester</option>";
$counter++;
$datearr[1] = 2010-06-01;
}
}
}
Re: Trying to create dynamic sql loop - help!
Posted: Fri Feb 19, 2010 2:07 pm
by califdon
Did you echo out the value in $datearr[0]? I'd bet it is 1988.
Re: Trying to create dynamic sql loop - help!
Posted: Fri Feb 19, 2010 4:33 pm
by junestag
Interesting, I hadn't thought of that (clearly). I'm probably still doing the date thing wrong, but I changed $datearr[0] = "2010-01-21"; --- notice the quotation marks. Now that it's a string will that help me? I tried it and it echoed out correctly of course, but the following code is still not producing anything (even though I tested with echoing out values and it IS connecting to the database):
Code: Select all
$option_block1 = "";
$username = $_SESSION['username'];
$counter = 0;
$datearr[0] = "2010-01-21";
$sqlarr[]='';
$resultarr[]='';
$sqleoc = "SELECT * FROM MidCourseSurveys ORDER BY date";
$resultseoc = mysql_query($sqleoc,$connection);
while ($row = mysql_fetch_array($resultseoc, MYSQL_ASSOC)) {
$date = $row['date'];
foreach($date as $value) {
$sqlarr[$counter] = "SELECT DISTINCT course_title, course_dept_num FROM MidCourseSurveys WHERE date < '$datearr[$counter]' ORDER BY course_dept_num";
$resultarr[$counter] = mysql_query($sqlarr[$counter],$connection);
while ($row = mysql_fetch_array($resultarr[$counter], MYSQL_ASSOC)) {
$course_title = $row['course_title'];
$course_dept_num = $row['course_dept_num'];
$date = $row['date'];
if (substr($date, 5, 2) == '01') {
$semester = "FALL" . (substr($date, 0, 3) - 1);
} elseif (substr($date, 5, 2) == '06') {
$semester = "SPRING" . (substr($date, 0, 3));
} else {
$semester = "SUMMER" . (substr($date, 0, 3));
}
$option_block1 .= "<option value=\"$course_dept_num\">$course_dept_num: $course_title - $semester</option>";
$counter++;
$datearr[1] = "2010-06-01";
}
}
}
Re: Trying to create dynamic sql loop - help!
Posted: Fri Feb 19, 2010 6:24 pm
by mikosiko
where did you define $connection ????
Re: Trying to create dynamic sql loop - help!
Posted: Fri Feb 19, 2010 6:27 pm
by junestag
Oh, that's in an include file earlier in the script that i didn't include.
Re: Trying to create dynamic sql loop - help!
Posted: Fri Feb 19, 2010 6:39 pm
by califdon
It looks like all your SUBSTR() functions are based on the first position in a string being "0", but when I checked the MySQL manual, it says:
"For functions that operate on string positions, the first position is numbered 1. "
Re: Trying to create dynamic sql loop - help!
Posted: Fri Feb 19, 2010 7:01 pm
by junestag
Interesting...however, the $row['date'] value being returned from mysql is actually stored as a date in mysql...does it still become a string once it's in php?
Re: Trying to create dynamic sql loop - help!
Posted: Fri Feb 19, 2010 7:11 pm
by califdon
junestag wrote:Interesting...however, the $row['date'] value being returned from mysql is actually stored as a date in mysql...does it still become a string once it's in php?
Yes. Otherwise, you couldn't parse out the various parts. It's actually not php that does it, MySQL outputs that format by default. Again, the manual says:
"The DATE type is used when you need only a date value, without a time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'."
Re: Trying to create dynamic sql loop - help!
Posted: Fri Feb 19, 2010 7:28 pm
by junestag
Hmmm, I think the problem must be with my logic then.. I will work on this and get back to you Monday.
Re: Trying to create dynamic sql loop - help!
Posted: Sat Feb 20, 2010 8:36 pm
by mikosiko
junestag wrote:Hmmm, I think the problem must be with my logic then.. I will work on this and get back to you Monday.
could you give some example of the data that you have in your table and what exactly are you trying to obtain?
your logic looks very curious to me (no critiquing.. just trying to understand to help if possible), several things caught my attention as p.e:
- Why are you using arrays?
- Why are you doing 2 selects over the same table?
- Why are you using a foreach over what looks to me only one value?
- Your second While also don't seems ok... you are post-incrementing the $counter value inside the while, hence changing the pointer in your second result set "resultarr" ?????
- And last... in your if are you saying that every record in '01' is in 'FALL" semester and every record in "06" is in "SPRING" semester and everything else is in "SUMMER" ???
MIko
Re: Trying to create dynamic sql loop - help!
Posted: Tue Feb 23, 2010 1:11 pm
by junestag
Miko:
The data I'm trying to obtain is survey data. All of the courses have multiple entries so that's why I'm trying to make the sql query return DISTINCT values, so an instructor can just select one option for their course. Instead of listing every single result, the results get tabulated in the processing page that follows this page anyway. However, because we have multiple semesters, we'd like to be able to 'chunk' the survey results by semester instead of just tabulating them all into one results (because instructors change, or the course gets changed, etc. - we'd like to know what students think every semester for each course individually). So it would be IDEAL if the course select box said:
CourseX - Fall 2009
CourseX - Spring 2010
CourseX - Summer 2010
CourseX - Fall 2010
CourseY - Fall 2009
CourseY - Spring 2010
CourseY - Summer2010
etc. you get the picture
Each survey in the mysql db has the following fields: survey_id, date (xxxx-xx-xx), instructor_lname, instructor_fname, course_title, course_dept_num, a bunch of results fields, and then a username field (that uniquely identifies each instructor for their login credentials).
I was using arrays because I thought I would need them to generate an unknown quantity of values...I don't know how many semesters we will ultimately end up tabulating results for - presumably it will never stop unless time stops, haha. So that's why I have the $sqlarr and $resultsarr.
I don't know why im using the foreach and two SELECTS now...that's why I realize my logic must be flawed...i'm going to print this out and rethink how it works today.
The reason I'm saying every record in '01' is Fall is because that is the value at that part of the date string that will always be unique to Fall records and so forth with Spring and Summer semesters.
Any help you can provide on conceptualizing how I might approach this better would be appreciated. Like I said I'm going to be working on this most of today.
Thanks so much!
Re: Trying to create dynamic sql loop - help!
Posted: Tue Feb 23, 2010 8:37 pm
by mikosiko
"The reason I'm saying every record in '01' is Fall is because that is the value at that part of the date string that will always be unique to Fall records and so forth with Spring and Summer semesters."
this still confusing me... in your code you are using substr(field,5,2) which is month right?
if that is correct means that only the records in month =01 are in Fall, the records in month=06 are in Spring and any other month is in Summer?... curious for me.
what I will do instead of you first/second select is:
- Create a View in the database selecting DISTINCT:
Course_Title, "SPRING, SUMMER or FALL" using some function to traduce the month accordingly (elt function or similar) , substr(your date field to get the year).
- In you PHP code just select the already filtered records and create the <option...> field
hope this help
Re: Trying to create dynamic sql loop - help!
Posted: Tue Feb 23, 2010 9:08 pm
by califdon
I'm sure that's because the dates the OP is dealing with are registration dates for college classes. Winter registration is typically in January, etc. Typically, you can't register any other time than a brief period for each of the 3 semesters.
However, as I pointed out earlier, the substring parameters are incorrect, so if the date string is, say, "2010-01-15", then the expression substr(field,5,2) will return "-0", NOT "01". It usually helps to read the manual: "For functions that operate on string positions, the first position is numbered 1. "
Re: Trying to create dynamic sql loop - help!
Posted: Tue Feb 23, 2010 10:25 pm
by mikosiko
califdon wrote:I'm sure that's because the dates the OP is dealing with are registration dates for college classes. Winter registration is typically in January, etc. Typically, you can't register any other time than a brief period for each of the 3 semesters.
Thank you for clarify this for me.
califdon wrote:However, as I pointed out earlier, the substring parameters are incorrect, so if the date string is, say, "2010-01-15", then the expression substr(field,5,2) will return "-0", NOT "01". It usually helps to read the manual: "For functions that operate on string positions, the first position is numbered 1. "
And that confuse to some many people... in MYSQL the function substr() start counting in position 1, en PHP the substr() function start in position 0
PHP :
http://us3.php.net/substr
MYSQL:
http://dev.mysql.com/doc/refman/4.1/en/ ... _substring
Re: Trying to create dynamic sql loop - help!
Posted: Wed Feb 24, 2010 1:11 pm
by califdon
Oops! My mistake! The OP was using the PHP substring function, so the original code was correct. Yes, most languages begin counting string positions (and array indexes) with zero, but SQL begins with 1.