Help with Dropdown and MySQL selects

FifeBirder · November 5, 2010

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.

Anti-Moronic · November 5, 2010

I'm not sure about your specific problem..

But you are repeating a lot of needless code. Like the way you are constructing your sql queries using switch. You need only use switch to alter the variables you compare against the db. Think about it - if you are using the same query with different variables, you don't need to repeat the query at all, just change the variables.


switch($something){

  case 'somematch':
    $tablename = 'firsttable';
    break;

  case 'anothermatch':
    $tablename = 'diftable';
    break;

}

$sql = "INSERT INTO $tablename";

Hope that helps at least until somebody comes along with a solution to your problem.

FifeBirder · November 5, 2010

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 ??

FifeBirder · November 6, 2010

Guys ???

FifeBirder · November 6, 2010

Guys

Didnt think a newbie would baffle all the pro's !!

LOL

Can anyone help or would i be better abandoning the project ?

regards

FifeBirder · November 7, 2010

Bump

Anti-Moronic · November 7, 2010

You're not getting any answers because you need to make more of an effort to resolve a solution first. If you just spend a few more minutes looking at the problem, coming up with some more specific questions, searching google for background, and THEN coming here for help, you may just find exactly what you're looking for.

Again, lots of needless code. You need to stop that habit right away or your source will turn into a mess of unmaintainable text. If no one has replied later I'll look into it for you and advise.

..and hey, another bump just trying to help!

FifeBirder · November 7, 2010

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

Anti-Moronic · November 7, 2010

Right, I've got some free time on my hands so I've taken a closer look.

1. this is done by dynamically setting the 'selected' flag of the submitted option in your select list. You can do this via setting the select variable within every options, or you can abstract the whole thing by using a loop. This is how you do it with a loop:

You would simply replace NAME with the name of your select option. I see you have at least 2. I have only used 2 of your options for simplicity and so you can do the rest to better learn how to do it.

You would usually wrap this with <select and </select which you can by either weaving in and out of php or simply setting the html within php.


$select = '<select name="NAME">';

$options = array('All Dates' => 'All Dates', 'Today' => 'Today');

foreach($options as $key => $option){

  $sel = (isset($_POST['NAME']) && $_POST['NAME'] == $key) ? 'selected' : '';
  $select .= "<option value='$key' $sel>$option</option>";

}

$select .= '</select>';

Then you can echo $select and you'll have your list. This is also more manageable, you can easily add new items with the $options array and everything will continue to function perfectly. You can also use the $options array for other processing. NOTE: you might want to escape the post data in the above though it is only used for camparison and not outputted or stored at least.

If there is anything you don't understand in the above, search the php manual and you'll find better descriptions than anyone can answer with here.

2. this can be and would preferably be done with javascript. To do with php, you would wrap your select boxes in an if statement which checks whether the previous drop down has been submitted.


if(isset($_POST['dropdown1'])){

  // drop down here
  
}

You would then either construct your select element or echo straight out with 'echo' OR weave out of php ?> and enter your html directly. Lots of options. Ideal possibly to construct your select elements using loops like above for abstract purposes, then only echo those elements on the above condition.

For example, if we did do that, because we have already constructed our select box, we would simply do:


if(isset($_POST['dropdown1'])){

  echo $select;
  
}

Of course, change your $select variable name to account for others.

Like I said though, this is better done with javascript and I'd advise you checkout jquery and search in google for this sort of thing. There are tons of examples. It is more responsive than managing everything in the backend because you would have to keep posting back to the page just to change the visibility of the select elements.

Oh, one other way with php is to dynamically set the style="" attribute of the select statement to display:none of visibility:hidden

3. You can do a lot more to reduce your code. Constructing with loops and creating functions for common uses are the best ways. In fact, the loop would likely be part of the function. Something like:

$select1 = createSelect(array('Date'=>'Date'));

The function would then have the loop I created above and take *any* array to produce the select list. I'd advise you look into functions and how important they are. All of your switch statements should be inside a class or a function at least. You should then pass the $date_filter along to that which will cleverly construct your sql query ideally using an abstract loop.

Hope that helps!! Let me know if you have any other questions. Don't forget, you can reduce these repeated queries to a couple of sets. You only need to change the $variables with the switch, not redefine the sql statements.

Anti-Moronic · November 7, 2010

Oh, to illustrate how you can reduce your code take a look at this from your source:


            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;

..can be reduced to:


               $year =date("Y");
               $month = date("m");
               $day = date("d");

            case "last month":
               $month = date("m")-1;
            break;
            case "last 3 months":
               $month = date("m")-3;
            break;

               $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";

Take a close look and see how it is doing the exact same thing but is far more maintainable. What is does:

1. initiate your variables first ($date etc)

2. change your variables based on certain conditions - but ONLY change the variables you need to

3. define your sql statements using the variables - whether they be the original variables, or the modified ones. Because this comes after all of that, you'll always have the correct query.

You can do this also to reduce your code further but your other sql statements are slightly different so you'd have to take that into account.

FifeBirder · November 7, 2010

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

FifeBirder · November 7, 2010

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

Anti-Moronic · November 7, 2010

Hi Fife.

Sure, you can do the functions in the same file for the time being. Yeh, the functions will have to feature *before* you use them. That's the only rule. Of course, it is highly advised you put them in a separate file and include. Also, there will be little benefit in keeping them in the same file even if you are just doing so you can see everything in front of you.

You have to take scope (search) into account where your variables outside functions will not be available unless you pass them on, or 'globalize' them. $_POST and $_GET are global variables (among others) which allow you to access them from any where. Because of this scope issue, it will be quite confusing for you using functions within the same file.

Before you do start using functions I would definitely advise you do a few searches on the basics so you understand. You should then be able to wrap most of this in a couple of functions (one for producing your select list, and one for your sql constructor).

Let me know if you have any questions on that once you've tried to convert some of the code over.

Sign In

Help with Dropdown and MySQL selects

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information