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

bobbinsbro · November 5, 2008

ya, this is what you should be doing:

<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="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="00">00</option>
<option value="15">15</option>
<option value="30">30</option>
<option value="45">45</option>
</select>
EOT;
?>

<form method="post" action="<?php echo $_SERVER['PHP_SELF'] ?>" name="advanced">
<table border="1" bordercolor="#FFCC00" style="background-color:#FFFFCC" width="800" 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"><input type="text" size="25" name="spec" /></td>
   </tr>
   <tr>
      <td>Overige</td>
      <td colspan="2"><input type="text" size="25" name="overige" /></td>
   </tr>
   <tr>
      <td>Afdeling</td>
      <td><input type="text" size="25" 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 server access variables
   $host = "localhost";
   $user = "root";
   $pass = "password";
   $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");
   
   //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 * 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 overige
   if(!empty($_POST['overige'])){
      $sql[] = "overige = (SELECT overid FROM overige WHERE overige LIKE '".$_POST['overige']."%')";
   }
   
   //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']."%'";
   }
   
   //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:
         if (isset($dateVals['datum1'])) $sql[] = "datum > '".$dateVals['datum1']."'";
         else $sql[] = "datum < '".$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:
         if (isset($weekVals['week1'])) $sql[] = "datum > '".$weekVals['week1']."'";
         else $sql[] = "week < '".$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($_POST['uren'] != 0){
      $sql[] = "uren = '".$_POST['uren']."'";
   }
   
   //sql query bit for minuten
   if ($_POST['minuten'] != 0){
      $sql[] = "minuten = '".$_POST['minuten']."'";
   }
   
   
   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;
   
   //execute the query here
$result = mysql_connect("server", "username", "password");
$num = mysql_num_rows($result);

echo "<b><center>Database output</center></b><br /><br />";

   for($i = 0; $i < $num; ++$i) {

      $userid=mysql_result($result,$i,"userid");
      $specid=mysql_result($result,$i,"specid");
      $overid=mysql_result($result,$i,"overid");
      $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="15%">Voornaam</td>
      <td width="85%"><?php echo $werkArray[$userid]['voornaam']; ?></td>
   </tr>
   <tr>
      <td width="15%">Achternaam</td>
      <td width="85%"><?php echo $werkArray[$userid]['achternaam']; ?></td>
   </tr>
   <?php if ($overid){?>
   <tr>
      <td width="15%">Overige</td>
      <td width="85%"><?php echo $overigeArray[$overid]; ?></td>
   </tr>
   <?php }
   else if ($afdelingid && $specid && $callnr){
   ?>
   <tr>
      <td width="15%">Gerelateerd</td>
      <td width="85%"><?php echo $specArray[$specid]; ?></td>
   </tr>
   <tr>
      <td width="15%">Afdeling</td>
      <td width="85%"><?php echo $afdArray[$afdelingid]; ?></td>
   </tr>
   <tr>
      <td width="15%">Call nr</td>
      <td width="85%"><?php echo $callnr; ?></td>
   </tr>
   <?php 
   }//close if block
   ?>
   <tr>
      <td width="15%">Uren</td>
      <td width="85%"><?php echo $uren; ?></td>
   </tr>
   <tr>
      <td width="15%">Minuten</td>
      <td width="85%"><?php echo $minuten ?></td>
   </tr>
   <tr>
      <td width="15%">Omschrijving</td>
      <td width="85%"><?php echo $omschrijving; ?></td>
   </tr>
   <tr>
      <td width="15%">Datum (YYYY-MM-DD)</td>
      <td width="85%"><?php echo $datum; ?></td>
   </tr>
   <tr>
      <td width="15%">Week</td>
      <td width="85%"><?php echo $week; ?></td>
   </tr>
</table>

<?php }
} ?>

</body></html>

i added mysql_connect() and mysql_num_rows() on lines 254, 255 and put the whole search result code inside the if(isset($_POST['submit'])) block (otherwise, i think you get a load of errors). change the server, username and password in the mysql_connect() function to test.

GamerGun · November 5, 2008

Okay. Well, the results are still empty. Just "Database output"

Perhaps because it looks or 0 hours and 00 minutes? (Yes i tried cleaning the default values)

bobbinsbro · November 5, 2008

damn! debugging this will be difficult... i already account for the default values in the code (including uren and minuten being 0). that shouldn't cause any problems... did you try only searching for tommy?

