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

GamerGun · November 4, 2008

<html>
<head>

<STYLE TYPE="text/css">
<!--
TD{font-size: 11pt;}
--->
</STYLE>

</head>
<body>
<basefont face="Arial">

<form method="post" action="<?php echo $_SERVER['PHP_SELF'] ?>">
   Zoeken <input type="text" name="search"><br />
   <select size="1" name="dropdown">
      <option value="" selected>Zoek op...</option>
      <option value="userid">Medewerker</option>
      <option value="specid">Gerelateerd</option>
      <option value="overid">Overige</option>
      <option value="afdelingid">Afdeling</option>
      <option value="uren">Uren</option>
      <option value="minuten">Minuten</option>
      <option value="omschrijving">Omschrijving</option>
      <option value="callnr">Call nr</option>
      <option value="datum">Datum</option>
      <option value="week">Week</option>
   </select>
   <input type="Submit" value="Verstuur" name="Submit" />
</form>

<?php
error_reporting(E_ALL);

if (!isset($_POST['Submit'])) {
   // form not submitted
}
else {

   // form submitted
   // set server access variables
   $host = "localhost";
   $user = "root";
   $pass = "password";
   $db = "urendatabase";

   $search = empty($_POST['search'])? die ("ERROR: Je moet wel iets invullen") : mysql_escape_string($_POST['search']);
   $dropdown = empty($_POST['dropdown'])? die ("ERROR: Je moet wel een keuze maken") : mysql_escape_string($_POST['dropdown']);

   // 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

   $werknaam = explode(" ", $_POST['search']);

   $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($result4)){
               $specArray[$specid] = $specificatie;
            }
            break;
         default:
            die('there\'s a bug in the system!');
      }
      unset($result);
   }

   //Create search query
   $query = "select * FROM uren where userid = (SELECT userid FROM werknemers WHERE voornaam LIKE ".$werknaam[0]."% AND achternaam LIKE %."end($werknaam).")";

   $result = mysql_query($query) or die (mysql_error());

   $num=mysql_numrows($result);

   mysql_close($connect);

   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%">User ID</td>
      <td width="85%"><?php echo $userid; ?></td>
   </tr>
   <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>
   <tr>
      <td width="15%">Spec ID</td>
      <td width="85%"><?php echo $specArray[$specid]; ?></td>
   </tr>
   <tr>
      <td width="15%">Over ID</td>
      <td width="85%"><?php echo $overigeArray[$overid]; ?></td>
   </tr>
   <tr>
      <td width="15%">Afdeling ID</td>
      <td width="85%"><?php echo $afdArray[$afdelingid]; ?></td>
   </tr>
   <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%">Call nr</td>
      <td width="85%"><?php echo $callnr; ?></td>
   </tr>
   <tr>
      <td width="15%">Datum (YY/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
   } //close for loop
} //close if block
?>

</body>
</html>

bobbinsbro · November 4, 2008

oh, sorry. my mistake. i forgot to use ' ' around the searches inside the nested select. and i added your fix for my misplacing the period. try this:

$query = "SELECT * FROM uren WHERE userid = (SELECT userid FROM werknemers WHERE voornaam LIKE '".$werknaam[0]."%' AND acthernaam LIKE '%".end($werknaam)."')";

if this doesn't work, try (added ' ' around the nested select so that the value returned to userid is a string, which may be required):

$query = "SELECT * FROM uren WHERE userid = '(SELECT userid FROM werknemers WHERE voornaam LIKE '".$werknaam[0]."%' AND acthernaam LIKE '%".end($werknaam)."')'";

will be back after lunch

GamerGun · November 4, 2008

Your first one works Now it presents the data when Tommy van Extel is submitted.

bobbinsbro · November 4, 2008

i realized i broke all the other search options with the query i gave you (sorry :-\ ), so here's the fixed code:

<html>
<head>

<STYLE TYPE="text/css">
<!--
TD{font-size: 11pt;}
--->
</STYLE>

</head>
<body>
<basefont face="Arial">

<form method="post" action="<?php echo $_SERVER['PHP_SELF'] ?>">
   Zoeken <input type="text" name="search"><br />
   <select size="1" name="dropdown">
      <option value="" selected>Zoek op...</option>
      <option value="userid">Medewerker</option>
      <option value="specid">Gerelateerd</option>
      <option value="overid">Overige</option>
      <option value="afdelingid">Afdeling</option>
      <option value="uren">Uren</option>
      <option value="minuten">Minuten</option>
      <option value="omschrijving">Omschrijving</option>
      <option value="callnr">Call nr</option>
      <option value="datum">Datum</option>
      <option value="week">Week</option>
   </select>
   <input type="Submit" value="Verstuur" name="Submit" />
</form>

<?php
error_reporting(E_ALL);

if (!isset($_POST['Submit'])) {
   // form not submitted
}
else {

   // form submitted
   // set server access variables
   $host = "localhost";
   $user = "root";
   $pass = "password";
   $db = "urendatabase";

   $search = empty($_POST['search'])? die ("ERROR: Je moet wel iets invullen") : mysql_escape_string($_POST['search']);
   $dropdown = empty($_POST['dropdown'])? die ("ERROR: Je moet wel een keuze maken") : mysql_escape_string($_POST['dropdown']);

   // 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);
   }

   //Create search query
   if ($dropdown == 'userid'){ //if search is user name
   	   $werknaam = explode(" ", $search);
   	      $query = "SELECT * FROM uren WHERE userid =
   	      			(SELECT userid FROM werknemers WHERE
   	      					voornaam LIKE '".$werknaam[0]."%' AND achternaam LIKE '%".end($werknaam)."')";
   }
   else{//for everything else
	$query = "SELECT * FROM uren WHERE $dropdown='$search'";
   }

   $result = mysql_query($query) or die (mysql_error());

   $num=mysql_numrows($result);

   mysql_close($connect);

   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%">User ID</td>
      <td width="85%"><?php echo $userid; ?></td>
   </tr>
   <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%">Over ID</td>
      <td width="85%"><?php echo $overigeArray[$overid]; ?></td>
   </tr>
   <?php }
   else if ($afdelingid && $specid && $callnr){
   ?>
   <tr>
      <td width="15%">Spec ID</td>
      <td width="85%"><?php echo $specArray[$specid]; ?></td>
   </tr>
   <tr>
      <td width="15%">Afdeling ID</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
   } //close for loop
} //close if block
?>

</body>
</html>

i also forgot to mention that the name search is not 100% accurate, since the sql query only checks the first word in the first name and the last word in the last name. so if you have 2 guyz, 1 named tommy van extel and one named tommy lee van extel (or tommy extel), the query will return data for both people in the same search. i don't think there's a way around this unless you have 2 text fields for name searches - 1 for first name and 1 for last name.

EDIT: fixed typo in the code that made some of the result table disappear.

EDIT2: fixed another typo that caused specID to always be empty.

GamerGun · November 4, 2008

Thanks. The only problem now is that no spec+departmentcallnr or overige (other) are being outputted.

See my page, and use my name (Tommy van Extel) at "Medewerker".

Edit; didn't saw your edit. Let me try

Edit 2; it works now. Is it also possible to do this for "Gerelateerd" (= specid), Overige" (= overid) and "Afdeling" (= $afdelingid)?

bobbinsbro · November 4, 2008

sure, it would be done in a similar way. but to you really want people to have to search for "885190 EM&C Competence Center"... ? isn't using id codes easier on the user?

GamerGun · November 4, 2008

Well my idea is just to use a LIKE so when people enter Competence it shows up all departments containing Competence.

Same for other fields like "Omschrijving" (Description) and such.

Thanks

bobbinsbro · November 4, 2008

ok. well, the way to do this is similar to what i did with the names. just don't explode $search and use wild cards on both sides of the search term:

"...LIKE '%$search%'..."

