select_emp_mysqli.php

<?php

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

$db = mysqli_connect('localhost','test','pass','testphp');
if (! $db) { die("Can't connect: " . mysqli_connect_error()); }

// 部門番号の選択
$deptno_choices = array();
$sql = 'select deptno from departments order by deptno';
$q = mysqli_query($db, $sql);
while($row = mysqli_fetch_object($q)){
	$deptno_choices[] = $row->deptno;
}
$deptno_only = $deptno_choices;
$deptno_choices[] = '全部';

// 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() {
	// この関数内でグローバル変数$dbにアクセスする
	global $db,$deptno_only;
    
	// build up the query 
	$sql = 'SELECT * FROM employees  WHERE ';

	// add the minimum price to the query
	$sql .= "sal >= '" .
				mysqli_real_escape_string($db, $_POST['min_sal']) . "' ";

	// add the maximum price to the query
	$sql .= " AND sal <= '" .
        mysqli_real_escape_string($db, $_POST['max_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 = mysqli_real_escape_string($db, $_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 deptno = $spicy_choice";
	}

    // Send the query to the database program and get all the rows back
//var_dump($sql);
	$q = mysqli_query($db, $sql);

	if (mysqli_num_rows($q) == 0) {
			print 'No employees matched.';
	} else {
		print '<table border="">';
		print '<tr><th>番号</th><th>従業員名</th><th>読み</th><th>職種</th><th>上司</th><th>入社日</th><th>給与</th><th>歩合</th><th>部門番号</th></tr>';
		while ($emp = mysqli_fetch_object($q)) {
			printf('<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>'
				,h($emp->empno)
				,h($emp->ename)
				,h($emp->yomi)
				,h($emp->job)
				,h($emp->mgr)
				,h($emp->hiredate)
				,h($emp->sal)
				,h($emp->comm)
				,h($emp->deptno)
			);
		}
	}
}
?>