Populate Cross Reference Table Using PDO Create Position Page
Hi all,
I'm creating a new position page and attempting to associate one or more client contacts to the new position. This is a many-to-many relation therefore am using a cross reference table which is called jobs2clients. Within the body I've displayed an array of clients from the client table to select from.
The position gets created without a problem; however, I can not populate the cross reference table with the selected client contact.
I've included both the processing script and the form element below. Thanks in advance for any help.
<?php
require_once '../includes/session_timeout_db.php';
?>
<?php
require_once '../includes/connection.php';
// create database connection
$conn = dbConnect('write', 'pdo');
if (isset($_POST['insert'])) {
// initialize flag
$OK = false;
// create SQL
$sql = 'INSERT INTO jobs (job_number, job_title, job_status, notes, salary, location, cand_prospects, company_prospects, how_long_open, why_open, what_done, relo_info, write_up)
VALUES(:job_number, :job_title, :job_status, :notes, :salary, :location, :cand_prospects, :company_prospects, :how_long_open, :why_open, :what_done, :relo_info, :write_up)';
// prepare the statement
$stmt = $conn->prepare($sql);
// bind parameters and execute statement
$stmt->bindParam(':job_number', $_POST['job_number'], PDO::PARAM_STR);
$stmt->bindParam(':job_title', $_POST['job_title'], PDO::PARAM_STR);
$stmt->bindParam(':job_status', $_POST['job_status'], PDO::PARAM_STR);
$stmt->bindParam(':notes', $_POST['notes'], PDO::PARAM_STR);
$stmt->bindParam(':salary', $_POST['salary'], PDO::PARAM_STR);
$stmt->bindParam(':location', $_POST['location'], PDO::PARAM_STR);
$stmt->bindParam(':cand_prospects', $_POST['cand_prospects'], PDO::PARAM_STR);
$stmt->bindParam(':company_prospects', $_POST['company_prospects'], PDO::PARAM_STR);
$stmt->bindParam(':how_long_open', $_POST['how_long_open'], PDO::PARAM_STR);
$stmt->bindParam(':why_open', $_POST['why_open'], PDO::PARAM_STR);
$stmt->bindParam(':what_done', $_POST['what_done'], PDO::PARAM_STR);
$stmt->bindParam(':relo_info', $_POST['relo_info'], PDO::PARAM_STR);
$stmt->bindParam(':write_up', $_POST['write_up'], PDO::PARAM_STR);
// execute and get number of affected rows
$stmt->execute();
$OK = $stmt->rowCount();
// insert client data into cross reference table
if ($OK && isset($_POST['client_id'])) {
// get the client_contact's primary key
$job_id = $conn->lastInsertId();
foreach ($_POST['client_id'] as $client_id) {
if (is_numeric($client_id)) {
$values[] = "($job_id, " . (int) $client_id . ')';
}
}
if ($values) {
$sql = 'INSERT INTO jobs2clients (job_id, client_id) VALUES ' . implode(',', $values);
// execute the query and get error message if it fails
// PDO uses the exec() method for non-SELECT queries
if (!$conn->exec($sql)) {
$catError = $conn->error;
}
}
}
// redirect if successful or display error
if ($OK) {
header('Location: http://localhost/masbackroom/positions_cms/congrats.php');
exit;
} else {
$errorInfo = $stmt->errorInfo();
if (isset($errorInfo[2])) {
$error = $errorInfo[2];
}
}
}
?>
<p> * Required Field </p>
<form method="post" action="">
<p>
<label for="title">Position#:*</label>
<input name="job_number" type="text" required="required" id="job_number">
</p>
<p>
<label for="title">Title:*</label>
<input name="job_title" type="text" required="required" id="job_title">
</p>
<p>
<label for="title">Status:</label>
<input name="job_status" type="text" id="job_status">
</p>
<p>
<label for="article">Notes:</label>
<textarea name="notes" id="notes"></textarea>
</p>
<p>
<label for="title">Salary:</label>
<input name="salary" type="text" id="salary">
</p>
<p>
<label for="title">Location:</label>
<input name="location" type="text" id="location">
</p>
<p>
<label for="article">Cand Prospects:</label>
<textarea name="cand_prospects" id="cand_prospects"></textarea>
</p>
<p>
<label for="title">Company Prospects:</label>
<input name="company_prospects" type="text" id="company_prospects">
</p>
<p>
<label for="title">How Long Open:</label>
<input name="how_long_open" type="text" id="how_long_open">
</p>
<p>
<label for="title">Why Open:</label>
<input name="why_open" type="text" id="why_open">
</p>
<p>
<label for="title">What Done:</label>
<input name="what_done" type="text" id="what_done">
</p>
<p>
<label for="title">Relo Info:</label>
<input name="relo_info" type="text" id="relo_info">
</p>
<p>
<label for="article">Write Up:</label>
<textarea name="write_up" id="write_up"></textarea>
</p>
<p>
<label for="client_contact">Client Contacts:</label>
<br>
<select name="client_id" size="" multiple id="client_id">
<?php
// get client contacts
$getCats = 'SELECT client_id, first_name, last_name, client_company FROM clients ORDER BY last_name';
// use foreach loop to submit query and display results
foreach ($conn->query($getCats) as $row) {
?>
<option value="<?= $row['client_id']; ?>" <?php
if (isset($_POST['client_id']) && in_array($row['client_id'], $_POST['last_name'], $_POST['first_name'], $_POST['client_company'])) {
echo 'selected';
} ?>><?php echo $row['last_name']; ?>,
<?php echo $row['first_name']; ?> |
<?php echo $row['client_company']; ?></option>
<?php } ?>
</select>
</p>
<br>
<p>
<input type="submit" name="insert" value="Create New Position" id="insert">
</p>
</form>
AdChoices