you will have to check what $dorpdown is, because for afdeling, specificatie and overige, you have to add a nested select (like i did with names) to look up the id in another table, but for callnr, omch...( :-\ i'll never get that 1), uren, minuten, date, week... the lookup is in then uren table, so there will be no nested select.

i have to go now. try it out, and post any problems you have. i'll be back in ~2 hours.

GamerGun · November 4, 2008

Thanks

Well, i have this now:

<html>
<head>

<STYLE TYPE="text/css">
<!--
TD{font-size: 11pt;}
--->
</STYLE>

</head>
<body>
<basefont face="Arial">

<form method="post" action="<?php echo $_SERVER['PHP_SELF'] ?>">
   Zoeken <input type="text" name="search"><br />
   <select size="1" name="dropdown">
      <option value="" selected>Zoek op...</option>
      <option value="userid">Medewerker</option>
      <option value="specid">Gerelateerd</option>
      <option value="overid">Overige</option>
      <option value="afdelingid">Afdeling</option>
      <option value="uren">Uren</option>
      <option value="minuten">Minuten</option>
      <option value="omschrijving">Omschrijving</option>
      <option value="callnr">Call nr</option>
      <option value="datum">Datum</option>
      <option value="week">Week</option>
   </select>
   <input type="Submit" value="Verstuur" name="Submit" />
</form>

<?php
error_reporting(E_ALL);

if (!isset($_POST['Submit'])) {
   // form not submitted
}
else {

   // form submitted
   // set server access variables
   $host = "localhost";
   $user = "root";
   $pass = "password";
   $db = "urendatabase";

   $search = empty($_POST['search'])? die ("ERROR: Je moet wel iets invullen") : mysql_escape_string($_POST['search']);
   $dropdown = empty($_POST['dropdown'])? die ("ERROR: Je moet wel een keuze maken") : mysql_escape_string($_POST['dropdown']);

   // 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);
   }

   //Create search query
   if ($dropdown == 'userid'){ //if search is user name
         $werknaam = explode(" ", $search);
            $query = "SELECT * FROM uren WHERE userid =
                     (SELECT userid FROM werknemers WHERE
                           voornaam LIKE '".$werknaam[0]."%' AND achternaam LIKE '%".end($werknaam)."')";
   }
   else
   if ($dropdown == 'specid'){ //if search is spec
         $specnaam = explode(" ", $search);
            $query = "SELECT * FROM uren WHERE specid =
                     (SELECT specid FROM specificatie WHERE
                           specificatie LIKE '".$specnaam[0]."%')";
   }
   else
   if ($dropdown == 'overid'){ //if search is overige
         $overnaam = explode(" ", $search);
            $query = "SELECT * FROM uren WHERE overid =
                     (SELECT overid FROM overige WHERE
                           overige LIKE '".$overnaam[0]."%')";
   }
   else
   if ($dropdown == 'afdelingid'){ //if search is afdeling
         $afdelingnaam = explode(" ", $search);
            $query = "SELECT * FROM uren WHERE afdelingid =
                     (SELECT afdelingid FROM afdelingen WHERE
                           afdeling LIKE '".$afdelingnaam[0]."%')";
   }
   else{//for everything else
      $query = "SELECT * FROM uren WHERE $dropdown LIKE '%$search%'";
   }

   $result = mysql_query($query) or die (mysql_error());

   $num=mysql_numrows($result);

   mysql_close($connect);

   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
   } //close for loop
} //close if block
?>

</body>
</html>

It words except for "Afdeling", and i can't see why. Any idea? All the other fields works as far as i tested.

Edit; it works when you do "885910 Process technology" or "885910 " but not with "Process" or "technology"

BTW, when you fill in CAD at "Gerelateerd", it says: Subquery returns more than 1 row. Maybe because i have both CAD-E and CAD-S

Thanks

bobbinsbro · November 4, 2008

change:

   //Create search query
   if ($dropdown == 'userid'){ //if search is user name
         $werknaam = explode(" ", $search);
            $query = "SELECT * FROM uren WHERE userid =
                     (SELECT userid FROM werknemers WHERE
                           voornaam LIKE '".$werknaam[0]."%' AND achternaam LIKE '%".end($werknaam)."')";
   }
   else
   if ($dropdown == 'specid'){ //if search is spec
         $specnaam = explode(" ", $search);
            $query = "SELECT * FROM uren WHERE specid =
                     (SELECT specid FROM specificatie WHERE
                           specificatie LIKE '".$specnaam[0]."%')";
   }
   else
   if ($dropdown == 'overid'){ //if search is overige
         $overnaam = explode(" ", $search);
            $query = "SELECT * FROM uren WHERE overid =
                     (SELECT overid FROM overige WHERE
                           overige LIKE '".$overnaam[0]."%')";
   }
   else
   if ($dropdown == 'afdelingid'){ //if search is afdeling
         $afdelingnaam = explode(" ", $search);
            $query = "SELECT * FROM uren WHERE afdelingid =
                     (SELECT afdelingid FROM afdelingen WHERE
                           afdeling LIKE '".$afdelingnaam[0]."%')";
   }
   else{//for everything else
      $query = "SELECT * FROM uren WHERE $dropdown LIKE '%$search%'";
   }

   $result = mysql_query($query) or die (mysql_error());

