I have two tables: one for student records and one for siblings.
When I want to update tables, I repeat them in textareas.
I have no problem doing this for the student table, but for the siblings table, if the student has two or more brothers, my update does not work because the name of the text field becomes the last line (last relationship).
How can I deal with this one-to-many relationship?
Here is my code:
<?php $database="sy_database"; mysql_connect ("localhost", "root", ""); @mysql_select_db($database) or die( "Unable to select database"); $id=$_GET['up']; $sno=$_GET['s_no']; $order = "SELECT * FROM sy_form WHERE reg_no='$id'"; $result = mysql_query($order); $row = mysql_fetch_array($result); ?> <?php $con = mysql_connect("localhost","root",""); if (!$con) { die('Could not connect: ' . mysql_error()); } echo "<form method=post action=ambot.php>"; mysql_select_db("sy_database", $con); $result = mysql_query("SELECT * FROM sy_form where reg_no='$id'"); echo "<body align=center>"; echo"Student Record"; echo "<table border='1' width=1000px margin=0 padding=0 align=center> <tr bgcolor=yellow> <th>Reg No.</th> <th>Name</th> <th>Date of Birth</th> <th>Age</th> <th>Class</th> <th>School</th> </tr>"; while($row = mysql_fetch_array($result)) { echo "<tr align=center>"; echo "<td><textarea readonly=readonly rows=1 cols=3 name=reg_no>". $row['reg_no'] ." </textarea></td>"; echo "<td><textarea rows=1 cols=20 name=name>" .$row['name']. "</textarea></td>"; echo "<td><textarea rows=1 cols=20 name=birth>" . $row['birth'] . "</textarea></td>"; echo "<td><textarea rows=1 cols=3 name=age>" . $row['age'] . "</textarea></td>"; echo "<td><textarea rows=1 cols=3 name=sclass>" . $row['sclass'] . "</textarea> </td>"; echo "<td><textarea rows=1 cols=20 name=school>" . $row['school'] . "</textarea></td>"; echo "</tr>"; } echo "</table>"; $result = mysql_query("SELECT * FROM sy_form where reg_no='$id'"); echo "<br><table border='1' width=1250px margin=0 padding=0 align=center> <tr bgcolor=yellow> <th>Father Name</th> <th>Father Occupation</th> <th>Mother Name</th> <th>Mother Occupation</th> <th>Address</th> <th>Phone No.</th> <th>Cellphone No.</th> <th>Email</th> </tr>"; while($row = mysql_fetch_array($result)) { echo "<tr align=center>"; echo "<td><textarea rows=1 cols=20 name=fname>" . $row['fname'] . "</textarea></td>"; echo "<td><textarea rows=1 cols=10 name=focc>" . $row['focc'] . "</textarea></td>"; echo "<td><textarea rows=1 cols=20 name=mname>" . $row['mname'] . "</textarea></td>"; echo "<td><textarea rows=1 cols=10 name=mocc>" . $row['mocc'] . "</textarea></td>"; echo "<td><textarea rows=1 cols=25 name=address>" . $row['address'] . "</textarea> </td>"; echo "<td><textarea rows=1 cols=8 name=phone>" . $row['phone'] . "</textarea></td>"; echo "<td><textarea rows=1 cols=10 name=cp>" . $row['cp'] . "</textarea></td>"; echo "<td><textarea rows=1 cols=20 name=email>" . $row['email'] . "</textarea></td>"; echo "</tr>"; } echo "</table>"; echo"<br/>Student Sibling(s)"; $result = mysql_query("SELECT * FROM siblings where reg_no='$id' AND s_no<min($sno)"); echo "<br/><table border='1' margin=0 padding=0 align=center> <tr bgcolor=yellow> <th>Reg No.</th> <th>Sibling No.</th> <th>Name</th> <th>Age</th> <th>School</th> </tr>"; while($row = mysql_fetch_array($result)) { echo "<tr align=center>"; echo "<td align=right><textarea readonly=readonly rows=1 cols=3 name=reg_no>". $row['reg_no'] . "</textarea></td>"; echo "<td><textarea rows=1 cols=3>" . $row['s_no'] . "</textarea></td>"; echo "<td><textarea rows=1 name=sname>" . $row['sname'] . "</textarea></td>"; echo "<td><textarea rows=1 cols=3 name=sage>" . $row['sage'] . "</textarea></td>"; echo "<td><textarea rows=1 name=sschool>" . $row['sschool'] . "</textarea></td>"; echo "</tr>"; } echo "</table>"; echo "<input type =submit value =update />"; echo "</form>"; mysql_close($con); ?> <?php $database="sy_database"; $id=$_POST['id']; $ip=$_POST['ip']; $mac=$_POST['mac']; $location=$_POST['location']; $use=$_POST['use']; mysql_connect ("localhost", "root", ""); @mysql_select_db($database) or die( "Unable to select database"); $order = "UPDATE sy_form SET name='$name' WHERE reg_no='$reg_no'"; $result=mysql_query($order); if($result){ echo "<p style=margin-top:50px;><BR><BR><BR></p>"; echo "<a href='view.php'>Back to Records</a>"; } else { echo "ERROR"; echo $name; } ?>
here is my update code:
$con = mysql_connect("localhost","root",""); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("sy_database", $con); $result = mysql_query("UPDATE sy_form SET name='$name', birth='$birth', age='$age', sclass='$sclass', school='$school' , fname='$fname', focc='$focc', mname='$mname', mocc='$mocc', address='$address', phone='$phone', cp='$cp', email='$email' where reg_no='$reg'"); $result = mysql_query("UPDATE siblings SET sname='$sname',sage='$sage', sschool='$sschool' where reg_no='$reg'"); echo("record updated."); mysql_close($con); ?>
HERE is my html code for INSERT:
<html> <head> <style type = "text/css"> body { font-family: Arial; margin: 0 auto; } table{ margin: 0 auto; } #masterdiv { width: 800px; margin: 0 auto; } #header { margin: 0 auto; width: 796px; height: 10; background-image: url('header.jpg'); } #fillForm { margin: 0 auto; width: 800px; background-color: #FFFFFF; } #footer { margin: 0 auto; width: 798px; height: 75px; background-image: url('footer.jpg'); } </style> <title>Registration Form</title> </head> <body bgcolor="black"> <div id = "masterdiv"> <div id = "header"></div> <div id = "fillForm"> <p align="right" style="color:red;font-size:60px;"/> Registration Form</p> <table cellspacing="0" cellpadding="0" border="0"> <form action = "cc.php" method ="post"> <tr bgcolor="red"> <td colspan="3"> </td> <td><b>Registration No:</b></td> <td><input type = "text" name = "reg_no"></td> </tr> <tr><td> </td></tr> <tr> <td>Name: </td> <td><input type = "text" size = "40" name = "name"></td> <td> </td> <td>Date of Birth: </td> <td><input type = "text" name = "birth"></td> </tr> <tr> <td>Age: </td> <td><input type = "text" size = "20" name = "age"></td> <td> </td> <td>Class as at January: </td> <td><input type = "text" name = "sclass"></td> </tr> <tr> <td>School: </td> <td colspan = "4"><input type = "text" size = "80" name = "school"></td> </tr> <tr> <td>Father Name: </td> <td colspan = "4"><input type = "text" size = "80" name = "fname"></td> </tr> <tr> <td>Father Occupation: </td> <td colspan = "4"><input type = "text" size = "80" name = "focc"></td> </tr> <tr> <td>Mother Name: </td> <td colspan = "4"><input type = "text" size = "80" name = "mname"></td> </tr> <tr> <td>Mother Occupation: </td> <td colspan = "4"><input type = "text" size = "80" name = "mocc"></td> </tr> <tr><td> </td></tr> <tr><td> </td></tr> <tr> <td colspan = "5">Siblings (if any)</td> </tr> <tr> <td> </td> <td>Name</td> <td>Age</td> <td> </td> <td>School</td> </tr> </table> <table> <tr> <td>1. <input type = "text" size = "50" name = "sname"></td> <td><input type = "text" size = "25" name = "sage"></td> <td><input type = "text" size = "30" name = "sschool"></td> </tr> <tr> <td>2. <input type = "text" size = "50" name = "sname2"></td> <td><input type = "text" size = "25" name = "sage2"></td> <td><input type = "text" size = "30" name = "sschool2"></td> </tr> <tr> <td>3. <input type = "text" size = "50" name = "sname3"></td> <td><input type = "text" size = "25" name = "sage3"></td> <td><input type = "text" size = "30" name = "sschool3"></td> </tr> <tr align = "right"> <td colspan = "3">Address: <input type = "text" size = "80" name = "address"></td> </tr> <tr align = "right"> <td colspan = "3">Phone Number: <input type = "text" size = "80" name = "phone"></td> </tr> <tr align = "right"> <td colspan = "3">Cellphone Number (Mother/Father)<input type = "text" size = "80" name = "cp"></td> </tr> <tr align = "right"> <td colspan = "3">Email: <input type = "text" size = "80" name = "email"></td> </tr> <tr><td> </td></tr> <tr align = "center"> <td colspan = "3"> <input type = "submit" value = "Submit" name = "Submit"> </form> <form action="view.php" method="post" target="_blank"> <input type = "submit" value = "View/Update/Delete" name = "View" > </form> </td> </tr> <tr><td> </td></tr> </table> </div> <div id = "footer"></div> </div> </body> </html>
and here is the PHP code for INSERT:
<?php //student record details $name = $_POST['name']; $birth = $_POST['birth']; $age = $_POST['age']; $sclass = $_POST['sclass']; $school = $_POST['school']; $fname = $_POST['fname']; $focc = $_POST['focc']; $mname = $_POST['mname']; $mocc = $_POST['mocc']; $address = $_POST['address']; $phone = $_POST['phone']; $cp = $_POST['cp']; $email = $_POST['email']; $reg = $_POST['reg_no']; //siblings record $sname = $_POST['sname']; $sage = $_POST['sage']; $sschool = $_POST['sschool']; $sname2 = $_POST['sname2']; $sage2 = $_POST['sage2']; $sschool2 = $_POST['sschool2']; $sname3 = $_POST['sname3']; $sage3 = $_POST['sage3']; $sschool3 = $_POST['sschool3']; // connect to mysql $conn = mysql_connect("localhost", "root", "") or die("ERR: Connection"); // connect to database $db = mysql_select_db("sy_database", $conn) or die("ERR: Database"); // create mysql query // Insert a row of information into the table "sy_form" $sql = "INSERT INTO sy_form (reg_no, name, birth, age, sclass, school, fname, focc, mname, mocc, address, phone, cp, email) VALUES('".$reg."', '".$name."', '".$birth."', '".$age."', '".$sclass."', '".$school."', '".$fname."', '".$focc."', '".$mname."', '".$mocc."','".$address."','".$phone."','".$cp."','".$email."')"; if (!mysql_query($sql,$conn)) { die('Error: ' . mysql_error()); } echo "1 record added"; // execute query $exec = mysql_query($sql, $conn); // Insert a row of information into the table "sy_form" (1st sibling) $sql_1 = "INSERT INTO siblings (reg_no,s_no, sname, sage, sschool) VALUES('".$reg."','', '".$sname."', '".$sage."', '".$sschool."')"; if($sname==null) return 0; else $exec = mysql_query($sql_1, $conn); // Insert a row of information into the table "sy_form" (2nd sibling) $sql_2 = "INSERT INTO siblings (reg_no,s_no, sname, sage, sschool) VALUES('".$reg."','', '".$sname2."', '".$sage2."', '".$sschool2."')"; if($sname2==null) return 0; else $exec = mysql_query($sql_2, $conn); // Insert a row of information into the table "sy_form" (3rd sibling) $sql_3 = "INSERT INTO siblings (reg_no,s_no, sname, sage, sschool) VALUES('".$reg."','', '".$sname3."', '".$sage3."', '".$sschool3."')"; if($sname3==null) return 0; else $exec = mysql_query($sql_3, $conn); echo ("The following data has been added to sy_database"); ?>