向表数据库添加数据时的外键问题

Question

I have a PHP page which populates 2 different tables of my database.

After I add a new column and then create a foreign key, data cannot be inserted in that table anymore. If I delete the foreign key then it works again... Anyone experienced something like this before?

This is how I populate both of the table from the same page:

include("../includes/connection.php");

$name = mysqli_real_escape_string($link, $_POST['name']);
$email = mysqli_real_escape_string($link, $_POST['email']);
$number = mysqli_real_escape_string($link, $_POST['number']);
$device = mysqli_real_escape_string($link, $_POST['device']);
$price = mysqli_real_escape_string($link, $_POST['price']);
$payment = mysqli_real_escape_string($link, $_POST['payment']);
$status = mysqli_real_escape_string($link, $_POST['status']);
$model = mysqli_real_escape_string($link, $_POST['model']);
$problem = mysqli_real_escape_string($link, $_POST['problem']);

// attempt insert query execution

$sql = "INSERT INTO customer (name, mail, number, device, price, paymenttype,status,date) VALUES ('$name', '$email', '$number', '$device', '$price', '$payment','$status',NOW())";

if(mysqli_query($link, $sql)){
    // echo "Records added successfully.";
    header("location:add-customer.php?message=The customer has been added to the database1");
    } else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}         
$sql = "INSERT INTO job (device, model, problem, status) VALUES ('$device', '$model', '$problem', '$status')";

if(mysqli_query($link, $sql)){
    // echo "Records added successfully.";
    header("location:add-customer.php?message=The customer has been added to the database2");
} else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}    mysqli_close($link);?>

And, this is a picture might explain better what I want to achieve:

1. The one in the background is the JOB_Table.
2. Each row of JOB_Table has a button "info" than onclick should shows details gathered from CUSTOMER_table.

Example:
button "info"in the first row, wants to get the first row of customer table..
button "info"in the second row, wants to get the second row of customer table...

---------modal pop up code that gather customer table after click-----

<div class="modal fade" id="myModal" role="dialog">
    <div class="modal-dialog modal-lg">
  <!-- Modal content-->
      <div class="modal-content">
        <div class="modal-header">
          <button type="button" class="close" data-dismiss="modal">&times;</button>
          <h4 class="modal-title">Customer Information</h4>
        </div>
        <div class="modal-body">


        <?php

include("../includes/connection.php");

if ($link->connect_errno > 0) {
    die('Unable to connect to database [' . $link->connect_error . ']');
}
$sql = "SELECT id,name,mail,number,price,paymenttype,faktura,date from customer WHERE     id = '[job_id]' ";



if (!$result = $link->query($sql)) {
    die('There was an error running the query [' . $link->error . ']');
}
echo "
<table class='table'>
    <thead>
        <tr>";
/* Get field information for all columns */
while ($finfo = $result->fetch_field()) {
    echo "
        <th>" . $finfo->name . "</th>";
}
echo "
        </tr>
    </thead>
    <tbody>";
while ($row = $result->fetch_assoc()) {
    echo "<tr class='info'>
    <td>" . $row['id'] . "</td>
                <td>" . $row['name'] . "</td>
                <td>" . $row['mail'] . "</td>
                <td>" . $row['number'] . "</td>
                <td>" . $row['price'] . "</td>
                <td>" . $row['paymenttype'] . "</td>

                <td>" . $row['faktura'] . "</td>
                <td>" . $row['date'] . "</td>
    </tr>";
}
echo "
    </tbody>

</table>";

?>
       </div>
        <div class="modal-footer">
          <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
        </div>
      </div>

    </div>
  </div>

---------and with this code I gather the job table----

<?php

include("../includes/connection.php");

if ($link->connect_errno > 0) {
    die('Unable to connect to database [' . $link->connect_error . ']');
}

if (isset($_POST['update'])) {
    $results = $link->query("UPDATE job SET status='$_POST[status]', priority='$_POST[priority]' WHERE id='$_POST[hidden]'");
    $results = $link->query("UPDATE customer SET status='$_POST[status]' WHERE id='$_POST[hidden]'");
}

$sql = "SELECT * from job";
if (!$result = $link->query($sql)) {
    die('There was an error running the query [' . $link->error . ']');
}
echo "
<table class='table'>
    <thead>
        <tr>";
