GamerGun Posted November 6, 2008 Author Share Posted November 6, 2008 Whoohoo, that works, great! For both 3 and 4. I guess the totals part for records4 will be kinda difficult, wont it? Oh and btw, also for records4 i want to create a check box near "Afdeling" which will output all departments when doing a search. Any idea on that? Thank you Quote Link to comment Share on other sites More sharing options...
bobbinsbro Posted November 6, 2008 Share Posted November 6, 2008 re. the afdeling checkbox: doesn't not searching for a specific department have the same effect as searching for all departments? re. the totals: is the same format used all the time, or does the format changed depending on the search (one isn't difficult, the other may take a bit of time)? Quote Link to comment Share on other sites More sharing options...
GamerGun Posted November 6, 2008 Author Share Posted November 6, 2008 Just a search for all departments and its calls. I tried searching for 885 since all my departments start with that but that doesn't work for some reason. For the totals: Well, the idea is just to have below the form a overview with totals. Total work hours per employee Total work hours per department Total work hours per gerelateerd (= related) It would be even better if i could have 'Total work hours "employee X" for "department Y"' and so but that's not really needed if it's too much work for you. Most important is just what i typed above, that you can see how many work someone (employee) did, or how much for something (department/related) was spend (in total), without relations. Thanks Quote Link to comment Share on other sites More sharing options...
bobbinsbro Posted November 6, 2008 Share Posted November 6, 2008 i'm sorry - i still don't understand why searching for all departments would give different results than just doing an empty search in records4 (since i noticed you removed the empty query error). re. the totals: i'll see what i can do about that. could you please post your current working code for records4? off topic: i think we should move this correspondence to PM. we kinda over did it here... Quote Link to comment Share on other sites More sharing options...
GamerGun Posted November 6, 2008 Author Share Posted November 6, 2008 I'm stupid, did not even know that i removed that error ??? Should i leave it like this or re-add the error and a extra button for searching all departments? records4.php <html><head> <title>Uren</title> <STYLE TYPE="text/css"> <!-- TD{font-size: 11pt;} ---> </STYLE> <SCRIPT language=Javascript> <!-- function isNumberKey(evt) { var charCode = (evt.which) ? evt.which : event.keyCode if (charCode > 31 && (charCode < 48 || charCode > 57)) return false; return true; } //--> </SCRIPT> <SCRIPT LANGUAGE="JavaScript"> <!-- function showList() { sList = window.open("popup2.php", "list", "width=300,height=600"); } function remLink() { if (window.sList && window.sList.open && !window.sList.closed) window.sList.opener = null; } // --> </SCRIPT> </head><body> <?php $urendropdown = <<<EOT <select name="uren"> <option value=""></option> <option value="0">0</option> <option value="1">1</option> <option value="2">2</option> <option value="3">3</option> <option value="4">4</option> <option value="5">5</option> <option value="6">6</option> <option value="7">7</option> <option value="8">8</option> </select> EOT; $minutendropdown = <<<EOT <select name="minuten"> <option value=""></option> <option value="00">00</option> <option value="15">15</option> <option value="30">30</option> <option value="45">45</option> </select> EOT; $gerelateerdropdown = <<<EOT <select name="spec"> <option value=""></option> <option value="CAD-E">CAD-E</option> <option value="CAD-S">CAD-S</option> <option value="CAE">CAE</option> <option value="LINUX">LINUX</option> </select> EOT; ?> <form method="post" action="<?php echo $_SERVER['PHP_SELF'] ?>" name="advanced"> <table border="1" bordercolor="#FFCC00" style="background-color:#FFFFCC" width="900" cellpadding="3" cellspacing="3"> <tr> <td>Naam</td> <td><input type="text" size="25" name="voornaam" value='Voornaam' /></td> <td><input type="text" size="25" name="achternaam" value='Achternaam' /></td> </tr> <tr> <td>Gerelateerd</td> <td colspan="2"><?php echo $gerelateerdropdown;?></td> </tr> <tr> <td>Afdeling</td> <td><input type="text" size="60" name="afdeling"/></td> <td><input type="button" value="Selecteer afdeling" onclick="showList()"></td> </tr> <tr> <td>Call nr</td> <td colspan="2"><input type="text" size="25" maxlength="5" onkeypress="return isNumberKey(event)" name="callnr" /></td> </tr> <tr> <td>Omschrijving</td> <td colspan="2"><input type="text" size="60" name="omschrijving" /></td> </tr> <tr> <td>Datum (YYYY-MM-DD)</td> <td><input type="text" size="25" name="datum1" value='Start' /></td> <td><input type="text" size="25" name="datum2" value='Eind' /></td> </tr> <tr> <td>Week</td> <td><input type="text" size="25" name="week1" value='Start' /></td> <td><input type="text" size="25" name="week2" value='Eind' /></td> </tr> <tr> <td>Uren / Min</td> <td><?php echo $urendropdown;?><?php echo $minutendropdown;?></td> <td><input type="submit" name="submit" value="Verstuur"></td> </tr> </table> </form> <?php function validDate($date, $errors){ $datecheck = explode("-", $date); if (count($datecheck) != 3){ $errors[] = "Invalid Search: Submitted date uses invalid format!"; } else if(!is_numeric($datecheck[0]) || !is_numeric($datecheck[1]) || !is_numeric($datecheck[2])){ $errors[] = "Invalid Search: Date must be numeric only!"; } return $errors; } //if search submitted if(isset($_POST['submit'])){ $errors = array(); //used to collect errors $sql = array(); //used to collect sql query bits // form submitted //set sql query bit for voornaam and achternaam if(!empty($_POST['voornaam']) && (strcasecmp($_POST['voornaam'], 'voornaam') != 0)){ $arrayNaam = array("voornaam LIKE '%".$_POST['voornaam']."%'"); } if(!empty($_POST['achternaam']) && (strcasecmp($_POST['achternaam'], 'achternaam') != 0)){ $arrayNaam[] = "achternaam LIKE '%".$_POST['achternaam']."%'"; } if (isset($arrayNaam)){ $strNaam = implode(" AND ", $arrayNaam); if (strlen($strNaam) > 0) $sql[] = "userid = (SELECT userid FROM werknemers WHERE ".$strNaam.")"; } //set sql query bit for specificatie if(!empty($_POST['spec'])) { $sql[] = "specid = (SELECT specid FROM specificatie WHERE specificatie LIKE '".$_POST['spec']."%')"; } //set sql query bit for afdeling if(!empty($_POST['afdeling'])){ $sql[] = "afdelingid = (SELECT afdelingid FROM afdelingen WHERE afdeling LIKE '%".$_POST['afdeling']."%')"; } //set sql query bit for callnr if(!empty($_POST['callnr'])){ $sql[] = "callnr LIKE '%".$_POST['callnr']."%'"; } else $sql[] = "callnr != 0"; //set sql query bit for omschrijving if(!empty($_POST['omschrijving'])){ $sql[] = "omschrijving LIKE '%".$_POST['omschrijving']."%'"; } //set sql query bit for datum $dateVals = array(); if(!empty($_POST['datum1']) && (strcasecmp($_POST['datum1'], 'start') != 0)) { $errorCount = count($errors); $errors = validDate($_POST['datum1'], $errors); if (count($errors) == $errorCount) $dateVals['datum1'] = $_POST['datum1']; } if(!empty($_POST['datum2']) && (strcasecmp($_POST['datum2'], 'eind') != 0)) { $errorCount = count($errors); $errors = validDate($_POST['datum2'], $errors); if (count($errors) == $errorCount) $dateVals['datum2'] = $_POST['datum2']; } //add a check here to make sure date1 < date2 switch (count($dateVals)){ case 0: break; case 1: $sql[] = "datum = '".(isset($dateVals['datum1'])?$dateVals['datum1']:$dateVals['datum2'])."'"; break; case 2: $sql[] = "datum BETWEEN '".$dateVals['datum1']."' AND '".$dateVals['datum2']."'"; break; } unset($dateVals); //sql query bit for week $weekVals = array(); if(!empty($_POST['week1']) && is_numeric($_POST['week1'])) $weekVals['week1'] = $_POST['week1']; if(!empty($_POST['week2']) && is_numeric($_POST['week2'])) $weekVals['week2'] = $_POST['week2']; switch (count($weekVals)){ case 0: break; case 1: $sql[] = "week = '".(isset($weekVals['week1'])?$weekVals['week1']:$weekVals['week2'])."'"; break; case 2: if ($weekVals['week1'] > $weekVals['week2']){ $errors[] = "Invalid Search: Week1 must have a lower value than Week2!"; break; } $sql[] = "week BETWEEN '".$weekVals['week1']."' AND '".$weekVals['week2']."'"; break; } unset($weekVals); //sql query bit for uren if (!empty($_POST['uren']) || (strcasecmp($_POST['uren'], '0') == 0)) { $sql[] = "uren = '".$_POST['uren']."'"; } //sql query bit for minuten if (!empty($_POST['minuten']) || (strcasecmp($_POST['minuten'], '00') == 0)) { $sql[] = "minuten = '".$_POST['minuten']."'"; } //if no fields were filled if (count($sql) == 0) $errors[] = "Invalid Search: At least one field must be filled!"; //if there are errors, kill the script and display the errors. if (count($errors) > 0){ echo "<strong>Search Errors:</strong><br />"; foreach ($errors as $error){ echo $error, "<br />"; } exit; } //setup the query $query = implode(" AND ", $sql); $query = "SELECT * FROM uren WHERE ".$query; // set server access variables $host = "localhost"; $user = "root"; $pass = "passwd"; $db = "urendatabase"; // Open Connection $connect = mysql_connect($host, $user, $pass) or die ("Unable to connect to host"); //select Database mysql_select_db($db) or die ("Unable to connect to database"); //save all queries in an array, to tidy things up a bit $sql = array('werk' => 'SELECT * FROM werknemers', 'afd' => 'SELECT * FROM afdelingen', 'spec' => 'SELECT * FROM specificatie'); //go through the $sql array, and get values from DB for each query foreach ($sql as $key => $sql){ $result = mysql_query($sql); switch ($key){ case 'werk': $werkArray = array(); while(list($userid, $voornaam, $achternaam) = mysql_fetch_array($result)){ $werkArray[$userid] = array('voornaam' => $voornaam, 'achternaam' => $achternaam); } break; case 'afd': $afdArray = array(); while(list($afdelingid, $afdeling) = mysql_fetch_array($result)){ $afdArray[$afdelingid] = $afdeling; } break; case 'spec': $specArray = array(); while(list($specid, $specificatie) = mysql_fetch_array($result)){ $specArray[$specid] = $specificatie; } break; default: die('there\'s a bug in the system!'); } unset($result); } $result = mysql_query($query); $num = mysql_num_rows($result); echo "<b><center>Database output</center></b><br /><br />"; ?> <table border="1" bordercolor="#FFCC00" style="background-color:#FFFFCC" width="100%" cellpadding="3" cellspacing="3"> <tr> <td width="10%">Voornaam</td> <td width="10%">Achternaam</td> <td width="7%">Gerelateerd</td> <td width="43%">Afdeling</td> <td width="5%">Call nr</td> <td width="5%">Uren</td> <td width="5%">Minuten</td> <td width="10%">Datum</td> <td width="5%">Week</td> </tr> </table> <?php for($i = 0; $i < $num; ++$i) { $userid=mysql_result($result,$i,"userid"); $specid=mysql_result($result,$i,"specid"); $afdelingid=mysql_result($result,$i,"afdelingid"); $uren=mysql_result($result,$i,"uren"); $minuten=mysql_result($result,$i,"minuten"); $omschrijving=mysql_result($result,$i,"omschrijving"); $callnr=mysql_result($result,$i,"callnr"); $datum=mysql_result($result,$i,"datum"); $week=mysql_result($result,$i,"week"); ?> <table border="1" bordercolor="#FFCC00" style="background-color:#FFFFCC" width="100%" cellpadding="3" cellspacing="3"> <tr> <td width="10%"><?php echo $werkArray[$userid]['voornaam']; ?></td> <td width="10%"><?php echo $werkArray[$userid]['achternaam']; ?></td> <td width="7%"><?php echo $specArray[$specid]; ?></td> <td width="43%"><?php echo $afdArray[$afdelingid]; ?></td> <td width="5%"><?php echo $callnr; ?></td> <td width="5%"><?php echo $uren; ?></td> <td width="5%"><?php echo $minuten ?></td> <td width="10%"><?php echo $datum; ?></td> <td width="5%"><?php echo $week; ?></td> </tr> </table> <?php } } ?> </body></html> I changed records3 and records4 a bit, made dropdown lists for "Gerelateerd" and "Overige" . Both work fine tho! Anyhow yeh perhaps we should do this on via PM or so if the mods mind doing it here. Thanks 4 all 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.