FifeBirder

Blank value in mySQL , no Reset -- Can you explain ?

mjdamato thanks alot for that more elegant solution. I am still digesting it but do get the jist of it. One little problem i have with the drop down box now is that if i select the firt option it does not work, all others do. Any ideas ? regards

mjdamato Working alot better, stable drop downs, but what i am finding is that the drop down box is not showing all selections, it is as if it is missing every second one if that makes sense. The full Alertz_VIP.php is :- <?php /*************************************************************************** * Alertz.php * ------------------- * ***************************************************************************/ define('IN_ICYPHOENIX', true); if (!defined('IP_ROOT_PATH')) define('IP_ROOT_PATH', './'); if (!defined('PHP_EXT')) define('PHP_EXT', substr(strrchr(__FILE__, '.'), 1)); // Include files include(IP_ROOT_PATH . 'common.' . PHP_EXT); include_once(IP_ROOT_PATH . 'includes/functions_groups.' . PHP_EXT); include(IP_ROOT_PATH . '/alerts/alertsconfig.'.PHP_EXT); // Page Authorise $cms_page_id = 'scotalertz'; $cms_page_nav = (!empty($cms_config_layouts[$cms_page_id]['page_nav']) ? true : false); $cms_global_blocks = (!empty($cms_config_layouts[$cms_page_id]['global_blocks']) ? true : false); $cms_auth_level = (isset($cms_config_layouts[$cms_page_id]['view']) ? $cms_config_layouts[$cms_page_id]['view'] : AUTH_ALL); check_page_auth($cms_page_id, $cms_auth_level); // Obtain Select Criteria $temp_region_filter = $_GET["REGION_FILTER"]; $temp_species_filter = $_GET["SPECIES_FILTER"]; $temp_date_filter = $_GET["DATE_FILTER"]; // Filter characters if required $region_filter =str_replace(" & ", "&", $temp_region_filter); $species_filter = $temp_species_filter; $date_filter = $temp_date_filter; // standard session management $userdata = session_pagestart($user_ip); // Check to see if user is logged in if ((!$userdata['session_logged_in']) ) // No he isnt { redirect(append_sid(LOGIN_MG . '?redirect=alerts.' . PHP_EXT)); } else // Yes they are { init_userprefs($userdata); // set page title $page_title = "ScotBird alerts - VIP's ONLY !! "; // standard page header include(IP_ROOT_PATH . 'includes/page_header.'.PHP_EXT); // Connect to the database $db = new sql_db($alerts_mysql_host,$alerts_mysql_username,$alerts_mysql_password,$alerts_mysql_db,false); if(!$db) { die("Database Connection Failed:- Please Contact Site Admin" . mysql_error()); } $template->set_filenames(array('body' => 'alertz_VIP.tpl')); // Some consistant Variables $year =date("Y"); $month = date("m"); $day = date("d"); function datedropdown_funct($switchstring) { $year =date("Y"); $month = date("m"); $day = date("d"); switch ($switchstring) { case "Today": $day = date("d")-1; $retval = '<option value="">Select</option><option value="Today" SELECTED>Today</option><option value="Last 48hrs">Last 48hrs</option><option value="Last week">Last Week</option><option value="last month">Last Month</option>'; break; case "Last 48hrs": $day = date("d")-2; $retval = '<option value="">Select</option><option value="Today">Today</option><option value="Last 48hrs" SELECTED>Last 48hrs</option><option value="Last week">Last Week</option><option value="last month">Last Month</option>'; break; case "Last week": $day = date("d")-8; $retval = '<option value="">Select</option><option value="Today">Today</option><option value="Last 48hrs">Last 48hrs</option><option value="Last week" SELECTED>Last Week</option><option value="last month">Last Month</option>'; break; case "last month": $month = date("m")-2; $retval = '<option value="">Select</option><option value="Today">Today</option><option value="Last 48hrs">Last 48hrs</option><option value="Last week">Last Week</option><option value="last month" SELECTED>Last Month</option>'; break; case "last 3 months": $month = date("m")-4; $retval = '<option value="">Select</option><option value="Today">Today</option><option value="Last 48hrs">Last 48hrs</option><option value="Last week">Last Week</option><option value="last month">Last Month</option><option value="last 3 months" SELECTED>Last 3 Month</option>'; break; default: // Default is the last 48hrs $day = date("d")-1; } $thestartdatecalc = $year . '-' .$month . '-' . $day ; echo $thestartdate; return array($retval, $thestartdatecalc); } $theenddate = $year . '-' .$month . '-' . $day ; // Check if the Date is Set if(isset($date_filter) and strlen($date_filter) > 0) { // The date is Set echo "Date is Set :- " . $date_filter . " - "; // Check to see if Region is set if(isset($region_filter) and strlen($region_filter) > 0) { // The region is set echo "Region is Set :- " . $region_filter . " - "; // Check to see if species is set if(isset($species_filter) and strlen($species_filter) > 0) { // The species is set echo "Species is Set :- " . $species_filter . " - "; list($arr1, $arr2) = datedropdown_funct($date_filter); $date_filter_options= $arr1; $thestartdate.= $arr2; $sql2 = "SELECT * FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' AND region = '".$region_filter ."' AND species = '".$species_filter ."' ORDER BY Date DESC, time DESC "; $sql3 = "SELECT DISTINCT species FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' AND region = '". $region_filter . "' GROUP BY species"; $sql = "SELECT DISTINCT region FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' AND species = '".$species_filter ."' GROUP BY region"; } else { // The species is Not Set echo "Species is NOT set - "; list($arr1, $arr2) = datedropdown_funct($date_filter); $date_filter_options= $arr1; $thestartdate.= $arr2; $sql2 = "SELECT * FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' AND region = '".$region_filter ."' ORDER BY Date DESC, time DESC "; $sql3 = "SELECT DISTINCT species FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' AND region = '". $region_filter . "' GROUP BY species"; $sql = "SELECT DISTINCT region FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY region"; } } else { // The region is not set echo "Region is NOT set - "; // Check to see if species is set if(isset($species_filter) and strlen($species_filter) > 0) { // The species is set echo "Species is Set :- " . $species_filter . " - "; list($arr1, $arr2) = datedropdown_funct($date_filter); $date_filter_options= $arr1; $thestartdate.= $arr2; $sql2 = "SELECT * FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' AND species = '".$species_filter ."' ORDER BY Date DESC, time DESC "; $sql3 = "SELECT DISTINCT species FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY species"; $sql = "SELECT DISTINCT region FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' AND species = '".$species_filter ."' GROUP BY region"; } else { // The species is NOT Set echo "Species is NOT set - "; list($arr1, $arr2) = datedropdown_funct($date_filter); $date_filter_options= $arr1; $thestartdate.= $arr2; $sql2 = "SELECT * FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' ORDER BY Date DESC, time DESC "; $sql3 = "SELECT DISTINCT species FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY species"; $sql = "SELECT DISTINCT region FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY region"; } } } else { // The date is not set $date_filter_options = '<option value="">Select</option><option value="Today">Today</option><option value="Last 48hrs">Last 48hrs</option><option value="Last week">Last Week</option><option value="last month">Last Month</option>'; echo "Date is NOT set - "; // Check to see if Region is set if(isset($region_filter) and strlen($region_filter) > 0) { // The region is set echo "Region is Set :- " . $region_filter . " - "; // Check to see if species is set if(isset($species_filter) and strlen($species_filter) > 0) { // The species is Set echo "Species is Set :- " . $species_filter . " - "; } else { // The species is not set echo "Species is NOT set - "; } } else { // The region is Set echo "Region is NOT set - "; // Check to see if species is set if(isset($species_filter) and strlen($species_filter) > 0) { // The species is Set echo "Species is Set :- " . $species_filter . " - " ; // Build the SQL strings $sql2 = "SELECT * FROM alerts WHERE species ='".$species_filter ."' ORDER BY Date DESC, time DESC "; $sql = "SELECT DISTINCT region FROM alerts GROUP BY region"; $sql3 = "SELECT DISTINCT species FROM alerts WHERE species ='".$species_filter ."' GROUP BY species"; // } else { // The species is not set echo "Species is NOT set - "; // We will only display 5 days worth of Data $year =date("Y"); $month = date("m"); $day = date("d")-5; $thestartdate = $year . '-' .$month . '-' . $day ; // Build the SQL strings $sql2 = "SELECT * FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' ORDER BY Date DESC, time DESC "; $sql3 = "SELECT DISTINCT species FROM alerts GROUP BY species"; $sql = "SELECT DISTINCT region FROM alerts GROUP BY region"; } } } $result2 = $db->sql_query($sql2); if (!($result2 = $db->sql_query($sql2))) { die("Database Query Failed" . mysql_error()); } $i = 1; while ( $row2 = $db->sql_fetchrow($result2) ) { $template->assign_block_vars('alerts', array( 'POS' => $i , 'REGION' => str_replace("&", " & ", $row2['region']), 'SPECIES' => str_replace("%", "'",$row2['species']), 'DATE' => $row2['Date'], 'TIME' => $row2['time'], 'COMMENTS' => str_replace("'", "%",$row2['comments']), ) ); $i++; } $template->assign_vars(array( 'USERNAME' => htmlspecialchars($userdata[username]), 'REGION_FILTER' => str_replace("&", " & ", $region_filter_options), 'SPECIES_FILTER' => $species_filter_options, ) ); $result = $db->sql_query($sql); if (!($result = $db->sql_query($sql))) { die("Database Query Failed" . mysql_error()); } // This is where you would add a new VARS Array if you intend to use your own custom VARS. while ($row = $db->sql_fetchrow($result)) { $selected = (isset($_GET['REGION_FILTER']) AND $_GET['REGION_FILTER']==$row['region']) ? ' selected="selected"' : '' ; $region_filter_options .= "<option value=\"{$row['region']}{$selected}>{$row['region']}</option>\n"; } $result = $db->sql_query($sql3); if (!($result = $db->sql_query($sql3))) { die("Database Query Failed" . mysql_error()); } // This is where you would add a new VARS Array if you intend to use your own custom VARS. while ($row = $db->sql_fetchrow($result)) { $selected = (isset($_GET['SPECIES_FILTER']) AND $_GET['SPECIES_FILTER']==$row['species']) ? ' selected="selected"' : '' ; $species_filter_options .= "<option value=\"{$row['species']}{$selected}>{$row['species']}</option>\n"; } $template->assign_vars(array( 'USERNAME' => htmlspecialchars($userdata[username]), 'REGION_FILTER' => str_replace("&", " & ", $region_filter_options), 'SPECIES_FILTER' => $species_filter_options, ) ); $template->assign_vars(array( 'USERNAME' => htmlspecialchars($userdata[username]), 'DATE_FILTER' => $date_filter_options, ) ); // Build the page $template->pparse('body'); // standard page footer include(IP_ROOT_PATH . 'includes/page_tail.'.PHP_EXT); } ?> regards