to this:

//Create search query
switch($dropdown){
	case 'userid'://if search is user name
		$werknaam = explode(" ", $search);
            $query = "SELECT * FROM uren WHERE userid =
                     (SELECT userid FROM werknemers WHERE
                           voornaam LIKE '".$werknaam[0]."%' AND achternaam LIKE '%".end($werknaam)."')";
		break;
	case 'specid': //if search is spec
            $query = "SELECT * FROM uren WHERE specid =
                     (SELECT specid FROM specificatie WHERE
                           specificatie LIKE '$search%')";
            break;
	case 'overid': //if search is overige
            $query = "SELECT * FROM uren WHERE overid =
                     (SELECT overid FROM overige WHERE
                           overige LIKE '$search%')";
            $break;
	case 'afdelingid': //if search is afdeling
            $query = "SELECT * FROM uren WHERE afdelingid =
                     (SELECT afdelingid FROM afdelingen WHERE
                           afdeling LIKE '%$search%')";
		break;
	default: //everything else
      		$query = "SELECT * FROM uren WHERE $dropdown LIKE '%$search%'";
      		break;
   }
   
   $result = @mysql_query($query);
   if (mysql_errorno() == 1242){//error code for "more than 1 row" mysql error
   		die("Your search matches more than one value. Please modify the search term to match only one value.");
   }
   else{//for all other errors, show the mysql error msg
   		die(mysql_error());
   }

i turned the if-elseif block into a switch block (i read somewhere that it's faster in php, and it looks better). also, there was no need to explode anything other than the name, because only the name has to match 2 table columns.

afdeling didn't match everything because you didn't put a wild card before the search, only after, so mysql expected the search string to begin with 885910.

you're right about the reason specid errors on CAD, and there's nothing to do about that, but i added an error check, and the user will see a friendly msg explaining what they did wrong and how to fix it. (you may want to change the language/contents of the msg... it's the first die() in the if-else block at the bottom of the code).

i set up both the overige and gerelateerd search queries so that mysql will only recognize searches of strings that begin the same way as values in the tables. for example, a gerelateerd search for "LI" (or "li") will return all etnries of LINUX, but a search for "INUX" (or "inux") will return nothing.

GamerGun · November 4, 2008

Thanks for your explanation.

Tried your code but nothing sends output now. Not even the "Database output" word.

<html>
<head>

<STYLE TYPE="text/css">
<!--
TD{font-size: 11pt;}
--->
</STYLE>

</head>
<body>
<basefont face="Arial">

<form method="post" action="<?php echo $_SERVER['PHP_SELF'] ?>">
   Zoeken <input type="text" name="search"><br />
   <select size="1" name="dropdown">
      <option value="" selected>Zoek op...</option>
      <option value="userid">Medewerker</option>
      <option value="specid">Gerelateerd</option>
      <option value="overid">Overige</option>
      <option value="afdelingid">Afdeling</option>
      <option value="uren">Uren</option>
      <option value="minuten">Minuten</option>
      <option value="omschrijving">Omschrijving</option>
      <option value="callnr">Call nr</option>
      <option value="datum">Datum</option>
      <option value="week">Week</option>
   </select>
   <input type="Submit" value="Verstuur" name="Submit" />
</form>

<?php
error_reporting(E_ALL);

if (!isset($_POST['Submit'])) {
   // form not submitted
}
else {

   // form submitted
   // set server access variables
   $host = "localhost";
   $user = "root";
   $pass = "password";
   $db = "urendatabase";

   $search = empty($_POST['search'])? die ("ERROR: Je moet wel iets invullen") : mysql_escape_string($_POST['search']);
   $dropdown = empty($_POST['dropdown'])? die ("ERROR: Je moet wel een keuze maken") : mysql_escape_string($_POST['dropdown']);

   // 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);
   }

//Create search query
   switch($dropdown){
      case 'userid'://if search is user name
         $werknaam = explode(" ", $search);
            $query = "SELECT * FROM uren WHERE userid =
                     (SELECT userid FROM werknemers WHERE
                           voornaam LIKE '".$werknaam[0]."%' AND achternaam LIKE '%".end($werknaam)."')";
         break;
      case 'specid': //if search is spec
            $query = "SELECT * FROM uren WHERE specid =
                     (SELECT specid FROM specificatie WHERE
                           specificatie LIKE '$search%')";
            break;
      case 'overid': //if search is overige
            $query = "SELECT * FROM uren WHERE overid =
                     (SELECT overid FROM overige WHERE
                           overige LIKE '$search%')";
            $break;
      case 'afdelingid': //if search is afdeling
            $query = "SELECT * FROM uren WHERE afdelingid =
                     (SELECT afdelingid FROM afdelingen WHERE
                           afdeling LIKE '%$search%')";
         break;
      default: //everything else
            $query = "SELECT * FROM uren WHERE $dropdown LIKE '%$search%'";
            break;
   }
      
   $result = @mysql_query($query);
   if (mysql_errorno() == 1242){//error code for "more than 1 row" mysql error
         die("Your search matches more than one value. Please modify the search term to match only one value.");
   }
   else{//for all other errors, show the mysql error msg
         die(mysql_error());
   }

   $num=mysql_numrows($result);

   mysql_close($connect);

   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
   } //close for loop
} //close if block
?>

