insert slected data as Foreign key and SQLSTATE[23000]: Integrity constraint violation: 1048

Question

Thank you for being here!

i want to be able to select acc_id from account_info table and insert it in patient_info table as Foreign Key

I am getting this error :

In my localhost i can see the value it's suppose to return. And this error which I suppose it occurs because i haven't inserted acc_id yet SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'acc_id' cannot be null

Also var_dump($data) returns array(1) { [0]=> object(stdClass)#3 (1) { ["acc_id"]=> int(124) } }

php file :

 <?php
 header('Access-Control-Allow-Origin: *');

// Define database connection parameters
$hn      = 'localhost';
$un      = 'root';
$pwd     = '';
$db      = 'ringabell';
$cs      = 'utf8';

// Set up the PDO parameters
$dsn  = "mysql:host=" . $hn . ";port=3306;dbname=" . $db . ";charset=" . $cs;
$opt  = array(
                    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
                    PDO::ATTR_EMULATE_PREPARES   => false,
                   );
// Create a PDO instance (connect to the database)
$pdo  = new PDO($dsn, $un, $pwd, $opt);

// Retrieve specific parameter from supplied URL
$data = array();


     try{

    $stmt = $pdo->query('SELECT acc_id FROM account_info ORDER BY acc_id DESC LIMIT 1');
    $data = $stmt->fetchAll(PDO::FETCH_OBJ);
        // Return data as JSON
            echo json_encode($data); 
            var_dump($data);


    $sql= "INSERT INTO patient_info(acc_id, p_fname, p_lname, p_gender, p_condition, p_birthdate, p_emergencycontact)    
                            VALUES(:data, :p_fname, :p_lname, :p_gender, :p_condition, :p_birthdate, :p_emergencycontact)";


        $stmt    = $pdo->prepare($sql); 

        $stmt->bindParam(':p_fname', $p_fname, PDO::PARAM_STR);
        $stmt->bindParam(':p_lname', $p_lname, PDO::PARAM_STR);
        $stmt->bindParam(':p_gender', $p_gender, PDO::PARAM_STR);
        $stmt->bindParam(':p_condition', $p_condition, PDO::PARAM_STR);
        $stmt->bindParam(':p_birthdate', $p_birthdate, PDO::PARAM_STR);
        $stmt->bindParam(':p_emergencycontact', $p_emergencycontact, PDO::PARAM_STR);
            $stmt->bindParam(':data', $acc_id, PDO::PARAM_STR); 


        $stmt->execute();

         echo json_encode(array('message' => 'Congratulations the record was added to the database')); 
   }

    catch(PDOException $e)
     {
        echo $e->getMessage();
     }
?>

Answer 1

It seems there are multiple issues in your code or the way you are trying to do things. Please check the code below. It should work. I have added few inline comments. Please check them:

<?php


// Define database connection parameters
$hn      = 'localhost';
$un      = 'root';
$pwd     = '';
$db      = 'ringabell';
$cs      = 'utf8';

// Set up the PDO parameters
$dsn  = "mysql:host=" . $hn . ";port=3306;dbname=" . $db . ";charset=" . $cs;
$opt  = array(
                    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
                    PDO::ATTR_EMULATE_PREPARES   => false,
                   );
// Create a PDO instance (connect to the database)
$pdo  = new PDO($dsn, $un, $pwd, $opt);

// Retrieve specific parameter from supplied URL
$data = array();


try {

    $stmt = $pdo->query('SELECT acc_id FROM account_info ORDER BY acc_id DESC LIMIT 1');
    $data = $stmt->fetchAll(PDO::FETCH_OBJ);
    // You do not need to return response from here
    // echo json_encode($data); 
    // var_dump($data);


    $sql= "INSERT INTO patient_info(acc_id, p_fname, p_lname, p_gender, p_condition, p_birthdate, p_emergencycontact)    
                            VALUES(:acc_id, :p_fname, :p_lname, :p_gender, :p_condition, :p_birthdate, :p_emergencycontact)";


    $stmt    = $pdo->prepare($sql);

    // the $p_fname, $p_lname, $p_gender etc variables in your code were never initiated. You would get
    // notice for this if you had all error_reporting on. I am not sure from where you intend to get this info;
    // so, I just added some dummy data.
    $p_fname = 'Patient first name';
    $p_lname = 'Patient last name';
    $p_gender = 'm';
    $p_condition = 'condition';
    $p_birthdate = '1999-01-01';
    $p_emergencycontact = 'Contact';

    $stmt->bindParam(':p_fname', $p_fname, PDO::PARAM_STR);
    $stmt->bindParam(':p_lname', $p_lname, PDO::PARAM_STR);
    $stmt->bindParam(':p_gender', $p_gender, PDO::PARAM_STR);
    $stmt->bindParam(':p_condition', $p_condition, PDO::PARAM_STR);
    $stmt->bindParam(':p_birthdate', $p_birthdate, PDO::PARAM_STR);
    $stmt->bindParam(':p_emergencycontact', $p_emergencycontact, PDO::PARAM_STR);

    // You do not have any $acc_id variable in your code. To get data from your fetch you need to do 
    // like this:
    $stmt->bindParam(':acc_id', $data[0]->acc_id, PDO::PARAM_STR); 


    $stmt->execute();

    header('Access-Control-Allow-Origin: *');

    // If you want to get the acc_id in your client side through the AJAX call, combine both
    // mesage and data in the same JSON object.
    echo json_encode(
        array(
            'message' => 'Congratulations the record was added to the database'
            'data' => $data
        )
   ); 
} catch(PDOException $e) {
    // make sure to send the proper status code
    http_response_code(500);
    // even error should be sent back as in json so that your javascript client can
    // easily parse it
    echo json_encode(
        array(
            'error' => $e->getMessage()
        )
    );
}
?>