vbcoach Posted March 8, 2015 Share Posted March 8, 2015 I could use your assistance. In my sports website I have a set of leagues, some that are active, some that are not (yet) active. (these inactive leagues are usually sub-sets, but still want to be able to view these) In my LEAGUES table I have nite, type, size, division, venue, and regactive that I pull information from. From these leagues, I have a query that calls all the leagues (filtered by venue) and then a PHP statement that creates a pulldown menu. What I would like to do is separate, or have a BREAK that lists first the ACTIVE leagues (regactive) then continue the column with the inactive leagues. Basically, === ACTIVE LEAGUES === League A League B League C === INACTIVE LEAGUES === League A1 League BB League C1 League C2 League C3 Now in past, about a decade ago, a friend of mine wrote the following statement to make this happen: <?php $na_found = false; //that's very old code while ($lrow = mssqlfetchassoc($lres)) { if(!$lrow['regactive'] && !$na_found) { echo "<option value =''>--- Non Active Leagues ---</option>"; $na_found = true; } ?> <option value="<?php echo $lrow['l_id'];?>" <?php if($l == $lrow['l_id']) echo "selected"; ?>><?php echo "$lrow[night] $lrow[type] $lrow[size]s $lrow[division]"; ?></option> <?php } ?> </select> <input name="Submit" type="submit" class="button" value="View"> <br> </form></td> <td width="637" valign="top"><br> <br><br> <?php if($_POST) { ?> The query is here: $lsql = "SELECT * FROM league where [session] = 'Spring' "; $lres = mssqlquery($lsql); $x = mssqlrowsaffectedX($lres); Now at the moment, this works for the first 5 lines, then stops. Then the rest of the leagues, active or not, is listed. The “regactive” column is a simple BIT, using 1 or 0, with 1 being active. I cannot figure out, for the life of me, how to re-write this statement to work as I listed above. ANY help would be greatly appreciated! Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted March 8, 2015 Share Posted March 8, 2015 your query should retrieve the rows in the order that you want them, with the active first, followed by the non-active - ORDER BY regactive DESC in your select list, you should use <optgroup label="--- Active Leagues ---"></optgroup> and <optgroup label="--- Non Active Leagues ---"></optgroup> tags around the appropriate list of <option></option> tags. you would control the outputting of the opening/closing optgroup tags based on detecting a change in the active/non-active bit in the data as you are looping over it. if you are always going to have at least one active league, you can unconditionally start the active optgroup, output the option list while the active bit is a 1, then when the active bit changes to a zero, close the first optgroup and start the second. at the end, after the loop, close the optgroup tag. Quote Link to comment Share on other sites More sharing options...
vbcoach Posted March 8, 2015 Author Share Posted March 8, 2015 That's what I thought, but this is not quite true. You see, I have leagues running 7 nights a week, What your suggestion does is rank the active/inactive by night. So I would have Sunday ACTIVE, league 1, league 2, league 3, then League C1 and C2 as inactive. Then it goes to Monday, Tuesday, etc.. and does the same thing. So perhaps I have to ORDER BY nite, regactive DESC Let me try that. Problem is, when I start adding tournaments, this will again screw everything up. Quote Link to comment Share on other sites More sharing options...
vbcoach Posted March 8, 2015 Author Share Posted March 8, 2015 As I suspected, not working. Quote Link to comment Share on other sites More sharing options...
vbcoach Posted March 8, 2015 Author Share Posted March 8, 2015 your query should retrieve the rows in the order that you want them, with the active first, followed by the non-active - ORDER BY regactive DESC in your select list, you should use <optgroup label="--- Active Leagues ---"></optgroup> and <optgroup label="--- Non Active Leagues ---"></optgroup> tags around the appropriate list of <option></option> tags. you would control the outputting of the opening/closing optgroup tags based on detecting a change in the active/non-active bit in the data as you are looping over it. if you are always going to have at least one active league, you can unconditionally start the active optgroup, output the option list while the active bit is a 1, then when the active bit changes to a zero, close the first optgroup and start the second. at the end, after the loop, close the optgroup tag. In my example, where would you put these OptGroup tags? Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted March 9, 2015 Share Posted March 9, 2015 assuming you always want the Active Leagues optgroup, even if it is empty, and you only want the Non Active leagues one when there are non active records - echo "<select name='whatever you are using'>"; echo "<optgroup label='--- Active Leagues ---'>"; // start with the active optgroup, even if it is empty $last_val = 1; // detect when this changes from active/1 to non active while ($lrow = mssqlfetchassoc($lres)) { // detect a change in regactive from the initial active/1 value if($last_val != $lrow['regactive']) { // value changed, to a zero, since there are only two possible values // close the previous section (even if empty) and start the next section echo "</optgroup>"; echo "<optgroup label='--- Non Active Leagues ---'>"; $last_val = $lrow['regactive']; // remember the new value so that this code only runs once } // output the data within each optgroup echo "<option value={$lrow['l_id']} ", ($l == $lrow['l_id'] ? "selected" : '') , ">{$lrow['night']} {$lrow['type']} {$lrow['size']}s {$lrow['division']}</option>"; } echo "</optgroup>"; // close the last optgroup echo "</select>"; Quote Link to comment Share on other sites More sharing options...
vbcoach Posted March 15, 2015 Author Share Posted March 15, 2015 Sorry for the lengthy delay. This did not work.... as intended. What is happening is, the list works - - - until it finds it's first change (regactive=0) Then EVERYTHING else in the list is not listed as non-active. (see pictures) Here is how I would like to see the dropdown list: === Active Leagues === Sun Coed 6 A Sun Coed 6 BB Sun Coed 6 B Sun Coed 6 C Mon Men 2 Open Mon Men 2 A Mon Men 2 B Mon Women 2 Open Mon Women 2 A Mon Women 2 B Tue Coed 4 AA Tue Coed 4 A Tue Coed 4 BB Tue Coed 4 B Wed Coed 6 A Wed Coed 6 BB Wed Coed 6 B Wed Coed 6 C Thu Men 4 A Thu Men 4 BB Thu Men 4 B Thu Women 4 A Thu Women 4 BB Thu Women 4 B Fri Coed 2 A Fri Coed 2 BB Fri Coed 2 B Fri Juniors 4 All* Sat Coed 4 A Sat Coed 4 BB Sat Coed 4 B Sat Coed 6 BB Sat Coed 6 B Sat Coed 6 C === Non-Active Leagues === Mon Men 2 BB Mon Women 2 BB Tue Coed 4 A1 Tue Coed 4 A2 Tue Coed 4 BB1 Tue Coed 4 BB2 Tue Coed 4 B1 Tue Coed 4 B2 Wed Coed 6 B1 Wed Coed 6 B2 Wed Coed 6 C1 Wed Coed 6 C2 Wed Coed 6 C3 Thu Women 4 BB1 ASI Coed 6 ASICS Cha Coed 6 CC How do we fix this? Leagues list looks like this: Sun Coed 6 A 1 Sun Coed 6 BB 1 Sun Coed 6 B 1 Sun Coed 6 C 1 Mon Men 2 Open 1 Mon Men 2 A 1 Mon Men 2 BB 0 Mon Men 2 B 1 Mon Women 2 Open 1 Mon Women 2 A 1 Mon Women 2 BB 0 Mon Women 2 B 1 Tue Coed 4 AA 1 Tue Coed 4 A 1 Tue Coed 4 A1 0 Tue Coed 4 A2 0 Tue Coed 4 BB 1 Tue Coed 4 BB1 0 Tue Coed 4 BB2 0 Tue Coed 4 B 1 Tue Coed 4 B1 0 Tue Coed 4 B2 0 Wed Coed 6 A 1 Wed Coed 6 BB 1 Wed Coed 6 B 1 Wed Coed 6 B1 0 Wed Coed 6 B2 0 Wed Coed 6 C 1 Wed Coed 6 C1 0 Wed Coed 6 C2 0 Wed Coed 6 C3 0 Thu Men 4 A 1 Thu Men 4 BB 1 Thu Men 4 B 1 Thu Women 4 A 1 Thu Women 4 BB 1 Thu Women 4 BB1 0 Thu Women 4 B 1 Fri Coed 2 A 1 Fri Coed 2 BB 1 Fri Coed 2 B 1 Fri Juniors 4 All* 1 Sat Coed 4 A 1 Sat Coed 4 BB 1 Sat Coed 4 B 1 Sat Coed 6 BB 1 Sat Coed 6 B1 Sat Coed 6 C1 ASI Coed 6 ASICS 0 Cha Coed 6 CC 0 Any ideas? Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted March 15, 2015 Share Posted March 15, 2015 your sql query would order the rows in the result set by active first, followed by non-active - your query should retrieve the rows in the order that you want them, with the active first, followed by the non-active - ORDER BY regactive DESC Quote Link to comment Share on other sites More sharing options...
vbcoach Posted March 15, 2015 Author Share Posted March 15, 2015 Tried that - that doesn't work either. Quote Link to comment Share on other sites More sharing options...
Barand Posted March 15, 2015 Share Posted March 15, 2015 And without a day number in your data the sort order will be Fri, Mon, Sat, Sun, Thu, Tue, Wed Quote Link to comment Share on other sites More sharing options...
Solution vbcoach Posted March 15, 2015 Author Solution Share Posted March 15, 2015 Ok, got it to work this time Thanks mac_gyver! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.