</body>
</html>

Apart from that, i'm trying to build something which can be used to filter.

So i placed this bit of code above the output:

<form method="post" action="<?php echo $_SERVER['PHP_SELF'] ?>">
	Naam: <input type="checkbox" name="naam" value="naam"  />
	Overige: <input type="checkbox" name="overige" value="overige"  />
	Gerelateerd: <input type="checkbox" name="gerelateerd" value="gerelateerd"  />
	Afdeling: <input type="checkbox" name="afdeling" value="afdeling"  />
	Call nummer: <input type="checkbox" name="callnr" value="callnr"  />
	Uren: <input type="checkbox" name="uren" value="uren"  />
	Minuten: <input type="checkbox" name="minuten" value="minuten"  />
	Omschrijving: <input type="checkbox" name="omschrijving" value="omschrijving"  />
	Datum: <input type="checkbox" name="datum" value="datum"  />
	Week: <input type="checkbox" name="week" value="week"  />
<input type="hidden" name="dropdown" value="<?php echo $_POST['dropdown']; ?>">
<input type="hidden" name="search" value="<?php echo $_POST['search']; ?>">
<input type="Submit" value="Verstuur" name="Submit" />
</form>

Via that way i want to select which fields are being outputted.

For example, someone searches for my name (Tommy van Extel), and sees all my work hours because default are the check boxes enabled. (Not yet shown in the code above).

Now, that someone only wants to see my name and the departments i worked for. So he/she disables all check boxes except for "Naam" and "Afdelingen".

Somehow, i guess via hidden input fields and $vars, the other fields disappear and only "Naam" (voornaam+achternaam) and "Afdelingen" remain left.

I also want to place a print button so the current view can be printed. (I know how to do that).

Any help is appreciated.

Thanks

bobbinsbro · November 4, 2008

ok, here's how i broke your output . this bit of code:

   $result = @mysql_query($query);
   if (mysql_errorno() == 1242){//error code for "more than 1 row" mysql error
         die("Your search matches more than one value. Please modify the search term to match only one value.");
   }
   else{//for all other errors, show the mysql error msg
         die(mysql_error());
   }

