Trying to create dynamic sql loop - help!

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

junestag
Forum Commoner
Posts: 39
Joined: Tue Sep 18, 2007 5:14 pm
Location: Alaska
Contact:

Trying to create dynamic sql loop - help!

Post 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;
}
}
}
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Trying to create dynamic sql loop - help!

Post by califdon »

Code: Select all

$datearr[0] = 2010-01-21;
Did you echo out the value in $datearr[0]? I'd bet it is 1988.
junestag
Forum Commoner
Posts: 39
Joined: Tue Sep 18, 2007 5:14 pm
Location: Alaska
Contact:

Re: Trying to create dynamic sql loop - help!

Post 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";
}
}
}
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Trying to create dynamic sql loop - help!

Post by mikosiko »

where did you define $connection ????
junestag
Forum Commoner
Posts: 39
Joined: Tue Sep 18, 2007 5:14 pm
Location: Alaska
Contact:

Re: Trying to create dynamic sql loop - help!

Post by junestag »

Oh, that's in an include file earlier in the script that i didn't include.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Trying to create dynamic sql loop - help!

Post 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. "
junestag
Forum Commoner
Posts: 39
Joined: Tue Sep 18, 2007 5:14 pm
Location: Alaska
Contact:

Re: Trying to create dynamic sql loop - help!

Post 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?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Trying to create dynamic sql loop - help!

Post 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'."
junestag
Forum Commoner
Posts: 39
Joined: Tue Sep 18, 2007 5:14 pm
Location: Alaska
Contact:

Re: Trying to create dynamic sql loop - help!

Post by junestag »

Hmmm, I think the problem must be with my logic then.. I will work on this and get back to you Monday.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Trying to create dynamic sql loop - help!

Post 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
junestag
Forum Commoner
Posts: 39
Joined: Tue Sep 18, 2007 5:14 pm
Location: Alaska
Contact:

Re: Trying to create dynamic sql loop - help!

Post 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!
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Trying to create dynamic sql loop - help!

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Trying to create dynamic sql loop - help!

Post 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. "
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Trying to create dynamic sql loop - help!

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Trying to create dynamic sql loop - help!

Post 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.
Post Reply