EDIT: silly me! i forgot to add a mysql_query() call. add this $result = mysql_query($query); after the mysql_connect();

and mysql_connect() should not be saved in $result, so $result = mysql_connect(); should be just mysql_connect();

GamerGun · November 5, 2008

Yup. Tried some things. Also only Week 43 - 44 for example. All empty results.

The query is good, it just doesn't output

bobbinsbro · November 5, 2008

see edit in my last post.

GamerGun · November 5, 2008

You mean like this?

   //setup the query
   $query = implode(" AND ", $sql);
   $query = "SELECT * FROM uren WHERE ".$query;
   
   //execute the query here
   mysql_connect("localhost", "root", "password");
   $result = mysql_query($query);
   $num = mysql_num_rows($result);

This gives the same...

BTW, is this needed at all? (Which i added earlier, but i'm not sure about it, see between the -----------------------------)

//if search submitted
if(isset($_POST['submit'])){

   $errors = array(); //used to collect errors
   $sql = array(); //used to collect sql query bits

   // form submitted

-----------------------------

   // set server access variables
   $host = "localhost";
   $user = "root";
   $pass = "password";
   $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");

-----------------------------

   //set sql query bit for voornaam and achternaam
   if(!empty($_POST['voornaam']) && (strcasecmp($_POST['voornaam'], 'voornaam') != 0)){
      $arrayNaam = array("voornaam LIKE '%".$_POST['voornaam']."%'");
   }

Actual file:

<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="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="00">00</option>
<option value="15">15</option>
<option value="30">30</option>
<option value="45">45</option>
</select>
EOT;
?>

<form method="post" action="<?php echo $_SERVER['PHP_SELF'] ?>" name="advanced">
<table border="1" bordercolor="#FFCC00" style="background-color:#FFFFCC" width="800" 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"><input type="text" size="25" name="spec" /></td>
   </tr>
   <tr>
      <td>Overige</td>
      <td colspan="2"><input type="text" size="25" name="overige" /></td>
   </tr>
   <tr>
      <td>Afdeling</td>
      <td><input type="text" size="25" 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 server access variables
   $host = "localhost";
   $user = "root";
   $pass = "password";
   $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");
   
   //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 * 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 overige
   if(!empty($_POST['overige'])){
      $sql[] = "overige = (SELECT overid FROM overige WHERE overige LIKE '".$_POST['overige']."%')";
   }
   
   //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']."%'";
   }
   
   //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:
         if (isset($dateVals['datum1'])) $sql[] = "datum > '".$dateVals['datum1']."'";
         else $sql[] = "datum < '".$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:
         if (isset($weekVals['week1'])) $sql[] = "datum > '".$weekVals['week1']."'";
         else $sql[] = "week < '".$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($_POST['uren'] != 0){
      $sql[] = "uren = '".$_POST['uren']."'";
   }
   
   //sql query bit for minuten
   if ($_POST['minuten'] != 0){
      $sql[] = "minuten = '".$_POST['minuten']."'";
   }
   
   
   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;
   
   //execute the query here
   mysql_connect("localhost", "root", "password");
   $result = mysql_query($query);
   $num = mysql_num_rows($result);
   
echo "<b><center>Database output</center></b><br /><br />";

   for($i = 0; $i < $num; ++$i) {

      $userid=mysql_result($result,$i,"userid");
      $specid=mysql_result($result,$i,"specid");
      $overid=mysql_result($result,$i,"overid");
      $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="15%">Voornaam</td>
      <td width="85%"><?php echo $werkArray[$userid]['voornaam']; ?></td>
   </tr>
   <tr>
      <td width="15%">Achternaam</td>
      <td width="85%"><?php echo $werkArray[$userid]['achternaam']; ?></td>
   </tr>
   <?php if ($overid){?>
   <tr>
      <td width="15%">Overige</td>
      <td width="85%"><?php echo $overigeArray[$overid]; ?></td>
   </tr>
   <?php }
   else if ($afdelingid && $specid && $callnr){
   ?>
   <tr>
      <td width="15%">Gerelateerd</td>
      <td width="85%"><?php echo $specArray[$specid]; ?></td>
   </tr>
   <tr>
      <td width="15%">Afdeling</td>
      <td width="85%"><?php echo $afdArray[$afdelingid]; ?></td>
   </tr>
   <tr>
      <td width="15%">Call nr</td>
      <td width="85%"><?php echo $callnr; ?></td>
   </tr>
   <?php 
   }//close if block
   ?>
   <tr>
      <td width="15%">Uren</td>
      <td width="85%"><?php echo $uren; ?></td>
   </tr>
   <tr>
      <td width="15%">Minuten</td>
      <td width="85%"><?php echo $minuten ?></td>
   </tr>
   <tr>
      <td width="15%">Omschrijving</td>
      <td width="85%"><?php echo $omschrijving; ?></td>
   </tr>
   <tr>
      <td width="15%">Datum (YYYY-MM-DD)</td>
      <td width="85%"><?php echo $datum; ?></td>
   </tr>
   <tr>
      <td width="15%">Week</td>
      <td width="85%"><?php echo $week; ?></td>
   </tr>
</table>

<?php }
} ?>