tells php to die no matter what happens (that's what the else does). sorry... it should be:

   $result = @mysql_query($query);
   $errno = mysql_errno();
   if ($errno == 1242){//error code for "more than 1 row" mysql error
         die("Your search matches more than one value. Please modify the search term to match only one value.");
   }
   else if ($errno != 0){//for all other errors, show the mysql error msg
         die(mysql_error());
   }

this now makes sure there is actually an error while fetching the query results, and only dies if it finds one.

regarding the second bit, that would be done with javascript. i'll look into the best way to do that, since i'm not sure whether just setting the unchecked <tr></tr> to invisible will get rid of the row, or just make a white hole in your table. i will do some testing and let you know.

GamerGun · November 5, 2008

I see now, thanks, that works

Will be curious to see what you can think of concerning the other thing. Pz

GamerGun · November 5, 2008

I changed my code a bit Got this now:

records2.php

<html>
<head>

    <link rel="stylesheet" href="style.css" />

<STYLE TYPE="text/css">
<!--
TD{font-size: 11pt;}
--->
</STYLE>

<SCRIPT LANGUAGE="JavaScript">
<!--

function showList() {
  sList = window.open("popup.php", "list", "width=300,height=600");
}

function remLink() {
  if (window.sList && window.sList.open && !window.sList.closed)
    window.sList.opener = null;
}

// -->
</SCRIPT>

</head>
<body>

<div class="RedLink">

<basefont face="Arial">

<table border="1" bordercolor="#FFCC00" style="background-color:#FFFFCC" width="400" cellpadding="3" cellspacing="3">
<tr>
<td>Zoeken</td>
</tr><tr>
	<td>
<form method="post" action="<?php echo $_SERVER['PHP_SELF'] ?>" name="stockform">
<input type="text" name="search" size="40">
	</td>
<td><input type="button" value="Selecteer afdeling" onclick="showList()"></td>
<tr><td>
   <select size="1" name="dropdown">
      <option value="" selected>Zoek op...</option>
      <option value="userid">Medewerker</option>
      <option value="specid">Gerelateerd</option>
      <option value="overid">Overige</option>
      <option value="afdelingid">Afdeling</option>
      <option value="uren">Uren</option>
      <option value="minuten">Minuten</option>
      <option value="omschrijving">Omschrijving</option>
      <option value="callnr">Call nr</option>
      <option value="datum">Datum</option>
      <option value="week">Week</option>
   </select>


	<input type="Submit" value="Verstuur" name="Submit" /></form>
</td></tr>
</table>

<?php
error_reporting(E_ALL);

if (!isset($_POST['Submit'])) {
   // form not submitted
}
else {

   // form submitted
   // set server access variables
   $host = "localhost";
   $user = "root";
   $pass = "password";
   $db = "urendatabase";

   $search = empty($_POST['search'])? die ("ERROR: Je moet wel iets invullen") : mysql_escape_string($_POST['search']);
   $dropdown = empty($_POST['dropdown'])? die ("ERROR: Je moet wel een keuze maken") : mysql_escape_string($_POST['dropdown']);

   // 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);
   }

//Create search query
   switch($dropdown){
      case 'userid'://if search is user name
         $werknaam = explode(" ", $search);
            $query = "SELECT * FROM uren WHERE userid =
                     (SELECT userid FROM werknemers WHERE
                           voornaam LIKE '".$werknaam[0]."%' AND achternaam LIKE '%".end($werknaam)."')";
         break;
      case 'specid': //if search is spec
            $query = "SELECT * FROM uren WHERE specid =
                     (SELECT specid FROM specificatie WHERE
                           specificatie LIKE '$search%')";
            break;
      case 'overid': //if search is overige
            $query = "SELECT * FROM uren WHERE overid =
                     (SELECT overid FROM overige WHERE
                           overige LIKE '$search%')";
            $break;
      case 'afdelingid': //if search is afdeling
            $query = "SELECT * FROM uren WHERE afdelingid =
                     (SELECT afdelingid FROM afdelingen WHERE
                           afdeling LIKE '%$search%')";
         break;
      default: //everything else
            $query = "SELECT * FROM uren WHERE $dropdown LIKE '%$search%'";
            break;
   }
      
   $result = @mysql_query($query);
   $errno = mysql_errno();
   if ($errno == 1242){//error code for "more than 1 row" mysql error
         die("Your search matches more than one value. Please modify the search term to match only one value.");
   }
   else if ($errno != 0){//for all other errors, show the mysql error msg
         die(mysql_error());
   }

   $num=mysql_numrows($result);

   mysql_close($connect);

   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
   } //close for loop
} //close if block
?>

</div>

</body>
</html>

popup.php

<html>
<head>
<title>Afdelingen</title>

<style type="text/css">
A:link {text-decoration: none}
A:visited {text-decoration: none}
A:active {text-decoration: none}
A:hover {text-decoration: underline; color: red;}
</style>

<script language="JavaScript">
<!--

function pick(symbol) {
  if (window.opener && !window.opener.closed)
    window.opener.document.stockform.search.value = symbol;
  window.close();
}

// -->
</script>
</head>
<body>
<div style="background-color:#E0E0E0;">
<?php include("afd2.php"); ?>
</div>
</body>
</html>

afd2.php

<?
//connect to mysql
//change user and password to your mySQL name and password
mysql_connect("localhost","root","password"); 

//select which database you want to edit
mysql_select_db("urendatabase"); 

//select the table
$result = mysql_query("select * from afdelingen");

