Jump to content

Archived

This topic is now archived and is closed to further replies.

GamerGun

[SOLVED] Insert form to DB (Multiple rows, array)

Recommended Posts

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

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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...  ;)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.