Page 3 of 3

Re: Trying to create dynamic sql loop - help!

Posted: Fri Feb 26, 2010 4:28 am
by VladSun
junestag wrote:Is there a PHP-only way to parse these dates correctly and get the drop down I want?
Yes, there is. Just rewrite mikosiko's SQL implementation in PHP.

Re: Trying to create dynamic sql loop - help!

Posted: Fri Feb 26, 2010 11:00 am
by junestag
Ok, I thought so...just need to think how to do it. You meant this right?

Code: Select all

  1. select tbname, tbdate,        
   2.        (CASE WHEN tbdate BETWEEN concat(year(tbdate)-1,'-09-01') AND concat(year(tbdate),'-01-20')  THEN concat('FALL-',year(tbdate))
   3.              WHEN tbdate BETWEEN concat(year(tbdate),'-01-20') AND concat(year(tbdate),'-06-01')  THEN concat('SPRING-',year(tbdate))
   4.              WHEN tbdate BETWEEN concat(year(tbdate),'-06-01') AND concat(year(tbdate),'-09-01')  THEN concat('SUMMER-',year(tbdate))
   5.              WHEN tbdate BETWEEN concat(year(tbdate),'-09-01') AND concat(year(tbdate)+1,'-01-20')  THEN concat('FALL-',year(tbdate))
   6.              ELSE 'SOMETHING-WRONG' END) from tbtest;
I'll be back when I have tried it out in PHP format..thanks guys.

Re: Trying to create dynamic sql loop - help!

Posted: Fri Feb 26, 2010 12:26 pm
by junestag
Alright, don't laugh. I've tried to implement this in PHP and my output is a blank page. I checked the syntax and it looks fine to me, but of course I've never used concat() before within a SQL select statement in PHP, please take a look at my code:

Code: Select all

include("connection.php");
 
$option_block1 = "";
 
$username = $_SESSION['username'];
 
$sqleoc = "SELECT course_title, course_dept_num, date" switch(date) { case 0: "WHEN date BETWEEN concat(year(date)-1, '-09-01')) AND concat(year(date), '-01-20')) THEN concat('FALL - ' year(date))"; case 1: "WHEN date BETWEEN concat(year(date), '-01-20')) AND concat(year(date), '-06-01')) THEN concat('SPRING - ' year(date))" "FROM MidCourseSurveys ORDER BY date"; case 2: "WHEN date BETWEEN concat(year(date), '-06-01')) AND concat(year(date), '-09-01')) THEN concat('SUMMER - ' year(date))"; } "FROM MidCourseSurveys WHERE username='$username' ORDER BY course_dept_title";
$resultseoc = mysql_query($sqleoc,$connection);
while ($row = mysql_fetch_array($resultseoc, MYSQL_ASSOC)) {
    $course_title = $row['course_title'];
    $course_dept_num = $row['course_dept_num'];
    $option_block1 .= "<option value=\"$course_dept_num\">$course_dept_num; $course_title</option>";
    }

Re: Trying to create dynamic sql loop - help!

Posted: Fri Feb 26, 2010 5:12 pm
by VladSun
junestag wrote:Alright, don't laugh. I've tried to implement this in PHP
OK, :rofl:

You need something like this

Code: Select all

$sqleoc = "SELECT course_title, course_dept_num, date FROM MidCourseSurveys WHERE username='$username' ORDER BY course_dept_title";
$resultseoc = mysql_query($sqleoc,$connection);
while ($row = mysql_fetch_array($resultseoc, MYSQL_ASSOC)) 
{
     $course_title = $row['course_title'];
     $course_dept_num = $row['course_dept_num'];
     $course_date = date_parse($row['date']);
     $course_semester = .... //use $course_date here
     $option_block1 .= "<option value=\"$course_dept_num\">$course_dept_num; $course_title</option>";
}

Re: Trying to create dynamic sql loop - help!

Posted: Fri Feb 26, 2010 6:13 pm
by junestag
Alright, this is driving me crazy. It still doesn't work because it doesn't return DISTINCT values and so ends up showing the course_title for every time a survey was entered for every course which makes a pretty long list of the same things.

I'm thinking instead of just adding a semester field in mysql and then updating that field using the following syntax (which is not working):

Code: Select all

UPDATE MidCourseSurveys
SET semester='Fall 2009'
WHERE date < 2010-01-21
Can someone explain to me how to test a date stored in date format in mysql to determine whether other dates in that same field are greater or less than the date you supply in the WHERE clause?

Re: Trying to create dynamic sql loop - help!

Posted: Fri Feb 26, 2010 9:03 pm
by mikosiko
8O :? no laughing... yet

Code: Select all

UPDATE MidCourseSurveys
SET semester='Fall 2009'
WHERE date < '2010-01-21'
BUT: Do NOT run that sentence otherwise you are going to have a very different result off what do you want.... p.e.: if you have a record with date='2008-01-25' with that update it will classified in 'FALL 2009'... is that what you want?

Please allow me to give you a very respectful advise... read all the information that you are been given here... read it 4 more times... analyze it, and more important... understand it, and only then try to decide what you need to do with your code... at this point, you (and the rest of us) are going in circles and no even solving basic issues (as the update that you show)... my apologizes if this could sound rude to you... is not my intention... You can solve it... just need to study the solution and understand and know a little better the tools that you have available.

If you want to implement the "semester" field then you have to work first in update your records according to your own definition (depicted in the select that I did provide before... the CASE part of it), for that you will need to write and execute a Mysql function or procedure (you must write it and use the case that was provide), then you will need to implement a way to determine and store the right "semester" value for each new or modified record (here the triggers suggested should be your solution)... only after that you will be able to write your php code to select what you want.

I know this could sound long and complicated, but is not.

I remember a sentence... "Give a fish to a hungry person and you will have a hungry person the next day... teach that person how to fish and you will have a happy friend for life"..... Moral: I don't want give you a fish :wink:

Miko