//grab all the content
while($r=mysql_fetch_array($result))
{	
   //the format is $variable = $r["nameofmysqlcolumn"];
   //modify these to match your mysql table columns
  
   $afdeling=$r["afdeling"];
   
   //display the row
   echo "<a href=\"javascript:pick('$afdeling')\">$afdeling</a><br>";
}
?>

Works like a charm . This way users don't have to fill in the afdeling (= department) their self.

Anyhow, back to the filtering.

I think the check box system will carry on some problems. Like;

1. I only want the results from user 1 and user 3, not from user 2 and 4.

2. I want the results from Week 22 till 44.

3. I want the results from 05-05-2008 till 10-10-2008.

4. A combination of the above.

This cannot be done with the check boxes only, any idea or suggestions on what we could to best?

Thanks

bobbinsbro · November 5, 2008

not sure how to do the filtering system yet, but in the meantime here's a small addition to the afdeling selecting button:

<html>
<head>

    <link rel="stylesheet" href="style.css" />

<STYLE TYPE="text/css">
<!--
TD{font-size: 11pt;}
--->
</STYLE>

<SCRIPT LANGUAGE="JavaScript">
<!--

function showList() {
  sList = window.open("popup.php", "list", "width=300,height=600");
}

function remLink() {
  if (window.sList && window.sList.open && !window.sList.closed)
    window.sList.opener = null;
}

function displayButton(){
var theContents = document.getElementById('dropdown')[document.getElementById('dropdown').selectedIndex].value;
if (theContents == 'afdelingid'){
	if (document.all) { //for internet explorer 7 (not tested with 8 beta)
		document.all['button'].style.display = 'block';
	}
	else if (document.getElementById) { //for FF, chrome, opera, and all other standards-compliant browsers
		document.getElementById('button').style.display = 'table-cell';
	}
}
else{
	if (document.all) { //for internet explorer 7 (not tested with 8 beta)
		document.all['button'].style.display = 'non';
	}
	else if (document.getElementById) { //for FF, chrome, opera, and all other standards-compliant browsers
		document.getElementById('button').style.display = 'none';
	}
}
}

// -->
</SCRIPT>

</head>
<body>

<div class="RedLink">

<basefont face="Arial">

<table border="1" bordercolor="#FFCC00" style="background-color:#FFFFCC" width="420" cellpadding="3" cellspacing="3">
<tr>
<td>Zoeken</td>
</tr><tr>
	<td>
<form method="post" action="<?php echo $_SERVER['PHP_SELF'] ?>" name="stockform">
<input type="text" name="search" size="40">
<input type="button" id = "button" value="Selecteer afdeling" onclick="showList()" style="display:none">
	</td>
<tr><td>
   <select size="1" name="dropdown" id="dropdown" onChange = "displayButton()">
      <option value="" selected>Zoek op...</option>
      <option value="userid">Medewerker</option>
      <option value="specid">Gerelateerd</option>
      <option value="overid">Overige</option>
      <option value="afdelingid">Afdeling</option>
      <option value="uren">Uren</option>
      <option value="minuten">Minuten</option>
      <option value="omschrijving">Omschrijving</option>
      <option value="callnr">Call nr</option>
      <option value="datum">Datum</option>
      <option value="week">Week</option>
   </select>


	<input type="Submit" value="Verstuur" name="Submit" /></form>
</td></tr>
</table>

GamerGun · November 5, 2008

Cool, thanks!

Hope you can come up with something for the filtering Hope you still don't mint doing this all.

bobbinsbro · November 5, 2008

i think the best filtering system would probably be an advanced search, where the user can search for more than 1 type of information, and the result will be an intersection of all the searches. so you would have a link (or a button) that would open an advanced search window/page/<div> which would have text fields for name, afdeling, spec, overige, callnr, omschrijving, 2 fields for week (so they can search in a range of weeks) 2 fields for date (so they can search in a range of dates) and 2 time dropdowns (1 for uren and 1 for minuten) and 1 submit button. then we make a tremendous and scary sql query that will only find entries that match all the fields (those that are filled out).

if you put together an advanced search form and post the code, i'll trow together the sql query to find the filtered results.

GamerGun · November 5, 2008

Something like this? See records3.php

<html><head>
<title>Uren</title>

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

</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 name="advanced" method="post" action="<?php echo $_SERVER['PHP_SELF'] ?>">
<table border="1" bordercolor="#FFCC00" style="background-color:#FFFFCC" width="700" cellpadding="3" cellspacing="3">
<tr>
	<td>Naam</td>
	<td><input type=\"text\" size=\"25\" name=\"naam\" value='Voornaam + achternaam' /></td>