</body></html>

Thanks

bobbinsbro · November 5, 2008

lol i am being brainless today

yes, put your code:

   // set server access variables
   $host = "localhost";
   $user = "root";
   $pass = "password";
   $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");

here (instead of the mysql_connect() function that i put there) :

   //execute the query here
   mysql_connect("localhost", "root", "password");
   $result = mysql_query($query);
   $num = mysql_num_rows($result);

also, i set the week/date querying to search for all weeks/dates bigger than week/date submitted if only the "begin" week/date is submitted. similar and opposite for "end" week/date. would you like this changed from "WHERE week/date </> $_POST['week/date']" to "WHERE week/date = $_POST['week/date']" ?

GamerGun · November 5, 2008

Yes that would be better i guess. But i think i made a mistake somewhere, because the result is still the same:

<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="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="00">00</option>
<option value="15">15</option>
<option value="30">30</option>
<option value="45">45</option>
</select>
EOT;
?>

<form method="post" action="<?php echo $_SERVER['PHP_SELF'] ?>" name="advanced">
<table border="1" bordercolor="#FFCC00" style="background-color:#FFFFCC" width="800" 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"><input type="text" size="25" name="spec" /></td>
   </tr>
   <tr>
      <td>Overige</td>
      <td colspan="2"><input type="text" size="25" name="overige" /></td>
   </tr>
   <tr>
      <td>Afdeling</td>
      <td><input type="text" size="25" 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 * 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 overige
   if(!empty($_POST['overige'])){
      $sql[] = "overige = (SELECT overid FROM overige WHERE overige LIKE '".$_POST['overige']."%')";
   }
   
   //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']."%'";
   }
   
   //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:
         if (isset($dateVals['datum1'])) $sql[] = "datum > '".$dateVals['datum1']."'";
         else $sql[] = "datum < '".$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:
         if (isset($weekVals['week1'])) $sql[] = "datum > '".$weekVals['week1']."'";
         else $sql[] = "week < '".$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($_POST['uren'] != 0){
      $sql[] = "uren = '".$_POST['uren']."'";
   }
   
   //sql query bit for minuten
   if ($_POST['minuten'] != 0){
      $sql[] = "minuten = '".$_POST['minuten']."'";
   }
   
   
   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;
   
   //execute the query here

   // set server access variables
   $host = "localhost";
   $user = "root";
   $pass = "password";
   $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");

   $result = mysql_query($query);
   $num = mysql_num_rows($result);
   
echo "<b><center>Database output</center></b><br /><br />";

   for($i = 0; $i < $num; ++$i) {

      $userid=mysql_result($result,$i,"userid");
      $specid=mysql_result($result,$i,"specid");
      $overid=mysql_result($result,$i,"overid");
      $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="15%">Voornaam</td>
      <td width="85%"><?php echo $werkArray[$userid]['voornaam']; ?></td>
   </tr>
   <tr>
      <td width="15%">Achternaam</td>
      <td width="85%"><?php echo $werkArray[$userid]['achternaam']; ?></td>
   </tr>
   <?php if ($overid){?>
   <tr>
      <td width="15%">Overige</td>
      <td width="85%"><?php echo $overigeArray[$overid]; ?></td>
   </tr>
   <?php }
   else if ($afdelingid && $specid && $callnr){
   ?>
   <tr>
      <td width="15%">Gerelateerd</td>
      <td width="85%"><?php echo $specArray[$specid]; ?></td>
   </tr>
   <tr>
      <td width="15%">Afdeling</td>
      <td width="85%"><?php echo $afdArray[$afdelingid]; ?></td>
   </tr>
   <tr>
      <td width="15%">Call nr</td>
      <td width="85%"><?php echo $callnr; ?></td>
   </tr>
   <?php 
   }//close if block
   ?>
   <tr>
      <td width="15%">Uren</td>
      <td width="85%"><?php echo $uren; ?></td>
   </tr>
   <tr>
      <td width="15%">Minuten</td>
      <td width="85%"><?php echo $minuten ?></td>
   </tr>
   <tr>
      <td width="15%">Omschrijving</td>
      <td width="85%"><?php echo $omschrijving; ?></td>
   </tr>
   <tr>
      <td width="15%">Datum (YYYY-MM-DD)</td>
      <td width="85%"><?php echo $datum; ?></td>
   </tr>
   <tr>
      <td width="15%">Week</td>
      <td width="85%"><?php echo $week; ?></td>
   </tr>
</table>

<?php }
} ?>

