在数据库中插入所选数据 [英] Insert Selected data in database

问题描述

基本上我想在account_info表的patient_info表中插入一个外键acc_id。

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;
}
?>

我收到此错误：

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

我是得到错误链接回php文件：

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;
  });
}

---

解决方案：将选定数据作为外键和SQLSTATE插入[23000]：完整性约束违规：1048

推荐答案

我很确定你可以通过删除while循环改进代码像：

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

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

你确定你得到了预期的JSON（在打印之前尝试过任何$ data的var_dump吗？） ？

这不仅仅是JavaScript的一个简单问题吗？您是否尝试过在JavaScript部分中使用的数据？

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 ?

这可能是在XMLHttpRequest中设置标头的问题，而JavaScript则不然照顾并给你JSON ...

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

现在显而易见的问题：

• 我无法看到您连接到数据库的位置。您是否已连接？


• 您正在尝试插入ID，MySQL是否允许您插入自动增量值？ （在这种情况下，不是acc_id是一个Int？）


• 你通过$ _REQUEST发送值，你确定你通过$ _REQUEST得到任何东西（顺便说一下，检查$ _GET和$ _POST）

我希望它有所帮助

这篇关于在数据库中插入所选数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！