Yes, there is. Just rewrite mikosiko's SQL implementation in PHP.junestag wrote:Is there a PHP-only way to parse these dates correctly and get the drop down I want?
Trying to create dynamic sql loop - help!
Moderator: General Moderators
Re: Trying to create dynamic sql loop - help!
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!
Ok, I thought so...just need to think how to do it. You meant this right?
I'll be back when I have tried it out in PHP format..thanks guys.
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;Re: Trying to create dynamic sql loop - help!
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!
OK,junestag wrote:Alright, don't laugh. I've tried to implement this in PHP
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>";
}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!
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):
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?
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-21Re: Trying to create dynamic sql loop - help!
Code: Select all
UPDATE MidCourseSurveys
SET semester='Fall 2009'
WHERE date < '2010-01-21'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
Miko