</body></html>

Thanks

bobbinsbro · November 5, 2008

ok. i fixed the date/week thing. i also discovered that i was selecting * from werknemers instead of just userid, which probably caused the problems. i hope this is fixed now...:

<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="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="00">00</option>
<option value="15">15</option>
<option value="30">30</option>
<option value="45">45</option>
</select>
EOT;
?>

<form method="post" action="<?php echo $_SERVER['PHP_SELF'] ?>" name="advanced">
<table border="1" bordercolor="#FFCC00" style="background-color:#FFFFCC" width="800" 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"><input type="text" size="25" name="spec" /></td>
   </tr>
   <tr>
      <td>Overige</td>
      <td colspan="2"><input type="text" size="25" name="overige" /></td>
   </tr>
   <tr>
      <td>Afdeling</td>
      <td><input type="text" size="25" 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 overige
   if(!empty($_POST['overige'])){
      $sql[] = "overige = (SELECT overid FROM overige WHERE overige LIKE '".$_POST['overige']."%')";
   }
   
   //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']."%'";
   }
   
   //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($_POST['uren'] != 0){
      $sql[] = "uren = '".$_POST['uren']."'";
   }
   
   //sql query bit for minuten
   if ($_POST['minuten'] != 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 = "password";
   $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");

   $result = mysql_query($query);
   $num = mysql_num_rows($result);
   
echo "<b><center>Database output</center></b><br /><br />";

   for($i = 0; $i < $num; ++$i) {

      $userid=mysql_result($result,$i,"userid");
      $specid=mysql_result($result,$i,"specid");
      $overid=mysql_result($result,$i,"overid");
      $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="15%">Voornaam</td>
      <td width="85%"><?php echo $werkArray[$userid]['voornaam']; ?></td>
   </tr>
   <tr>
      <td width="15%">Achternaam</td>
      <td width="85%"><?php echo $werkArray[$userid]['achternaam']; ?></td>
   </tr>
   <?php if ($overid){?>
   <tr>
      <td width="15%">Overige</td>
      <td width="85%"><?php echo $overigeArray[$overid]; ?></td>
   </tr>
   <?php }
   else if ($afdelingid && $specid && $callnr){
   ?>
   <tr>
      <td width="15%">Gerelateerd</td>
      <td width="85%"><?php echo $specArray[$specid]; ?></td>
   </tr>
   <tr>
      <td width="15%">Afdeling</td>
      <td width="85%"><?php echo $afdArray[$afdelingid]; ?></td>
   </tr>
   <tr>
      <td width="15%">Call nr</td>
      <td width="85%"><?php echo $callnr; ?></td>
   </tr>
   <?php 
   }//close if block
   ?>
   <tr>
      <td width="15%">Uren</td>
      <td width="85%"><?php echo $uren; ?></td>
   </tr>
   <tr>
      <td width="15%">Minuten</td>
      <td width="85%"><?php echo $minuten ?></td>
   </tr>
   <tr>
      <td width="15%">Omschrijving</td>
      <td width="85%"><?php echo $omschrijving; ?></td>
   </tr>
   <tr>
      <td width="15%">Datum (YYYY-MM-DD)</td>
      <td width="85%"><?php echo $datum; ?></td>
   </tr>
   <tr>
      <td width="15%">Week</td>
      <td width="85%"><?php echo $week; ?></td>
   </tr>
</table>

<?php }
} ?>

</body></html>

GamerGun · November 5, 2008

Cool, we have output!

Just one problem, these fields are empty:

Voornaam

Achternaam

Gerelateerd

Afdeling

Overige

See for yourself, go to records3.php and search for example on Tommy.

Thanks!

