CODEWITHSUNDEEP
phpmysql
Linda May
Linda May

Reputation: 87

cannot insert data into child table SQL phpMyAdmin

basically I have 2 simple tables..the first one is called "user" which is the parent table. The PK is index auto incremented. The second table is called "useradvert". the "id" field acts as a index key which is not auto incremented. whenever I try to insert data, it does not go into the table (useradvert). There's no error at all on my PHP page. I've turned on error reporting. I manage to create a relation table without errors. I've tried to solve the problem for several days and search the internet for answers, but still could not find and understand the problem. Is the problem due to the index key (id) in the child table which is not auto incremented? Should both id key be auto incremented?

Thank you..really need your help tqs..

table definition below for "users"-parent table and "useradvert"-child table;

-- Table structure for table useradvert

CREATE TABLE IF NOT EXISTS useradvert ( id 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 id (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--

-- Table structure for table users

CREATE TABLE IF NOT EXISTS users ( id 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 username (username), KEY id (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=97 ;

--

-- Dumping data for table users

INSERT INTO users (id, name, telno, username, password, date) VALUES (95, 'Test Name', '09999999999', '[email protected]', '$2y$12$fqdmAQk5c8qk8Eh2TWy2n.AdNO.lFjqmi2ruSzk8tsVXcK71OcPae', '2015-12-24 05:00:13'), (96, 'testtwo', '10121212121', '[email protected]', '$2y$12$nHw0CjWCF5AS4VB3mjIBo.o7nxszxXh.t5FWGv3pFe5izWBOo0A0O', '2015-12-24 05:20:19');

--

-- Constraints for dumped tables

--

-- Constraints for table useradvert

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

This is the user page (useracc-test.php) where the user suppose to insert the data into table "useradvert". The page display previous registered data (from table "user" and this page also allows users to insert new data (into table "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>

Upvotes: 0

Views: 1340

Answers (2)

Linda May
Linda May

Reputation: 87

Problem solved.. I forgot the if(isset($_POST['submit']))..LoL.I did some minor changes just to add the isset above.. everything works fine now..

Upvotes: 0

dawsonc623
dawsonc623

Reputation: 2275

The indexed key does not need to be auto-incremented. Instead, you should insert your record into the users table, grab the id for that record, and manually insert that as the id on the useradvert table.

I have a sneaking suspicion your error is a foreign key constraint failure. If you check for errors after running each of your inserts, you will likely see it. The id you give your useradvert must exist as an id on the users table first.

Upvotes: 0

Related Questions