I have read that artcle somewhere and that is why i wrote this if(isset($_GET["REGION_FILTER"]) AND $_GET["REGION_FILTER"] = $row['region'] AND strlen($_GET["REGION_FILTER"]) > 0) //if($region_filter = $row['region'] AND strlen($region_filter) > 0) { $region_filter_options .= '<option value="' . $row['region'] . '" SELECTED>' . $row['region'] . '</option>'; } else { $region_filter_options .= '<option value="' . $row['region']. '">' . $row['region'] . '</option>'; } if i am correct this translates to if(isset($_GET["REGION_FILTER"]) AND $_GET["REGION_FILTER"] = $row['region'] AND strlen($_GET["REGION_FILTER"]) > 0) if REGION_FILTER is set and REGION_FILTER = field.Region and the Length of REGION_FILTER >0 then $region_filter_options .= '<option value="' . $row['region'] . '" SELECTED>' . $row['region'] . '</option>'; let region_filter_options = <option value= " field.Region " SELECTED> field.Region </option>'; this should only be done once a match of value selected is found in field.region. else this gets done $region_filter_options .= '<option value="' . $row['region']. '">' . $row['region'] . '</option>'; ( Which is non SELECTED ) for all other field.region values Does that make sense or have i got this completely wrong

This is the code on the HTML page <form action="alertz_VIP.php" method="GET"> <select name=REGION_FILTER size="1" id=REGION_FILTER onchange="this.form.submit()"> <option value="">Select </option> <option value={REGION_FILTER}></option> </select> As you can see changing the dropdown option results in the page being submitted. The Alertz_VIP.php page populates the {REGION_FILTER} Field if that makes sense