Edit; i guess i have to get rid of the $afdArray and such.

This:

   <tr>
      <td width="15%">Voornaam</td>
      <td width="85%"><?php echo $werkArray[$userid]['voornaam']; ?></td>
   </tr>
   <tr>
      <td width="15%">Achternaam</td>
      <td width="85%"><?php echo $werkArray[$userid]['achternaam']; ?></td>
   </tr>
   <?php if ($overid){?>
   <tr>
      <td width="15%">Overige</td>
      <td width="85%"><?php echo $overigeArray[$overid]; ?></td>
   </tr>
   <?php }
   else if ($afdelingid && $specid && $callnr){
   ?>
   <tr>
      <td width="15%">Gerelateerd</td>
      <td width="85%"><?php echo $specArray[$specid]; ?></td>
   </tr>
   <tr>
      <td width="15%">Afdeling</td>
      <td width="85%"><?php echo $afdArray[$afdelingid]; ?></td>
   </tr>

Will be something like this?

   <tr>
      <td width="15%">Voornaam</td>
      <td width="85%"><?php echo [$userid]['voornaam']; ?></td>
   </tr>
   <tr>
      <td width="15%">Achternaam</td>
      <td width="85%"><?php echo [$userid]['achternaam']; ?></td>
   </tr>
   <?php if ($overid){?>
   <tr>
      <td width="15%">Overige</td>
      <td width="85%"><?php echo $overid; ?></td>
   </tr>
   <?php }
   else if ($afdelingid && $specid && $callnr){
   ?>
   <tr>
      <td width="15%">Gerelateerd</td>
      <td width="85%"><?php echo $specid; ?></td>
   </tr>
   <tr>
      <td width="15%">Afdeling</td>
      <td width="85%"><?php echo $afdelingid; ?></td>
   </tr>

bobbinsbro · November 5, 2008

ya, i was expecting these to be empty, since we don't get the values from those tables in this file like we do in records2. you can just copy+paste the relevant code for now (paste it right under the database selection). as you can see, this file is full of code replication. this is why i think it would be best to combine records2 with records3. i have to go soon, so we can work on it tomorrow.

GamerGun · November 5, 2008

Ahh okay. My thinking was wrong, lol.

Will play with it! Hope we can get it in 1 file later yeh, Thanks for all!

Greetings

Edit; works

<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="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="00">00</option>
<option value="15">15</option>
<option value="30">30</option>
<option value="45">45</option>
</select>
EOT;
?>

<form method="post" action="<?php echo $_SERVER['PHP_SELF'] ?>" name="advanced">
<table border="1" bordercolor="#FFCC00" style="background-color:#FFFFCC" width="800" 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"><input type="text" size="25" name="spec" /></td>
   </tr>
   <tr>
      <td>Overige</td>
      <td colspan="2"><input type="text" size="25" name="overige" /></td>
   </tr>
   <tr>
      <td>Afdeling</td>
      <td><input type="text" size="25" 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 overige
   if(!empty($_POST['overige'])){
      $sql[] = "overige = (SELECT overid FROM overige WHERE overige LIKE '".$_POST['overige']."%')";
   }
   
   //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']."%'";
   }
   
   //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($_POST['uren'] != 0){
      $sql[] = "uren = '".$_POST['uren']."'";
   }
   
   //sql query bit for minuten
   if ($_POST['minuten'] != 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 = "pass";
   $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',
             'over' => 'SELECT * FROM overige',
             '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 'over':
            $overigeArray = array();
            while(list($overid, $overige) = mysql_fetch_array($result)){
               $overigeArray[$overid] = $overige;
            }
            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 />";

   for($i = 0; $i < $num; ++$i) {

      $userid=mysql_result($result,$i,"userid");
      $specid=mysql_result($result,$i,"specid");
      $overid=mysql_result($result,$i,"overid");
      $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="15%">Voornaam</td>
      <td width="85%"><?php echo $werkArray[$userid]['voornaam']; ?></td>
   </tr>
   <tr>
      <td width="15%">Achternaam</td>
      <td width="85%"><?php echo $werkArray[$userid]['achternaam']; ?></td>
   </tr>
   <?php if ($overid){?>
   <tr>
      <td width="15%">Overige</td>
      <td width="85%"><?php echo $overigeArray[$overid]; ?></td>
   </tr>
   <?php }
   else if ($afdelingid && $specid && $callnr){
   ?>
   <tr>
      <td width="15%">Gerelateerd</td>
      <td width="85%"><?php echo $specArray[$specid]; ?></td>
   </tr>
   <tr>
      <td width="15%">Afdeling</td>
      <td width="85%"><?php echo $afdArray[$afdelingid]; ?></td>
   </tr>
   <tr>
      <td width="15%">Call nr</td>
      <td width="85%"><?php echo $callnr; ?></td>
   </tr>
   <?php 
   }//close if block
   ?>
   <tr>
      <td width="15%">Uren</td>
      <td width="85%"><?php echo $uren; ?></td>
   </tr>
   <tr>
      <td width="15%">Minuten</td>
      <td width="85%"><?php echo $minuten ?></td>
   </tr>
   <tr>
      <td width="15%">Omschrijving</td>
      <td width="85%"><?php echo $omschrijving; ?></td>
   </tr>
   <tr>
      <td width="15%">Datum (YYYY-MM-DD)</td>
      <td width="85%"><?php echo $datum; ?></td>
   </tr>
   <tr>
      <td width="15%">Week</td>
      <td width="85%"><?php echo $week; ?></td>
   </tr>
</table>

<?php }
} ?>

</body></html>

bobbinsbro · November 5, 2008

awesome.

i made 1 more mistake - i don't allow the user to search for uren = 0 or minuten = 00. i will fix this tomorrow. off to bed now. good night.

GamerGun · November 5, 2008

Great. Good night!

Edit; correct! Something else; "Overige" is not working. We'll see tomorrow tho

GamerGun · November 6, 2008

Perhaps we should drop records2.php, what do u think?

Because records3.php can do the same as records2.php, but it can do more too.

The idea from 2 was to let users search for what they submitted, and 3 to create reports.

I guess this is in 1 file now (3) but i'd like to know what u think

GamerGun · November 6, 2008

Great. Good night!

Edit; correct! Something else; "Overige" is not working. We'll see tomorrow tho

I fixed "Overige".

Had to be:

$sql[] = "overid = (SELECT overid FROM overige WHERE overige LIKE '".$_POST['overige']."%')";

Instead of:

$sql[] = "overige = (SELECT overid FROM overige WHERE overige LIKE '".$_POST['overige']."%')";

GamerGun · November 6, 2008

Damn, why can you only edit your post for a few times or within a certain time?

BTW, in addition to the hours/minutes bug; when you search for 0 hour 15 minutes, you get all the results with * hours and 15 minutes. So also 1.15 for example. Same the other way around. 1 hour 00 minutes gives all with 1 hour, no matter what the minutes are.

Anyhow, i tested and thought about some things. My view at the moment:

Get rid of records2.php, no need for a light search since you can use records3.php with only 1 field filled in for example. So use records3.php for searching, and records4.php for reporting. (Just made that one, see for yourself).

Records3 will look the same as records4, but with records4 only calls will be shown and no description since the output is slightly different for printing it to paper. Also at records4 should be a "totals" overview at the end of the results. Like; employee Y worked *amount* hours. Department Y has *amount* hours work spend on. And some more. I guess this should be possible, just don't know exactly how.

So i have to remove the "Overige" from records4. I already removed the query, fields and such but when you (for example) search for "Voornaam" Tommy you still get the "Overige" along with the calls. I have to get rid of that. To make it more clear, here is a overview:

Records3.php (Nothing wrong with this one, except for the hours/minutes bug)

<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="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="00">00</option>
<option value="15">15</option>
<option value="30">30</option>
<option value="45">45</option>
</select>
EOT;
?>

