首页 文章

无法将数据插入子表SQL phpMyAdmin

提问于
浏览
0

基本上我有2个简单的表...第一个叫做“user”,它是父表 . PK是索引自动递增 . 第二个表称为“useradvert” . “id”字段用作不自动递增的索引键 . 每当我尝试插入数据时,它都不会进入表格(useradvert) . 我的PHP页面上根本没有错误 . 我打开了错误报告 . 我设法创建一个没有错误的关系表 . 我试图解决这个问题好几天,并在互联网上搜索答案,但仍然无法找到并理解问题 . 问题是由于子表中的索引键(id)不是自动递增的吗?两个id键是否应自动递增?

谢谢..真的需要你的帮助tqs ..

下面的表定义为“users”-parent table和“useradvert”-child表;

- 表useradvert的表结构

CREATE TABLE IF NOT EXISTS useradvertid int(10)unsigned NOT NULL, name2 varchar(60)COLLATE utf8_unicode_ci NOT NULL, color2 varchar(60)COLLATE utf8_unicode_ci NOT NULL, hobby2 varchar(60)COLLATE utf8_unicode_ci NOT NULL,KEY idid ))ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci;


- 表用户的表结构

CREATE TABLE IF NOT EXISTS usersid int(10)unsigned NOT NULL AUTO_INCREMENT, name varchar(60)COLLATE utf8_unicode_ci NOT NULL, telno varchar(11)COLLATE utf8_unicode_ci NOT NULL, username varchar(60)COLLATE utf8_unicode_ci NOT NULL, password varchar( 60)COLLATE utf8_unicode_ci NOT NULL, date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY( id ),UNIQUE KEY usernameusername ),KEY idid ))ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci AUTO_INCREMENT = 97;

- 转储表用户的数据

INSERT INTO usersidnametelnousernamepassworddate )VALUES(95,'Test Name','09999999999','test@test.com','$2y$12$fqdmAQk5c8qk8Eh2TWy2n.AdNO.lFjqmi2ruSzk8tsVXcK71OcPae','2015-12-24 05:00:13'),(96,'testtwo','10121212121','test2@mail.com','$2y$12$nHw0CjWCF5AS4VB3mjIBo.o7nxszxXh.t5FWGv3pFe5izWBOo0A0O','2015-12-24 05:20:19');

- 转储表的约束

- 表useradvert的约束

ALTER TABLE useradvert ADD CONSTRAINT useradvert_ibfk_1 FOREIGN KEY( id )REFERENCES usersid );


这是用户页面(useracc-test.php),用户希望将数据插入表“useradvert” . 页面显示以前注册的数据(来自表“user”,此页面还允许用户插入新数据(进入表“useradvert”) .

<?php

//useracc-test.php

/**
 * Start the session.
 */
session_start();
ini_set('display_errors', 1);
error_reporting(E_ALL);



// require 'lib/password.php';
require 'connect-test.php';


$userName= isset($_POST['username']) ? $_POST['username'] : '';




$query = "SELECT id, name, username, telno FROM users WHERE username = ?";
$stmt = $conn->prepare($query);
$stmt->bind_param('s', $userName);
$stmt->execute();
$res = $stmt->get_result();


 ?>




<html>
<head>
<style type="text/css">
#apDiv2 {
    position: absolute;
    left: 51px;
    top: 238px;
    width: 237px;
    height: 93px;
    z-index: 1;
}
#apDiv1 {
    position: absolute;
    left: 134px;
    top: 123px;
    width: 234px;
    height: 104px;
    z-index: 2;
}
#apDiv3 {
    position: absolute;
    left: 58px;
    top: 146px;
    width: 219px;
    height: 61px;
    z-index: 2;
}
#apDiv4 {
    position: absolute;
    left: 302px;
    top: 102px;
    width: 365px;
    height: 123px;
    z-index: 3;
}
</style>
<link href="SpryAssets/SpryTabbedPanels.css" rel="stylesheet" type="text/css">
<script src="SpryAssets/SpryTabbedPanels.js" type="text/javascript"></script>
</head>
<body>
Your Personal details:</p>
      <p><?php while($row = $res->fetch_array()): ?>
<p><?php echo $row['id']; ?></p>
<p><?php echo $row['name']; ?></p>
<p><?php echo $row['username']; ?></p>
<p><?php echo $row['telno']; ?>





  <?php     

  // $userid = $_POST['id'];
  $stmt=$conn->prepare("INSERT INTO useradvert (id,name2,color2,hobby2) VALUES (?,?,?,?)");
  $stmt->bind_param("isss", $id, $name2, $color2, $hobby2);
  $stmt->execute();
  if (!$stmt)
  { printf("Errormessage: %s\n", $mysqli->error);}
  else {

  echo "New records created successfully";}

$stmt->close();
$conn->close();

    ?>  





<form name="form2" method="post" action="useracc-test.php">
        <p>INSERT YOUR INTEREST:</p>
        <p>     
        </p>
          ID:
      <input name="id" type="hidden" id="id" value="<?php echo $row['id']; ?>">


  <p>Name :
          <input type="text" name="name2" id="name2">
        </p>
        <p>
          <label for="warna2"></label>
          Color :
          <input type="text" name="color2" id="color2">
        </p>
        <p>
          <label for="hobi2"></label>
          Hobby:
          <input type="text" name="hobby2" id="hobby2">
        </p>
        <p>
          <input type="submit" name="submit" id="submit" value="submit">
       </p>
        <p>&nbsp;</p>
      </form>       

               <?php endwhile; ?>

               </body>
               </html>

2 回答

  • 0

    问题解决了..我忘了if(isset($ _ POST ['submit'])).. LoL.I做了一些小改动只是为了添加上面的isset ..一切正常现在..

  • 0

    索引键不需要自动递增 . 相反,您应该将您的记录插入 users 表,抓取该记录的 id ,然后在 useradvert 表上手动插入 id .

    我怀疑你的错误是外键约束失败 . 如果在运行每个插入后检查错误,您可能会看到它 . 您给 useradvertid 必须首先在 users 表上作为 id 存在 .

相关问题