/* Get field information for all columns */
while ($finfo = $result->fetch_field()) {
    echo "
        <th>" . $finfo->name . "</th>";
}
echo "
        </tr>
    </thead>
   <tbody>";


while ($row = $result->fetch_assoc()) {

      $job_id = $row['id'];
    echo "<form action='' method=post>";

    echo "<tr class='info'>

                <input type=hidden name=hidden value=" . $row['id'] . ">
                <td>" . $row['id'] . "</td> 
                <td>" . $row['device'] . "</td>
                  <td>" . $row['model'] . "</td> 
                <td>" . $row['problem'] . "</td>
                 <td>
           <select class='form-control col-sm-10' id='status' name='status'>
               <option value='new' ". ($row['status'] == 'new'? 'selected ': '') .">New</option>
        <option value='progress' ". ($row['status'] == 'progress'? 'selected ': '') .">Progress</option>
            <option  value='wait' ". ($row['status'] == 'wait'? 'selected ': '') .">Wait</option>
            <option value='done' ". ($row['status'] == 'done'? 'selected ': '') .">Done</option>
            <option value='close' ". ($row['status'] == 'close'? 'selected ': '') .">Close</option>
    </select>
            </td>        

                <td><select class='form-control col-sm-10' id='priority' name='priority'>                
                             <option  style='background-color:green;'value='low' ". ($row['priority'] == 'Low'? 'selected ': '') .">Low</option>
                                <option style='background-color:yellow; value='Medium' ". ($row['priority'] == 'Medium'? 'selected ': '') .">Medium</option>
                  <option style='background-color:red; value='High' ". ($row['priority'] == 'High'? 'selected ': '') .">High</option>


                       </select></td>

                <td> <button type='submit' class='btn btn-primary btn-sm' name='update'>Update</button></td>

                <td> <a class='btn btn-primary btn-sm'  data-toggle='modal' data-target='#myModal'   name='job_id' value='[$job_id]'  >  Info</a></td>


            </tr>";
    echo "</form>";
}
echo "
    </tbody>

</table>";

?>

Answer 1

There seems to be a lot wrong with this script.

Since your Customer table row is inserted first, and you don't specify a job_id I think it's attempting to use 0 which fails the foreign key constraint.

I can't tell from your tables which entity needs to be added first or if one depends on the other. So I'll give you a solution that should work for both. I will say that while this solution works, it allows you to ignore the FK constraint and if you plan to ignore it then you're better off not setting it in the first place.

First change the job_id field on the Customer table to allow NULL values.

Second change this line

$sql = "INSERT INTO customer (name, mail, number, device, price, paymenttype,status,date) VALUES ('$name', '$email', '$number', '$device', '$price', '$payment','$status',NOW())";

to this line

$sql = "INSERT INTO customer (name, mail, number, device, price, paymenttype,status,date,job_id) VALUES ('$name', '$email', '$number', '$device', '$price', '$payment','$status',NOW(),NULL)";

It appears as though both of your header redirects can be reached during a execution.

1. You should always call exit() after a header redirect or the script will continue to process. Why I have to call 'exit' after redirection through header('Location..') in PHP?

2. If you echo an error message and your are not buffering your output then your redirect will fail because you have already sent output, which sends headers. How to fix "Headers already sent" error in PHP

Edit after problem clarification

<?php

    //connection includes and escaping POST data goes here.

    // insert job first so we can use the pk as an fk for the customer table
    $sql = "INSERT INTO job (device, model, problem, status) VALUES ('$device', '$model', '$problem', '$status')";

    $result = mysqli_query($link, $sql);

    // if query fails stop script and echo error
    if( $result === false)
    {
        echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
        exit;
    }

    // this only works for auto-incremented fields  
    $jobPrimaryKey = mysqli_insert_id($link);

    $sql = "INSERT INTO customer (name, mail, number, device, price, paymenttype,status,date,job_id) VALUES ('$name', '$email', '$number', '$device', '$price', '$payment','$status',NOW(),'{$jobPrimaryKey}')";

    $result = mysqli_query($link, $sql);

    // if query fails stop script and echo error
    if( $result === false)
    {
        echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
        exit;
    }

    // if we get here then both queries were successful

    header("location:some_page.php?message=A job and customer has been added to the database");
    // exit now to stop the script from continuing 
    exit;

    echo "You'll never see this";
?>