jhh1987 Posted October 17, 2014 Share Posted October 17, 2014 I have attempted to get a php reporting page built for our off site managers to run reports. Because of the number of employees I think it ouwld be best to sort by foreman, then be able to pick from the resulting drop down list of current employees under that foreman. My issue I think really comes from having the first drop down be dynamic in that you would see a list of foremen which comes from a query of the main employees table and then picks up only active employees and their foreman. The tutorials I have seen all reference an 'id" field but since I am pulling the info for the drop down from a query, should I have some sort of rowID? If so how? The tutorial I have found checks to see if you pick a number which I seem to have gotten around but when I do the query and look for $GF (general foreman) then the query comes back empty (this is bolded and underlined below). Should this be some sort of rowID for the general foreman drop down? Below is what I have been able to sort-of get working (adding in the URL handlers I think will be useful for people in the future to be able to bookmark it/send out emails with links already in it etc) Here is the tutorial http://www.plus2net.com/php_tutorial/php_drop_down_list.php Not sure if i understand why he used the $nocitia tags either I know Im close on this but am starting to go crosseyed! Thanks for pointing me in the right direction. <?php require 'config.php'; // Database connection //////// End of connecting to database //////// ?> <!doctype html public "-//w3c//dtd html 3.2//en"> <html> <head> <title>Multiple drop down list box</title> <SCRIPT language=JavaScript> function reload(form) { var val=form.GF.options[form.GF.options.selectedIndex].value; var val2=form.EMPLOYEE_ID.options[form.EMPLOYEE_ID.options.selectedIndex].value; var val3=form.SEPARATED.options[form.SEPARATED.options.selectedIndex].value; self.location='dd3.php?GF=' + val + '&EMPLOYEE_ID=' + val2 + '&SEPARATED=' + val3 ; } </script> </head> <body> <?Php @$GF=$_GET['GF']; // Use this line or below line if register_global is off @$EMPLOYEE_ID=$_GET['EMPLOYEE_ID']; // Use this line or below line if register_global is off @$SEPARATED=$_GET['SEPARATED']; // Use this line or below line if register_global is off ///////// Getting the data from Mysql table for first list box////////// $quer2="SELECT tbl_Employee_Master.Current_GF as GF FROM tbl_Employee_Master GROUP BY tbl_Employee_Master.Current_GF, tbl_Employee_Master.Separated HAVING (((tbl_Employee_Master.Separated)=0))"; ///////////// End of query for first list box//////////// /////// for second drop down list we will check if category is selected else we will display all the subcategory///// if(strlen($GF) > 0){ $quer="SELECT Employee_ID tbl_Employee_Master WHERE Current_GF=$GF"; }else{$quer="SELECT DISTINCT Employee_ID, Last_Name, First_Name FROM tbl_Employee_Master order by Last_Name"; } ////////// end of query for second subcategory drop down list box /////////////////////////// echo "<form method=post name=f1 action='dd-check.php'>"; /// Add your form processing page address to action in above line. Example action=dd-check.php//// ////////// Starting of first drop downlist ///////// echo "<select name='GF' onchange=\"reload(this.form)\"><option value=''>Select one</option>"; foreach ($dbo->query($quer2) as $noticia2) { if($noticia2['GF']==@$GF){echo "<option selected value='$noticia2[GF]'>$noticia2[GF]</option>"."<BR>";} else{echo "<option value='$noticia2[GF]'>$noticia2[GF]</option>";} } echo "</select>"; ////////////////// This will end the first drop down list /////////// ////////// Starting of second drop downlist ///////// echo "<select name='EMPLOYEE_ID'><option value=''>Select one</option>"; foreach ($dbo->query($quer) as $noticia) { echo "<option value='$noticia[Employee_ID]'>$noticia[Employee_ID] $noticia[Last_Name] $noticia[First_Name]</option>"; } echo "</select>"; ////////////////// This will end the second drop down list /////////// //// Add your other form fields as needed here///// echo "<input type=submit value=Submit>"; echo "</form>"; ?> <br><br> <a href=dd.php>Reset and start again</a> <br><br> <center><a href='http://www.plus2net.com' rel="nofollow">PHP SQL HTML free tutorials and scripts</a></center> </body> </html> Quote Link to comment Share on other sites More sharing options...
ginerjm Posted October 17, 2014 Share Posted October 17, 2014 Do you want to do this via JS and Ajax? Or would a plain php solution work? The latter would be easy. Simply send the form with the primary foreman dropdown where the values in the dropdown are the foreman ids. Then when they submit that you take the foreman value and do a second query of employees in that foreman's unit. Take those query results and build your second dropdown and re-send the page with the selected foreman's element marked as the selected one. An Ajax solution is pretty similar. Same php solution just being triggered via js instead of a form submit. Quote Link to comment Share on other sites More sharing options...
jhh1987 Posted October 17, 2014 Author Share Posted October 17, 2014 I guess what im asking is how should i assign those foreman IDs? and then from those rowIDs how do i proceed to filter the results? Am i barking up the right tree on this? Im all for the path of least resistance but am relatively new to all of this and was just trying to follow a tutorial. Quote Link to comment Share on other sites More sharing options...
ginerjm Posted October 17, 2014 Share Posted October 17, 2014 What is your db table structure? I would imagine that each employee has an id. And that foremen have some indicator that they are in fact foremen. If not, how do you recognize them? Quote Link to comment Share on other sites More sharing options...
jhh1987 Posted October 17, 2014 Author Share Posted October 17, 2014 Its basically a huge table...Ill try to show you below Employee_ID | Last_Name|First_Name|Current_GF|TrainingDocument (yes/no tiny int) 1234 Doe John COBB TY -1 2345 COBB TY COBB TY 0 7478 Trombone Rusty COBB TY -1 So I gather a list of the current GF's from the table of employees, yes in theory they all should have a row themselves but this info comes from corporate and is automatically imported and I have had many errors so to assume thats always true would be wrong (like if a new person gets hired as a foreman, but isnt reflected yet on the data). Quote Link to comment Share on other sites More sharing options...
ginerjm Posted October 17, 2014 Share Posted October 17, 2014 (edited) What is 'current_gf'? Is that the foreman's name? If so - bad choice - should have been id. If not, then how does one id any foreman? And whatever do you mean by "they should all should have a row (to) themselves"? Edited October 17, 2014 by ginerjm Quote Link to comment Share on other sites More sharing options...
jhh1987 Posted October 17, 2014 Author Share Posted October 17, 2014 Right so the current_gf is what corporate would say is the employees current foreman, im simply looking at this field for all GFs employee_id is unique to everyone I meant that everyone who pops up as a GF should have their own record and unique employeeID as well Quote Link to comment Share on other sites More sharing options...
ginerjm Posted October 17, 2014 Share Posted October 17, 2014 And the foremen DO have their own record. Your current structure is not that bad. A better design would have been to use the foreman's own employee id in the current_gf field instead of his name. As it is my original solution will still work - you just have to retain the foreman's name instead of his id in order to do that second query for the second dropdown. Think about it. 1 - query the table for distinct current_gf values and output those names to build your primary dropdown. 2 - upon submit grab the incoming post value and do the second query looking for employee info that has that selected name as the current_gf field value. Quote Link to comment Share on other sites More sharing options...
Barand Posted October 17, 2014 Share Posted October 17, 2014 The main problem you have given yourself is the foreman's name in the employee records does not match any field in the foreman's own record. In the employee record it is "COBB TY" whereas in Cobb's record you have "COBB" and "TY" in separate fields. Which is why you should use IDs to link records. Quote Link to comment Share on other sites More sharing options...
jhh1987 Posted October 17, 2014 Author Share Posted October 17, 2014 you just have to retain the foreman's name Right I am storing that in $GF 1 - query the table for distinct current_gf values and output those names to build your primary dropdown. The data for the first drop down of GFs comes from SELECT tbl_Employee_Master.Current_GF as GF FROM tbl_Employee_Master GROUP BY tbl_Employee_Master.Current_GF, tbl_Employee_Master.Separated HAVING (((tbl_Employee_Master.Separated)=0)) After change, the second drop down uses the below to check if a GF has been selected, if so it should use $GF to find all what employees have $GF as their gf on their file, however I get no results. If nothing is selected I can see all the employees. if(strlen($GF) <> 0){$quer="SELECT DISTINCT Employee_ID FROM tbl_Employee_Master WHERE Current_GF = $GF";}else{$quer="SELECT DISTINCT Employee_ID, Last_Name, First_Name FROM tbl_Employee_Master order by Last_Name"; } 2 - upon submit grab the incoming post value and do the second query looking for employee info that has that selected name as the current_gf field value. Quote Link to comment Share on other sites More sharing options...
Solution ginerjm Posted October 17, 2014 Solution Share Posted October 17, 2014 Your query in the second is way too complex. Why the distinct? Are you saying that employee_id is not unique? I don't think so. Also - since you are not doing join and don't have multiple tables you can shorten this way down. $q = "select Current_GF as foreman from tbl_Employee_Master tbl where Separated=0"; And for the second query: $q = "select Employee_ID from tbl_Employee_Master WHERE Current_GF = '$GF'"; (Note the quotes on $GF) And for the 3rd query: $quer = "select Employee_ID, Last_Name, First_Name from tbl_Employee_Master order by Last_Name"; Quote Link to comment Share on other sites More sharing options...
jhh1987 Posted October 23, 2014 Author Share Posted October 23, 2014 Thanks so much for your help ginerjm that '$GF' really helped. Everything is working smoothly now and even updates based off of the onchange action. Result below for others with the same issue. ///////// Getting the data from Mysql table for first list box////////// $quer2="SELECT tbl_Employee_Master.Current_GF as GF FROM tbl_Employee_Master GROUP BY Current_GF, tbl_Employee_Master.Separated HAVING (((tbl_Employee_Master.Separated)=0))"; ///////////// End of query for first list box//////////// /////// for second drop down list we will check if category is selected else we will display all the subcategory///// if(strlen($GF) <>0){$quer="SELECT DISTINCT Employee_ID, Last_Name, First_Name, Separated FROM tbl_Employee_Master WHERE ((Current_GF='$GF') AND (Separated=0)) order by Last_Name";} else{$quer="SELECT DISTINCT Employee_ID, Last_Name, First_Name, Separated FROM tbl_Employee_Master WHERE Separated=0 order by Last_Name";} ////////// end of query for second subcategory drop down list box /////////////////////////// echo "<form method=post name=f1 action='Reports.php'>"; /// Add your form processing page address to action in above line. Example action=dd-check.php//// ////////// Starting of first drop downlist ///////// echo "<select name='GF' onchange=\"reload(form)\"><option value=''>Select GF</option>"; foreach ($dbo->query($quer2) as $noticia2) { if($noticia2['GF']==@$GF){echo "<option selected value='$noticia2[GF]'>$noticia2[GF]</option>"."<BR>";} else{echo "<option value='$noticia2[GF]'>$noticia2[GF]</option>";} } echo "</select>"; ////////////////// This will end the first drop down list /////////// ////////// Starting of second drop downlist ///////// echo "<select name='EMPLOYEE_ID' onchange=\"reload(form)\"><option value=''>Select Employee</option>"; foreach ($dbo->query($quer) as $noticia) { if($noticia['Employee_ID']==@$EMPLOYEE_ID){echo "<option selected value='$noticia[Employee_ID]'>$noticia[Employee_ID] $noticia[Last_Name] $noticia[First_Name]</option>"."<BR>";} else{echo "<option value='$noticia[Employee_ID]'>$noticia[Employee_ID] $noticia[Last_Name] $noticia[First_Name]</option>";} } echo "</select>"; ////////////////// This will end the second drop down list /////////// And then the form has this code to show the report. if(strlen($EMPLOYEE_ID) >0 ){$result = mysql_query("SELECT * FROM tbl_Employee_Master where Employee_ID = $EMPLOYEE_ID");} echo "<style> table, th {border: 2px solid black;}</style>"; echo "<table>"; // keeps getting the next row until there are no more to get if(strlen($EMPLOYEE_ID) >0 ){while($row = mysql_fetch_array( $result )) { // Print out the contents of each row into a table echo "<font size=5 ><b>Report for " . $row['Last_Name'] . ", " . $row['First_Name'] . "</b></font>"; echo "</p>"; echo "<font size=4 ><b><u> General Information </u></b></font>"; echo "</p>"; echo "<tr><td align=left><b><b>Employee ID</td> <font size='2'><td width=120>"; echo $row['Employee_ID']; echo "<tr><td align=left><b><b>Last Name</td> <font size='2'><td width=120>"; echo $row['Last_Name']; echo "<td align=left><b><b>First Name</td> <font size='2'><td width=120>"; echo $row['First_Name']; 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.