Insert Selected data in database

Question

Basically I want to insert a Foreign key acc_id in patient_info table from account_info table.

I have managed to retrieve the data from my database. Now I want to insert it in another table as Foreign key. I have following code :

 try {
    $stmt = $pdo->query('SELECT acc_id FROM account_info ORDER BY acc_id DESC LIMIT 1');
        while($row  = $stmt->fetch(PDO::FETCH_OBJ))
        {
            // Assign each row of data to associative array
                $data[] = $row;
        }

        // Return data as JSON
            echo json_encode($data);
 }

How can I insert the value in the table? this is the full code :

<?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
 $key  = strip_tags($_REQUEST['key']);
$data = array();

 switch($key)

{
   // Add a new record to the technologies table
  case "create":

     // Sanitise URL supplied value
     $acc_id                = filter_var($_REQUEST['acc_id'], 
FILTER_SANITIZE_STRING, FILTER_FLAG_ENCODE_LOW);
     $p_fname               = filter_var($_REQUEST['p_fname'], 
FILTER_SANITIZE_STRING, FILTER_FLAG_ENCODE_LOW);
     $p_lname               = filter_var($_REQUEST['p_lname'], 
FILTER_SANITIZE_STRING, FILTER_FLAG_ENCODE_LOW);
     $p_gender              = filter_var($_REQUEST['p_gender'], 
FILTER_SANITIZE_STRING, FILTER_FLAG_ENCODE_LOW);
     $p_condition           = filter_var($_REQUEST['p_condition'], 
FILTER_SANITIZE_STRING, FILTER_FLAG_ENCODE_LOW);
     $p_emergencycontact    = filter_var($_REQUEST['p_emergencycontact'], 
FILTER_SANITIZE_STRING, FILTER_FLAG_ENCODE_LOW);
     $p_birthdate           = filter_var($_REQUEST['p_birthdate'], 
FILTER_SANITIZE_STRING, FILTER_FLAG_ENCODE_LOW); 

try{

    $stmt = $pdo->query('SELECT acc_id FROM account_info ORDER BY acc_id DESC LIMIT 1');
    while($row  = $stmt->fetch(PDO::FETCH_OBJ))
        {
            // Assign each row of data to associative array
                $data[] = $row;
        }

        // Return data as JSON
            echo json_encode($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); 

        $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(':acc_id', $acc_id, PDO::PARAM_STR); 


        $stmt->execute();

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

}
     // Catch any errors in running the prepared statement
     catch(PDOException $e)
     {
        echo $e->getMessage();
     }

 break;
}
?>

I am getting this error:

ERROR SyntaxError: Unexpected token < in JSON at position 0
at JSON.parse (<anonymous>) 

I am getting the error here which links back to the php file:

load()
{
  this.http.get('http://localhost:10080/ionic/patients.php')
  .map(res => res.json())
  .subscribe(data =>
  {
     this.items = data;
  });
}

---

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

Answer 1

I'm pretty sure you could improve your code by removing your while loop and instead go like :

$data = $stmt->fetchAll(PDO::FETCH_OBJ);

Are you sure you're getting expected JSON (tried any var_dump of $data before printing it ?) ?
Isn't it just a simple problem with JavaScript ? Have you tried to use the data you're supposed to get in your JavaScript part ?

It might be a problem of setting headers inside your XMLHttpRequest, and JavaScript doesn't care and give you the JSON anyway...

Now obvious questions :

• I can't see where you connect to your database. Are you connected ?
• You're trying to insert an ID, does MySQL allow you to INSERT auto increment value ? (in which case, isn't acc_id an Int ?)
• You're sending values through $_REQUEST, are you sure you're getting anything through $_REQUEST (btw, check $_GET and $_POST)

I hope it helps