CODEWITHSUNDEEP
phpmysqlforms
dali1985
dali1985

Reputation: 3313

add records in two different tables

I created a table which stores the users details

CREATE TABLE IF NOT EXISTS `users` (
  `userid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(25) NOT NULL,
  `username` varchar(25) NOT NULL,
  `password` varchar(25) NOT NULL,
  PRIMARY KEY (`userid`),
  KEY `userid` (`userid`),
  KEY `userid_2` (`userid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=18 ;

this one is to keep the first name and the last name from two textboxes

CREATE TABLE IF NOT EXISTS `data` (
  `dataid` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(25) NOT NULL,
  `lastname` varchar(25) NOT NULL,
  PRIMARY KEY (`surveyid`),
  KEY `firstname` (`firstname`),
  KEY `firstname_2` (`firstname`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

and this table has userid and dataid as foreign keys.

CREATE TABLE IF NOT EXISTS `jointable` (
`jointableid` int(11) NOT NULL AUTO_INCREMENT,
`dataid` int(11) NOT NULL,
`userid` int(11) NOT NULL,
PRIMARY KEY (`jointableid`),
KEY `surveyid` (`dataid`,`userid`),
KEY `userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

ALTER TABLE `jointable`
  ADD CONSTRAINT `lookup_ibfk_2` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON DELETE CASCADE ON UPDATE NO ACTION,
  ADD CONSTRAINT `lookup_ibfk_1` FOREIGN KEY (`dataid`) REFERENCES `data` (`dataid`) ON DELETE CASCADE ON UPDATE NO ACTION;

my page which inserts the data is

<?php 
session_start();

if ($_SESSION['username'])
{
echo "Welcome, ".$_SESSION['username']."! <a href='logout.php'>Logout</a>";
}
else
die("You must be logged in!!");
?>

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<script src="question.js"></script>
<title>Questionnaire</title>
<style type="text/css"> 
    span {color: #FF00CC}
</style>
</head>
<body  background="images/good.jpg">
<h1></h1>
<form name="quiz" method="post" action="submitdata.php">

First Name: <input type="text" name="firstname" id="fname"/>
<p></p>
Last Name: <input type="text" name="lastname" id="lname"/>
<input type="submit" name="submitbutton" value="Go"></input>
<input type="reset" value="clear all"></input>
</form>
</body>
</html>

and finally submitdata.php is the page which stores the data in the database

<?php
session_start();

if ($_SESSION['username'])
{
echo "Welcome, ".$_SESSION['username']."! <a href='logout.php'>Logout</a>";
}
else
die("You must be logged in!!");
$con=mysql_connect ("localhost","****","****");
mysql_select_db("project",$con);
@$firstname=$_POST['firstname'];
@$lastname=$_POST['lastname'];

$s="INSERT INTO data(`firstname`,`lastname`) VALUES ('$firstname','$lastname')";
echo "You have successfully submit your questions";
 mysql_query ($s);
?>

Furthermore I have this login page

<?php 
session_start();

@$username = $_POST['username'];
@$password = $_POST['pass'];

if(@$_POST['Submit']){
if($username&&$password)
{
$connect = mysql_connect("localhost","****","****") or die("Cannot Connect");
mysql_select_db("project") or die("Cannot find the database");

$query = mysql_query("SELECT * FROM users WHERE username='$username'");
$numrows = mysql_num_rows($query);
if($numrows!=0)
{
    while ($row = mysql_fetch_assoc($query))
    {
        $dbusername = $row['username'];
        $dbpassword = $row['password'];
    }
    if($username==$dbusername&&$password==$dbpassword)
    {
        echo "You are login!!!!! Continue now with the survey <a href='mainpage.php'>here</a>";
        $_SESSION['username']=$username;
    }
    else
    {
        echo "<b>Incorrect Password!!!!</b>";
    }
}
else
    //die("That user does not exist");
    echo "<b>That user does not exist</b>";
}
else
echo "<b>You must enter a username and a password</b>";
}
?>

<html xmlns="http://www.w3.org/1999/xhtml">
<head>   
<title>Login Page</title>
</head>
<body>

<h2>Login Page</h2>
<form name="loginform" method='POST'>

<fieldset>
<legend>Form</legend>
    <label>Username: <input type="text" name="username"/><span>*</span></label><br/>
    <label>Password: <input type="password" name="pass"/><span>*</span></label>
    <input class="placeButtons" type="reset" value='Reset'/>
    <input class="placeButtons" type="submit" name="Submit" value='Login'/>
    <a href='registration.php'>Register</a>
</fieldset>

</form>
</body>
</html>

So my aim is the user who login or register, fill the page and then add the record to the data (with the data) and jointable (with the ids of the record) at the same time. The problem is with the registration and login pages which I have I can insert the users data and also I can insert the data in the DATA table but I do not have a record in jointable. Why is this happen?

Upvotes: 0

Views: 188

Answers (1)

GGio
GGio

Reputation: 7653

To answer your question: "why is this happen?"

Because you are not inserting any data in join_table and therefore it wont do it for you.

Solution:

You need whats called lastInsertId to be returned once you insert a record in data table. Here is an example in PDO and I would recommend using PDO:

//prepare statement
$stmt_data = $this->db->prepare('
                 INSERT INTO data(`firstname`,`lastname`) 
                 VALUES (:fname, :lname)
             ');

//bind parameter/variables
$stmt_data->bindParam(':fname', $firstName, PDO::PARAM_STR);
$stmt_data->bindParam(':lname', $lastName, PDO::PARAM_STR);

//insert row
$res = $stmt_data->execute();

//get last isnerted ID
if ($res) {
    $id = $this->db->lastInsertId();

    //NOW INSERT INTO jointable with this id
    $stmt_jointable = $this->db->prepare('
                          INSERT INTO join_table(`userid`, dataid`)
                          VALUES (:uid, :dataid)
                      ');

    $stmt_jointable->bindParam(':uid', $userID, PDO::PARAM_INT);
    $stmt_jointable->bindParam(':dataid', $id, PDO::PARAM_INT);

    //insert
    $stmt->execute();
}

TIPS:

  1. Get rid of mysql_* functions and move on using PDO or MySQLi
  2. For jointable you dont need autoincrement primary key, you can just make both user ID and data ID be the primary keys, since same user can not have multiple data records. Isnt it 1-1 relationship?

Upvotes: 1

Related Questions