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

GamerGun · 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

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

GamerGun · 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

bobbinsbro · 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...

GamerGun · 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

Sign In

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

Recommended Posts

GamerGun

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

bobbinsbro

Link to comment

Share on other sites

GamerGun

Link to comment

Share on other sites

bobbinsbro

Link to comment

Share on other sites

GamerGun

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information