<form method="post" action="<?php echo $_SERVER['PHP_SELF'] ?>" name="advanced">
<table border="1" bordercolor="#FFCC00" style="background-color:#FFFFCC" width="800" 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"><input type="text" size="25" name="spec" /></td>
   </tr>
   <tr>
      <td>Overige</td>
      <td colspan="2"><input type="text" size="25" name="overige" /></td>
   </tr>
   <tr>
      <td>Afdeling</td>
      <td><input type="text" size="25" 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 overige
   if(!empty($_POST['overige'])){
      $sql[] = "overid = (SELECT overid FROM overige WHERE overige LIKE '".$_POST['overige']."%')";
   }
   
   //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']."%'";
   }
   
   //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($_POST['uren'] != 0){
      $sql[] = "uren = '".$_POST['uren']."'";
   }
   
   //sql query bit for minuten
   if ($_POST['minuten'] != 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',
             'over' => 'SELECT * FROM overige',
             '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 'over':
            $overigeArray = array();
            while(list($overid, $overige) = mysql_fetch_array($result)){
               $overigeArray[$overid] = $overige;
            }
            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 />";

   for($i = 0; $i < $num; ++$i) {

      $userid=mysql_result($result,$i,"userid");
      $specid=mysql_result($result,$i,"specid");
      $overid=mysql_result($result,$i,"overid");
      $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="15%">Voornaam</td>
      <td width="85%"><?php echo $werkArray[$userid]['voornaam']; ?></td>
   </tr>
   <tr>
      <td width="15%">Achternaam</td>
      <td width="85%"><?php echo $werkArray[$userid]['achternaam']; ?></td>
   </tr>
   <?php if ($overid){?>
   <tr>
      <td width="15%">Overige</td>
      <td width="85%"><?php echo $overigeArray[$overid]; ?></td>
   </tr>
   <?php }
   else if ($afdelingid && $specid && $callnr){
   ?>
   <tr>
      <td width="15%">Gerelateerd</td>
      <td width="85%"><?php echo $specArray[$specid]; ?></td>
   </tr>
   <tr>
      <td width="15%">Afdeling</td>
      <td width="85%"><?php echo $afdArray[$afdelingid]; ?></td>
   </tr>
   <tr>
      <td width="15%">Call nr</td>
      <td width="85%"><?php echo $callnr; ?></td>
   </tr>
   <?php 
   }//close if block
   ?>
   <tr>
      <td width="15%">Uren</td>
      <td width="85%"><?php echo $uren; ?></td>
   </tr>
   <tr>
      <td width="15%">Minuten</td>
      <td width="85%"><?php echo $minuten ?></td>
   </tr>
   <tr>
      <td width="15%">Omschrijving</td>
      <td width="85%"><?php echo $omschrijving; ?></td>
   </tr>
   <tr>
      <td width="15%">Datum (YYYY-MM-DD)</td>
      <td width="85%"><?php echo $datum; ?></td>
   </tr>
   <tr>
      <td width="15%">Week</td>
      <td width="85%"><?php echo $week; ?></td>
   </tr>
</table><br>

<?php }
} ?>

</body></html>

Input:

Output:

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="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="00">00</option>
<option value="15">15</option>
<option value="30">30</option>
<option value="45">45</option>
</select>
EOT;
?>

