php - add records in two different tables -
i created table stores 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 1 keep first name , last name 2 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 table has userid , dataid 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 inserts data is
<?php session_start(); if ($_session['username']) { echo "welcome, ".$_session['username']."! <a href='logout.php'>logout</a>"; } else die("you must 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 submitdata.php page stores data in database
<?php session_start(); if ($_session['username']) { echo "welcome, ".$_session['username']."! <a href='logout.php'>logout</a>"; } else die("you must logged in!!"); $con=mysql_connect ("localhost","****","****"); mysql_select_db("project",$con); @$firstname=$_post['firstname']; @$lastname=$_post['lastname']; $s="insert data(`firstname`,`lastname`) values ('$firstname','$lastname')"; echo "you have submit questions"; mysql_query ($s); ?> furthermore have 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 database"); $query = mysql_query("select * users 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 login!!!!! continue survey <a href='mainpage.php'>here</a>"; $_session['username']=$username; } else { echo "<b>incorrect password!!!!</b>"; } } else //die("that user not exist"); echo "<b>that user not exist</b>"; } else echo "<b>you must enter username , 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 aim user login or register, fill page , add record data (with data) , jointable (with ids of record) @ same time. problem registration , login pages have can insert users data , can insert data in data table not have record in jointable. why happen?
to answer question: "why happen?"
because not inserting data in join_table , therefore wont you.
solution:
you need whats called lastinsertid returned once insert record in data table. here example in pdo , recommend using pdo:
//prepare statement $stmt_data = $this->db->prepare(' insert 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 jointable id $stmt_jointable = $this->db->prepare(' insert 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:
- get rid of mysql_* functions , move on using pdo or mysqli
- for
jointabledont need autoincrement primary key, can make both user id , data id primary keys, since same user can not have multiple data records. isnt 1-1 relationship?
Comments
Post a Comment