</tr>
<tr>
	<td>Gerelateerd</td>
	<td><input type=\"text\" size=\"25\" name=\"spec\" /></td>
</tr>
<tr>
	<td>Overige</td>
	<td><input type=\"text\" size=\"25\" name=\"overige\" /></td>
</tr>
<tr>
	<td>Call nr</td>
	<td><input type=\"text\" size=\"25\" maxlength=\"5\" onkeypress=\"return isNumberKey(event)\" name=\"callnr\" /></td>
</tr>
<tr>
	<td>Omschrijving</td>
	<td><input type=\"text\" size=\"25\" 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>

</body></html>

The "callnr" function is not working. Dunno why, it works for the index. (No letters allowed).

But okay. Thanks

bobbinsbro · November 5, 2008

ya, but i think it would be better to split first and last name into 2 fields. will make search more accurate with the added bonus that ppl can search by only 1 and not both.

also, you forgot afdeling (+cool button ).

and the table would look nicer if the single-cell rows get a colspan="2" attribute.

i'll start working on the sql search.

GamerGun · November 5, 2008

Like?

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="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 colspan="2">Gerelateerd</td>
	<td><input type=\"text\" size=\"25\" name=\"spec\" /></td>
</tr>
<tr>
	<td colspan="2">Overige</td>
	<td><input type=\"text\" size=\"25\" name=\"overige\" /></td>
</tr>
<tr>
	<td colspan="2">Afdeling</td>
	<td><input type=\"text\" size=\"25\" name=\"afdeling\" /> <input type="button" value="Selecteer afdeling" onclick="showList()"></td>
</tr>
<tr>
	<td colspan="2">Call nr</td>
	<td><input type=\"text\" size=\"25\" maxlength=\"5\" onkeypress=\"return isNumberKey(event)\" name=\"callnr\" /></td>
</tr>
<tr>
	<td colspan="2">Omschrijving</td>
	<td><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>

</body></html>

popup2.php

<html>
<head>
<title>Afdelingen</title>

<style type="text/css">
A:link {text-decoration: none}
A:visited {text-decoration: none}
A:active {text-decoration: none}
A:hover {text-decoration: underline; color: red;}
</style>

<script language="JavaScript">
<!--

function pick(symbol) {
  if (window.opener && !window.opener.closed)
    window.opener.document.advanced.afdeling.value = symbol;
  window.close();
}

// -->
</script>
</head>
<body>
<div style="background-color:#E0E0E0;">
<?php include("afd2.php"); ?>
</div>
</body>
</html>

Any idea why the afdelingen selection (via the button) and call nr restriction (no chars but 0 t/m 9 allowed) is not working?

Thanks

bobbinsbro · November 5, 2008

re. the javascript errors:

you were escaping all attributes in the html, even though it was not inside php. this should work fine. i also rearranged the colspan, and there is no need for uren and minuten to be arrays, so i changed that too.

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

</body></html>

bobbinsbro · November 5, 2008

this should work:

<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

//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
}
?>

</body></html>

at the bottom of the code, there is a comment that reads "exectue query here". this is where you should call the mysql_query() function.

i don't have time to go into detail about what i did right now. if you like, i will explain it all next time. one thing i will say is that i added an error collecting array, which prints all the errors under the search table and dies if there are any errors, and continues the script if there aren't any errors.

of course, if there are bugs, please let me know. i tested it, so there should be none...

GamerGun · November 5, 2008

I don't get it. I added the connection and so, but what should the result query be?

The same like "Database output" at records2? Can't test right now. Will do so tomorrow

Thanks!

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

</body></html>

bobbinsbro · November 5, 2008

yes, the result should be the same as records 2.

i had an idea about how to combine these 2 files into 1, and include either the simple search code or the advanced search code, according to what the user submitted. the user would navigate between simple and advanced by clicking on a link/button that would call a javascript function that would change the contents of the search table (it would have to be put in a <div> for convenience). this is simple enough to do, but first we should make sure my new code works properly.

EDIT: please remind me at a later date to create a more extensive date validating function, because at this moment, the date 8888-88-88 is considered valid. i need to fix this, but unfortunately, i have no time now.

GamerGun · November 5, 2008

I guess i'm doing something wrong.

<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

}

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>

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