Evening People I have this drop downbox on a HTML page, it is populated from a PHP page, if the user makes a selection i want that selection to be retained once the page refreshed. What i have is while ($row = $db->sql_fetchrow($result)) { if(isset($_GET["REGION_FILTER"]) AND $_GET["REGION_FILTER"] = $row['region'] AND strlen($_GET["REGION_FILTER"]) > 0) //if($region_filter = $row['region'] AND strlen($region_filter) > 0) { $region_filter_options .= '<option value="' . $row['region'] . '" SELECTED>' . $row['region'] . '</option>'; } else { $region_filter_options .= '<option value="' . $row['region']. '">' . $row['region'] . '</option>'; } } REGION_FILTER is the ID of the option box The drop down box is populated from the database but i cant seem to get the selected bit working. Can someone help identify what i have done wrong ? regards

Cheers my friend. Sorted

Hi Guys I have created a function, well i think i have function datedropdown_funct() { switch ($date_filter) { case "Today": $retval = '<option value="">Select</option><option value="Today" SELECTED>Today</option><option value="Last 48hrs">Last 48hrs</option><option value="Last week">Last Week</option><option value="last month">Last Month</option>'; break; case "Last 48hrs": $day = date("d")-1; $retval = '<option value="">Select</option><option value="Today">Today</option><option value="Last 48hrs" SELECTED>Last 48hrs</option><option value="Last week">Last Week</option><option value="last month">Last Month</option>'; break; case "Last week": $day = date("d")-7; $retval = '<option value="">Select</option><option value="Today">Today</option><option value="Last 48hrs">Last 48hrs</option><option value="Last week" SELECTED>Last Week</option><option value="last month">Last Month</option>'; break; case "last month": $month = date("m")-1; $retval = '<option value="">Select</option><option value="Today">Today</option><option value="Last 48hrs">Last 48hrs</option><option value="Last week">Last Week</option><option value="last month" SELECTED>Last Month</option>'; break; case "last 3 months": $month = date("m")-3; $retval = '<option value="">Select</option><option value="Today">Today</option><option value="Last 48hrs">Last 48hrs</option><option value="Last week">Last Week</option><option value="last month">Last Month</option><option value="last 3 months" SELECTED>Last 3 Month</option>'; break; default: // Default is the last 48hrs $day = date("d")-1; } return $retval; } it gets called like so $date_filter_options= datedropdown_funct(); The variable $date_filter_options should take on the $retval value, but it doesnt appear to be calling the function Any ideas or correction where i have screwed up ? regards

One last question i have for the night is:- You mentioned creating a function, where do i put the function has the function to be before the call or after the call in the code layout ? I know i can put it in a seperate file, but i dont want to do that until i am comfortable that it all works if you know what i mean. Cheers

Cheers for that Anti-Moronic I had prior to you showing reduced code have done that, i have used if statements to check for dropdown settings. I will repost what i have when i am finished, i know it can be streamlined further and would appreciate any feedback. As i say i am learning and learning fast. Dont want to be a PHP master jedi, just wanna be able to manage my own website if that make sense, but i am enjoying it all the same. Cheers

Make more of an effort to resolve first -- Been at it for 3 days now, my code has changed drastically from the one i have put up, but still no joy. How much longer do i need to look / try to resolve before i get help. As i said i am new to PHP. Would appreciate it if you did look at it, basically i have a page that has 3 drop down boxes, I am trying to do 2 things 1) when an option in any of the drop down boxes is selected, the page refreshes and the selection is maintained. 2) No matter which drop down you select from the php code will perform an database query and populate the other 2 drop down boxes and also updates the screen based upon selected criteria. If you then pick a second option from one of the other drop down box, the database selection is filtered further. I know its a big project for a newbie to try and pull off. Appreciate any support i can get. Regards

I appreciate that JCBones, i didnt know that. so basically if i use ' instead of " then i can have text or variables in it, variables are automatically coverted to text ? Thanks

Sorry, I did say i was a noob at PHP, i Just didnt think that would work , i thought it woul look at it as being text instead of a string the way he had it. regards

Hi There I think the line $result = mysql_query("SELECT* FROM student WHERE $field = '$searchword'") or trigger_error('MySQL error: ' . mysql_error()); should read $result = mysql_query("SELECT* FROM student WHERE $field = '" . $searchword . "'") or trigger_error('MySQL error: ' . mysql_error()); Notice the change - '" . $searchword . "'", you need to break out of the string and add a string if that makes sense, what you have is you are telling it to pull from database where field = "$searchword" and not field =$searchword Hope that makes sense as i am a noob myself, i would get this confirmed but im almost certain thats where your problem lies. Hope it helps Regards

Bump

Guys Didnt think a newbie would baffle all the pro's !! LOL Can anyone help or would i be better abandoning the project ? regards

Guys ???

Hi Anti-Moronic, The SQL selects do not get fired until after the Switch statements are done anyway. if i was to do it the way you suggest would the payload not just be the same with 3 further lines of code as i would have to have 3 new variables and then still define the 3 sql selects at the end anyway. I will play with the idea and see if i can using that method slicken it. Anyone else ??

Hi Guys, I am a typical newbie with a twist, i program in VB, recently moved to PHP. I need help, preferable an example, but first let me explain what i have. I have a .tpl page that has 3 drop down boxes, one of these is populated from within the .tpl page itself. The other 2 i waant to pupulate from a SELECT on a database from a .php file. The .TPL file i have so far is:- <style type="text/css"> <!-- .style7 { color: #006600; font-weight: bold; font-size: 18px; } .style14 {color: #003300; font-weight: bold; } .style16 { color: #000066; font-style: italic; } .style25 { color: #FF0000; font-weight: bold; font-size: 12px; } .style28 { font-size: 12px; color: #330000; } .style29 { font-size: 12px } .style31 {color: #003300; font-weight: bold; font-size: 16px; } .style33 { color: #000099; font-weight: bold; font-style: italic; font-size: 18px; } .style34 { color: #FF0000; font-weight: bold; font-size: 24px; } .style35 {font-size: 12px; color: #003300; } .style36 { color: #FF0000; font-weight: bold; font-size: 14px; font-style: italic; } .style38 {color: #003300; font-weight: bold; font-size: 16px; font-style: italic; } --> </style> <script src="Scripts/AC_RunActiveContent.js" type="text/javascript"></script> <h1 align="center" class="style34">Scotbirds Alertz - Rare and Scarce</h1> <h3 align="center" class="style36">Soon Only VIP Members will be able to Access this Page</h3> <h3 align="center" class="style14">If you have seen something unusual / rare then please call it in on -- <span class="style33">Hotline: 0333 5772473</span></h3> <p align="center" class="style14"> <table width="90%" border="0" align="center" cellpadding="5"> <tr> <?php print $_SERVER['PHP_SELF']; $the_date_filter = $_GET["DATE_FILTER"];?> <td width="1%"><td width="5%"><td width="2%"><td width="2%"><td width="2%"><form action="alertz_VIP.php" method="post"> <td width="2%"><td width="2%"><td width="5%"><span class="style38">Date</span><td width="3%"></td> <td width="12%"><span class="style14"> <select name=DATE_FILTER size="1" id=DATE_FILTER onchange="this.form.submit()"> <option value="All Dates">All Dates</option> <option value="Today">Today</option> <option value="Last 48hrs">Last 48hrs</option> <option value="Last week">Last Week</option> <option value="last month">Last Month</option> </select> </span></td> <td width="1%"> </td> <td width="7%"><span class="style16"><span class="style14"><span class="style31">Region</span></span></span></td> <td width="22%"><span class="style16"><span class="style14"> <select name=REGION_FILTER size="1" id=REGION_FILTER onchange="this.form.submit()"> <option value="AllRg">All Regions</option> <option value={REGION_FILTER}></option> </select> </span></span></td> <td width="1%"> </td> <td width="8%"><span class="style16"><span class="style14"><span class="style31">Species</span></span></span></td> <td width="20%"><span class="style16"><span class="style14"> <select name=SPECIES_FILTER size="1" id=SPECIES_FILTER onchange="this.form.submit()"> <option value="AllSpec">All Species</option> <option value={SPECIES_FILTER}></option> </select> </span></span></td> <td width="9%"><input type="Submit" name="submit2" value="Search" method="get" action="alertz_VIP.php" /></td> </table> <p> </p> <table width="870" border="1" align="center" cellpadding="0" cellspacing="0" bordercolor="#99CC99"> <tr> <th width="178" class="style31" scope="col">Region</th> <th width="184" class="style31" scope="col">Species</th> <th width="96" class="style31" scope="col">Date</th> <th width="96" class="style31" scope="col">Time</th> <th width = "304" class="style31" scope="col">Comments</th> </tr> <!-- BEGIN alerts --> <tr> <th class="style7 style29" scope="col">{alerts.REGION}</th> <th class="style25" scope="col">{alerts.SPECIES}</th> <th class="style35" scope="col">{alerts.DATE} </th> <th scope="col"><span class="style35">{alerts.TIME} </span></th> <th scope="col"><span class="style28">{alerts.COMMENTS} </span></th> </tr> <!-- END alerts --> </table> <p align="center" class="style14"> </p> Now from here i expect the user to select a date from the date box, ( Ideally i would like the other 2 options to only become visible once a selection on the date has been made, i would also like to retain the selected in the date box once the form has been submited. Now the code i have for the PHP so far is quite long winded as my PHP skills are not so good, although i am learning fast. <?php /*************************************************************************** * Alertz.php * ------------------- * begin : 30/10/04/10 * copyright : (C) 2010 Andy Guppy * email : webmaster@scotbird.co.uk * * ***************************************************************************/ define('IN_ICYPHOENIX', true); if (!defined('IP_ROOT_PATH')) define('IP_ROOT_PATH', './'); if (!defined('PHP_EXT')) define('PHP_EXT', substr(strrchr(__FILE__, '.'), 1)); // Include files include(IP_ROOT_PATH . 'common.' . PHP_EXT); include_once(IP_ROOT_PATH . 'includes/functions_groups.' . PHP_EXT); include(IP_ROOT_PATH . '/alerts/alertsconfig.'.PHP_EXT); // Page Authorise $cms_page_id = 'scotalertz'; $cms_page_nav = (!empty($cms_config_layouts[$cms_page_id]['page_nav']) ? true : false); $cms_global_blocks = (!empty($cms_config_layouts[$cms_page_id]['global_blocks']) ? true : false); $cms_auth_level = (isset($cms_config_layouts[$cms_page_id]['view']) ? $cms_config_layouts[$cms_page_id]['view'] : AUTH_ALL); check_page_auth($cms_page_id, $cms_auth_level); // Obtain Select Criteria $temp_region_filter = $_REQUEST["REGION_FILTER"]; $temp_species_filter = $_REQUEST["SPECIES_FILTER"]; $temp_date_filter = $_REQUEST["DATE_FILTER"]; // Filter characters if required $region_filter =str_replace(" & ", "&", $temp_region_filter); $species_filter = $temp_species_filter; $date_filter = $temp_date_filter; // standard session management $userdata = session_pagestart($user_ip); // Check to see if user is logged in if ((!$userdata['session_logged_in']) ) // No he isnt { redirect(append_sid(LOGIN_MG . '?redirect=alerts.' . PHP_EXT)); } else // Yes they are { init_userprefs($userdata); // set page title $page_title = "ScotBird alerts - VIP's ONLY !! "; // standard page header include(IP_ROOT_PATH . 'includes/page_header.'.PHP_EXT); // Connect to the database $db = new sql_db($alerts_mysql_host,$alerts_mysql_username,$alerts_mysql_password,$alerts_mysql_db,false); if(!$db) { die("Database Connection Failed:- Please Contact Site Admin" . mysql_error()); } $template->set_filenames(array('body' => 'alertz_VIP.tpl')); if (!isset($_REQUEST['DATE_FILTER'])) { // if date is NOT set then perform this echo 'Date has not been set'; $sql2 = "SELECT * FROM alerts ORDER BY Date DESC, time DESC "; $sql3 = "SELECT DISTINCT species FROM alerts GROUP BY species"; $sql = "SELECT DISTINCT region FROM alerts GROUP BY region"; echo $sql2; } else { // if date is set then perform this $year =date("Y"); $month = date("m"); $day = date("d"); $theenddate = $year . '-' .$month . '-' . $day ; echo 'the choice selected was :- '.$_REQUEST['DATE_FILTER']; switch ($date_filter) { case "All Dates": $sql2 = "SELECT * FROM alerts ORDER BY Date DESC, time DESC "; break; case "Today": $sql2 = "SELECT * FROM alerts WHERE Date = '".$theenddate ."' ORDER BY Date DESC, time DESC "; $sql3 = "SELECT DISTINCT species FROM alerts WHERE Date = '".$theenddate ."' GROUP BY species"; $sql = "SELECT DISTINCT region FROM alerts WHERE Date ='".$theenddate ."' GROUP BY region"; break; case "Last 48hrs": $year =date("Y"); $month = date("m"); $day = date("d")-1; $thestartdate = $year . '-' .$month . '-' . $day ; $sql2 = "SELECT * FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' ORDER BY Date DESC, time DESC "; $sql3 = "SELECT DISTINCT species FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY species"; $sql = "SELECT DISTINCT region FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY region"; break; case "Last week": $year =date("Y"); $month = date("m"); $day = date("d")-7; $thestartdate = $year . '-' .$month . '-' . $day ; $sql2 = "SELECT * FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' ORDER BY Date DESC, time DESC "; $sql3 = "SELECT DISTINCT species FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY species"; $sql = "SELECT DISTINCT region FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY region"; break; case "last month": $year =date("Y"); $month = date("m")-1; $day = date("d"); $thestartdate = $year . '-' .$month . '-' . $day ; $sql2 = "SELECT * FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' ORDER BY Date DESC, time DESC "; $sql3 = "SELECT DISTINCT species FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY species"; $sql = "SELECT DISTINCT region FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY region"; break; case "last 3 months": $year =date("Y"); $month = date("m")-3; $day = date("d"); $thestartdate = $year . '-' .$month . '-' . $day ; $sql2 = "SELECT * FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' ORDER BY Date DESC, time DESC "; $sql3 = "SELECT DISTINCT species FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY species"; $sql = "SELECT DISTINCT region FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY region"; break; default: // Default is the last 48hrs $year =date("Y"); $month = date("m"); $day = date("d")-1; $thestartdate = $year . '-' .$month . '-' . $day ; $sql2 = "SELECT * FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' ORDER BY Date DESC, time DESC "; $sql3 = "SELECT DISTINCT species FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY species"; $sql = "SELECT DISTINCT region FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY region"; } } $result2 = $db->sql_query($sql2); if (!($result2 = $db->sql_query($sql2))) { die("Database Query Failed" . mysql_error()); } $i = 1; while ( $row2 = $db->sql_fetchrow($result2) ) { $template->assign_block_vars('alerts', array( 'POS' => $i , 'REGION' => str_replace("&", " & ", $row2['region']), 'SPECIES' => str_replace("%", "'",$row2['species']), 'DATE' => $row2['Date'], 'TIME' => $row2['time'], 'COMMENTS' => str_replace("'", "%",$row2['comments']), ) ); $i++; } $template->assign_vars(array( 'USERNAME' => htmlspecialchars($userdata[username]), 'REGION_FILTER' => str_replace("&", " & ", $region_filter_options), 'SPECIES_FILTER' => $species_filter_options, ) ); $result = $db->sql_query($sql); if (!($result = $db->sql_query($sql))) { die("Database Query Failed" . mysql_error()); } // This is where you would add a new VARS Array if you intend to use your own custom VARS. while ($row = $db->sql_fetchrow($result)) { $region_filter_options .= '<option value="' . $row['region'] . '">' . $row['region'] . '</option>'; } $result = $db->sql_query($sql3); if (!($result = $db->sql_query($sql3))) { die("Database Query Failed" . mysql_error()); } // This is where you would add a new VARS Array if you intend to use your own custom VARS. while ($row = $db->sql_fetchrow($result)) { $species_filter_options .= '<option value="' . $row['species'] . '">' . $row['species'] . '</option>'; } $template->assign_vars(array( 'USERNAME' => htmlspecialchars($userdata[username]), 'REGION_FILTER' => str_replace("&", " & ", $region_filter_options), 'SPECIES_FILTER' => $species_filter_options, ) ); // Build the page $template->pparse('body'); // standard page footer include(IP_ROOT_PATH . 'includes/page_tail.'.PHP_EXT); } ?> Now my questions are these:- 1) In the .tpl file hows can i submit from any of the dropdown boxes and retain the selection after submission 2) How can i have it so that the second and third drop down boxes are only visible after the previous one, ie the first box ( date ) has to have a selection before the Region ( 2nd one ) is visible and so 3) How can i reduce the amount of code to cover all options in building a select statement or is Switch - case the best way. I would greatly appreciate help with this and even more so for some example of what i am asking for so i can learn from them.