<form method="post" action="<?php echo $_SERVER['PHP_SELF'] ?>" name="advanced">
<table border="1" bordercolor="#FFCC00" style="background-color:#FFFFCC" width="800" 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"><input type="text" size="25" name="spec" /></td>
   </tr>
   <tr>
      <td>Afdeling</td>
      <td><input type="text" size="25" 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']."%'";
   }
   
   //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($_POST['uren'] != 0){
      $sql[] = "uren = '".$_POST['uren']."'";
   }
   
   //sql query bit for minuten
   if ($_POST['minuten'] != 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>

Current input:

Current output:

New output should be like this:

I hope that i'm clear and that i'm not bothering you too much.

Thanks

bobbinsbro · November 6, 2008

hi. had a busy morning.

i think you are right about getting rid of records2, just make sure records3 work 100% before deleting the old file.

i've been thinking about the uren and minuten bug, and i think that you will have to add a a default option:

<option value=""></option>

to both for best results.

i'll explain: at the moment, i the code ignores values of 0 for the time. but you want to be able to search for 0 value only. but if the script does search for 0, and 0 is the default value, you will have trouble searching for only 3 hours, because the script will always search for 0 minutes as well, and you will not get entries for 3:15, 3:30, 3:45. if you agree with me on thins, add the default empty option, and change line 215 to:

if (!empty($_POST['uren'])) {

and line 220 to:

if (!empty($_POST['minuten'])){

hopefully, that will work and not expose any new bugs. i'll have a look at records4 now.

bobbinsbro · November 6, 2008

i'm not sure this will work as a fix for records4, but it's simple enough to be worth a shot. add this line:

   else $sql[] = "callnr != 0";

at line 156, right after this bit of code:

   //set sql query bit for callnr
   if(!empty($_POST['callnr'])){
      $sql[] = "callnr LIKE '%".$_POST['callnr']."%'";
   }

and test. i'm hoping that since we set callnr to 0 whenever overige != 0, that this will eliminate the overige lines showing up.

GamerGun · November 6, 2008

For records3:

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

   //sql query bit for uren
if (!empty($_POST['uren'])) {
      $sql[] = "uren = '".$_POST['uren']."'";
   }
   
   //sql query bit for minuten
if (!empty($_POST['minuten'])){
      $sql[] = "minuten = '".$_POST['minuten']."'";
   }

Is not doing exactly what it should do. Search for 0:15 for example.

For your records4 fix; looks like it works

Thanks!

bobbinsbro · November 6, 2008

try changing this:

if (!empty($_POST['uren'])) {

to this:

if (!empty($_POST['uren']) && ($_POST['uren'] !== 0)) {

the double == is no a typo. it's important!

do the same for minuten and test.

GamerGun · November 6, 2008

Still having the same. Also for records4 btw. Thanks for helping tho!

bobbinsbro · November 6, 2008

what did you do to the minuten? that field works with 00... try 3:00 and you won't get 3:15. whatever it is you did there, we should do the same for uren.

GamerGun · November 6, 2008

Thats correct. 3:00 for example is working. 0:15 not as known.

records3.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;
?>

<form method="post" action="<?php echo $_SERVER['PHP_SELF'] ?>" name="advanced">
<table border="1" bordercolor="#FFCC00" style="background-color:#FFFFCC" width="800" 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"><input type="text" size="25" name="spec" /></td>
   </tr>
   <tr>
      <td>Overige</td>
      <td colspan="2"><input type="text" size="25" name="overige" /></td>
   </tr>
   <tr>
      <td>Afdeling</td>
      <td><input type="text" size="25" 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 overige
   if(!empty($_POST['overige'])){
      $sql[] = "overid = (SELECT overid FROM overige WHERE overige LIKE '".$_POST['overige']."%')";
   }
   
   //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']."%'";
   }
   
   //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']) && ($_POST['uren'] !== 0)) {
      $sql[] = "uren = '".$_POST['uren']."'";
   }
   
   //sql query bit for minuten
if (!empty($_POST['minuten']) && ($_POST['minuten'] !== 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 = "password";
   $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',
             'over' => 'SELECT * FROM overige',
             '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 'over':
            $overigeArray = array();
            while(list($overid, $overige) = mysql_fetch_array($result)){
               $overigeArray[$overid] = $overige;
            }
            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 />";

   for($i = 0; $i < $num; ++$i) {

      $userid=mysql_result($result,$i,"userid");
      $specid=mysql_result($result,$i,"specid");
      $overid=mysql_result($result,$i,"overid");
      $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="15%">Voornaam</td>
      <td width="85%"><?php echo $werkArray[$userid]['voornaam']; ?></td>
   </tr>
   <tr>
      <td width="15%">Achternaam</td>
      <td width="85%"><?php echo $werkArray[$userid]['achternaam']; ?></td>
   </tr>
   <?php if ($overid){?>
   <tr>
      <td width="15%">Overige</td>
      <td width="85%"><?php echo $overigeArray[$overid]; ?></td>
   </tr>
   <?php }
   else if ($afdelingid && $specid && $callnr){
   ?>
   <tr>
      <td width="15%">Gerelateerd</td>
      <td width="85%"><?php echo $specArray[$specid]; ?></td>
   </tr>
   <tr>
      <td width="15%">Afdeling</td>
      <td width="85%"><?php echo $afdArray[$afdelingid]; ?></td>
   </tr>
   <tr>
      <td width="15%">Call nr</td>
      <td width="85%"><?php echo $callnr; ?></td>
   </tr>
   <?php 
   }//close if block
   ?>
   <tr>
      <td width="15%">Uren</td>
      <td width="85%"><?php echo $uren; ?></td>
   </tr>
   <tr>
      <td width="15%">Minuten</td>
      <td width="85%"><?php echo $minuten ?></td>
   </tr>
   <tr>
      <td width="15%">Omschrijving</td>
      <td width="85%"><?php echo $omschrijving; ?></td>
   </tr>
   <tr>
      <td width="15%">Datum (YYYY-MM-DD)</td>
      <td width="85%"><?php echo $datum; ?></td>
   </tr>
   <tr>
      <td width="15%">Week</td>
      <td width="85%"><?php echo $week; ?></td>
   </tr>
</table><br>

<?php }
} ?>

</body></html>

bobbinsbro · November 6, 2008

fixed:

   //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']."'";
   }

to get records4 working, add the empty options to the select fields, and change the query code to the code i pasted here.

Sign In

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

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

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

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