Skip to main content
R
RR456
Inspiring
September 14, 2015
Question

Populate Cross Reference Table Using PDO Create Position Page

  • September 14, 2015
  • 1 reply
  • 905 views

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>

This topic has been closed for replies.

1 reply

sinious
sinious
Legend
September 19, 2015

I do very little PDO but just looking at what you're doing a couple things stick out to me.

$values[] = "($job_id, " . (int) $client_id . ')';

You should check what this actually gets populated with. You're actually getting the data in $client_id directly from $_POST['client_ids'] (foreach ($_POST['client_id'] as $client_id) {) which is loosely SQL injection validated by a quick is_numeric($client_id) check and then attempt to cast (int) (I foresee this being tampered with later). I'm interested in seeing what $values actually contains after the foreach loop.

That's just a shot across the bow for security, might want to do a little more validation. Like does that client_id exist, is it unique enough for someone not to loop an attack on you adding 1,1.. 1,9999999 then 2,1.....2,9999999 etc flooding your database with useless (but valid numeric) entries.

That aside, from a quick scan you forgot parenthesis around your insert statements values which would certainly explain why that's the part that isn't working:

This:

$sql = 'INSERT INTO jobs2clients (job_id, client_id) VALUES ' . implode(',', $values);

Should be:

$sql = 'INSERT INTO jobs2clients (job_id, client_id) VALUES (' . implode(',', $values) .')';

Again, this is only assuming $values actually has what you expect in it, which will require 2 numeric values.

R
RR456Author
Inspiring
September 20, 2015

sinious,

Thanks for taking your valuable time to review my code.  I did place the parenthesis where you noted I'd forgot them.  My script is still DOA; however, your discussion regarding SQL injection as well as possible malicious jamming of my database with useless numeric entries is greatly appreciated.

I'm going to plan B for population of the cross-reference tables by doing manual entry.  On the HTML input side I should be able to limit integer range and on the script side use PDO prepared statements to assure the input is numeric.  I'll also research to see if the PDO prepared can limit the size or range of the integer.

Again, thanks for your assistance.

R
RR456Author
Inspiring
September 24, 2015

. . .use PDO prepared statements to assure the input is numeric.  I'll also research to see if the PDO prepared can limit the size or range of the integer.

I would suggesting doing this with PHP before the data is submitted to the database:

$number = 9;
if (is_numeric($number) && $number > 2 && $number < 8){
// yes   
} else {
// no   
}


I find it easier to read and debug PDO when the columns and values are matched, as below, rather than listing all the columns, followed by all the values :


$sql=$dbh->prepare("INSERT INTO store_global_content SET

client_id=:client_id,

store_description=:store_description,

refund_policy=:refund_policy,

language_id=:language_id
ON DUPLICATE KEY UPDATE
store_description=:store_description,

refund_policy=:refund_policy
");
$sql->bindValue("client_id", CLIENT_ID);
$sql->bindValue("language_id", $language_selected);
$sql->bindValue("store_description", $store_description);
$sql->bindValue("refund_policy", $refund_policy);
try{
$sql->execute() or $response = "<p style='color:red'>INSERT FAILED!</P>";
}catch(PDOException $e)
{ echo $e;}


Thanks Rob for your very helpful discussion.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices