select_emp2.php

<?php

// Load PEAR MDB2
require 'MDB2.php';
// Load the form helper functions.
require 'formhelpers.php';

require 'connect_mysql.php';

// Connect to the database
$db = connect_mysql();

if (MDB2::isError($db)) { die ("Can't connect: " . $db->getMessage()); }

// Set up automatic error handling
$db->setErrorHandling(PEAR_ERROR_DIE);

// 部門番号の選択
$deptno_choices = array();
$rows = $db->queryAll('select deptno from departments order by deptno');
foreach($rows as $row){
	$deptno_choices[] = $row[0];
}
$deptno_only = $deptno_choices;
$deptno_choices[] = '全部';
//var_dump($deptno_choices);

// Set up fetch mode: rows as objects
//$db->setFetchMode(MDB2_FETCHMODE_OBJECT);


// The main page logic:
// - If the form is submitted, validate and then process or redisplay
// - If it's not submitted, display
if ($_POST['_submit_check']) {
    // If validate_form() returns errors, pass them to show_form()
    if ($form_errors = validate_form()) {
        show_form($form_errors);
    } else {
        // The submitted data is valid, so process it
        process_form();
    }
} else {
    // The form wasn't submitted, so display
    show_form();
}

function show_form($errors = '') {
    // If the form is submitted, get defaults from submitted parameters
    if ($_POST['_submit_check']) {
        $defaults = $_POST;
    } else {
        // Otherwise, set our own defaults
        $defaults = array('min_sal' => '100000',
                          'max_sal' => '500000');
    }
    
    // If errors were passed in, put them in $error_text (with HTML markup)
    if (is_array($errors)) {
        $error_text = '<tr><td>右記のエラーを修正してください:';
        $error_text .= '</td><td><ul><li>';
        $error_text .= implode('</li><li>',$errors);
        $error_text .= '</li></ul></td></tr>';
    } else {
        // No errors? Then $error_text is blank
        $error_text = '';
    }

    // Jump out of PHP mode to make displaying all the HTML tags easier
?>
<form method="POST" action="<?php print $_SERVER['PHP_SELF']; ?>">
<table>
<?php print $error_text ?>

<tr><td>従業員名:</td>
<td><?php input_text('ename', $defaults) ?></td></tr>

<tr><td>給与(最少):</td>
<td><?php input_text('min_sal', $defaults) ?></td></tr>

<tr><td>給与(最多):</td>
<td><?php input_text('max_sal', $defaults) ?></td></tr>

<tr><td>部門番号:</td>
<td><?php input_select('deptno', $defaults, $GLOBALS['deptno_choices']); ?>
</td></tr>

<tr><td colspan="2" align="center"><?php input_submit('search','Search'); ?>
</td></tr>

</table>
<input type="hidden" name="_submit_check" value="1"/>
</form>
<?php
      } // The end of show_form()

function validate_form() {
    $errors = array();

    // minimum sal must be a valid floating point number
    if ($_POST['min_sal'] != strval(floatval($_POST['min_sal']))) {
        $errors[] = '給与(最少)に正しい値を入力してください。';
    }

    // maximum sal must be a valid floating point number
    if ($_POST['max_sal'] != strval(floatval($_POST['max_sal']))) {
        $errors[] = '給与(最多)に正しい値を入力してください。';
    }

    // minimum sal must be less than the maximum sal
    if ($_POST['min_sal'] >= $_POST['max_sal']) {
        $errors[] = '給与(最少)は給与(最多)より少ない値を入力してください。';
    }

    if (! array_key_exists($_POST['deptno'], $GLOBALS['deptno_choices'])) {
        $errors[] = '正しい部門番号を選択してください。';
    }
    return $errors;
}

function process_form() {
    // Access the global variable $db inside this function
    global $db, $deptno_only;
    
    // build up the query 
    $sql = 'SELECT e.empno, e.ename, e.yomi, e.job, m.ename, e.hiredate, e.sal, e.comm, dname FROM employees e left outer join employees m on e.mgr = m.empno join departments d on e.deptno = d.deptno WHERE e.sal >= ? AND e.sal <= ?';

    // if a employee name was submitted, add to the WHERE clause
    // we use quoteSmart() and strtr() to prevent user-enter wildcards from working
    if (strlen(trim($_POST['ename']))) {
        $employee = $db->quote($_POST['ename']);
        $employee = strtr($employee, array('_' => '\_', '%' => '\%'));
        $sql .= " AND ename LIKE $employee";
    }

    // if deptno is "yes" or "no", add appropriate SQL
    // (if it's either, we don't need to add deptno to the WHERE clause)
    $spicy_choice = $GLOBALS['deptno_choices'][ $_POST['deptno'] ];
    if (in_array($spicy_choice, $deptno_only)) {
        $sql .= " AND e.deptno = $spicy_choice";
    }

		$sql .= ' order by e.mgr, e.empno';

    // Send the query to the database program and get all the rows back
//var_dump($sql);
		$sth = $db->prepare($sql);
    $result = $sth->execute(array($_POST['min_sal'], $_POST['max_sal']));
		$employees = $result->fetchAll();

//var_dump($employees);

    if (count($employees) == 0) {
			print 'No employees matched.';
    } else {
			print '<table border="">';
			print '<tr style="background-color: #C9E2F8">';
			print '<th>番号</th><th>従業員名</th><th>読み</th><th>職種</th><th>上司</th><th>入社日</th><th>給与</th><th>歩合</th><th>部門</th></tr>';
			$fill = false;
			foreach ($employees as $emp) {
				if(ctype_digit($emp[7])){
					$comm = number_format($emp[7]);
				}else{
					$comm = 'なし';
				}
				if($fill){
					print '<tr style="background-color: #C9E2F8">';
				}else{
					print '<tr style="background-color: #E3F0FB">';
				}
				printf('<td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td align="right">%s</td><td>%s</td></tr>'
					,h($emp[0])
					,h($emp[1])
					,h($emp[2])
					,h($emp[3])
					,h($emp[4])
					,h($emp[5])
					,h(number_format($emp[6]))
					,$comm
					,h($emp[8])
				);
			$fill = !$fill;
		}
	}
}
?>