inserting data in three different tables in mysql database using php

Question

punji 0 Newbie Poster

9 Years Ago

hello PHP tycoons, i beg for help in giving me a way forward to write php script which will solve the problem i'm facing now. currently i'm developing basic student information system but in php knowledge i'm not that good, thus why i'm asking for help.so what i want to do is how can i add student details in student table and then add the school which a particular student belongs in just one html form and then writing php script which will process those entered data from that single html form. so that when i want to know what student belong to certain school i can just write a query which can i retrieve all the student from that school by using school code. let's say i have 80 different school and each school has it's own student and each student is uniquely identified by his/her student code and it's not auto increment that apply to school code too. i have two tables namely student and school
school table is as follows

1   school_code varchar(250)     Primary key
2   school_name varchar(250)





student table is as follows
    1   student_code    varchar(250) primary key
    2   firstname   varchar(250)
    3   middlename  varchar(250)
    4   lastname    varchar(250)
    5   gender  varchar(250)
    6   school_code varchar(250) foreign key



any help will be much appreciated thanks in advance.

mysql php

3 Contributors
5 Replies
325 Views
4 Days Discussion Span
Latest Post 9 Years Ago Latest Post by lps

lps 71 Posting Pro in Training

9 Years Ago

Having a few question on you post before I can help:
1. Are you asking about the inserting part or querying part?
2. Can you provide the html codes you done so far for reference?
3. Can you provide the expected result?

rubberman 1,355 Nearly a Posting Virtuoso

9 Years Ago

What database are you using?
Are you building the database schema directly, or in PHP?
What API are you using to implement this in PHP?
Show your code.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

punji 0 Newbie Poster · Answer 1 · 2015-04-09T20:49:03+00:00

To Ips
1) I was asking for both part inserting and querying but specifically it was inserting. firstly i wanted to add data to both tables namely student and school at once using a combined html form and a combined php script to process those data, but i changed my mind and i decided to add data to those tables separately and everything worked fine, below is my html form to add data in student table

<?php error_reporting(E_ALL ^ E_NOTICE); ?>
<html>
<body>
<div>
<div>
<form action="connstudent.php" method="post">
    <table>
    <tr><td colspan="6">&nbsp;</td></tr>
    <tr>
    <td width="9%">Firstname<?php echo $mf;?></td>
    <td width="17%"><input name="firstname" type="text" size="30" value="<?php echo $fn;?>" /></td>
    <td width="10%">Middlename</td>
    <td width="18%"><input name="middlename" type="text" size="30"  value="<?php echo $mn;?>"/></td>
    <td width="12%">Lastname<?php echo $ml;?></td>
    <td width="34%"><input name="lastname" type="text" size="30" value="<?php echo $ln;?>"/></td>
    </tr>
    <tr><td colspan="6">&nbsp;</td></tr>
    <tr>
    <td>Student Code<?php echo $mstudentcode;?></td>
    <td><input name="student_code" type="text" size="30" value="<?php echo $studentcode;?>"/></td>
    <td>Gender<?php echo $mg;?></td>
    <td><select name="gender">
      <option value="Select Gender">--Select--</option>
      <option value="Male">Male</option>
      <option value="Female">Female</option>
      </select></td>
      <td>&nbsp;</td>
      <td>&nbsp;
      </td>
    </tr>
     <tr><td colspan="4">&nbsp;</td></tr>
    <tr>
     <td>School Code<?php echo $mschoolcode;?></td>
     <td><input name="school_code" type="text" size="30" value="<?php echo $schoolcode;?>"/></td>
     </tr>
      <tr><td colspan="6">&nbsp;</td></tr>
     <tr>
    <td>&nbsp;</td><td colspan="6"><input type="submit" name="save" value="Add Student" /></td>
     </tr>
     <tr><td colspan="6"><?php echo $sms1.$sms.$sms2;?></td></tr>
    </table>
    </form>
    </div>
    <div id="footer">Copyright <?php echo date("Y", time()); ?></div>
</div>
</body>
</html>

and here is my php script to process those data

<?php error_reporting(E_ALL ^ E_NOTICE); ?>
<?php
session_start();
if(isset($_POST['save']))
{
//  validating firstname
    if(empty($_POST['firstname']))
    {
        $mf='<font color="red"><b>**</b></font>';
    }
    else
    {
        $fn=$_POST['firstname'];
    }
// No validation for middlename it can be empty
    if(empty($_POST['middlename']))
    {
        $mn='NILL';
    }
    else
    {
        $mn=$_POST['middlename'];
    }
// validating lastname
    if(empty($_POST['lastname']))
    {
        $ml='<font color="red"><b>**</b></font>';
    }
    else
    {
        $ln=$_POST['lastname'];
    }
// validating student code
    if(empty($_POST['student_code']))
    {
        $mstudentcode='<font color="red"><b>**</b></font>';
    }
    else
    {
        $student_code=$_POST['student_code'];
    }

// validating school code
    if(empty($_POST['school_code']))
    {
        $mschoolcode='<font color="red"><b>**</b></font>';
    }
    {
        $school_code=$_POST['school_code'];
    }
// validating student gender
    if($_POST['gender']=='Select Gender')
    {
        $mg='<font color="red"><b>**</b></font>';
    }
    else
    {
        $gn=$_POST['gender'];
    }
// checking if there is any error message, if no error proceed, if there is error, display the error
//  Then exit the script and redirect at the same page
    if($mf||$ml||$mstudentcode||$mschoolcode||$mg||$sms)
    {
        $sms1='<font color="red"><b>Error found,please check **</b></font><br/>';
        include 'savestudent.php';
        exit;
    }
// if there is no error include connection file
    if($fn&&$mn&&$ln&&$student_code&&$school_code&&$gn)
    {
//      include 'mysqli_connect.php';
      require_once ('../../mysqli_connect.php');
// check for duplicate student code
        $checkstudent= "select * from student where student_code='".$student_code."'";
        $r = mysqli_query($dbc, $checkstudent);
        if(mysqli_num_rows($r)==0)
// if zero row is returned then add the student details and then populate student_school table in order to know which school each student belongs
        {
        $addstudent= "insert into student(student_code,firstname,middlename,lastname,gender, school_code) values ('".$student_code."','".$fn."','".$mn."','".$ln."','".$gn."', '".$school_code."')";
        $k = mysqli_query($dbc, $addstudent);
        if ($k)
        {
       $sms1='<font color="green"><b>Student Data Submitted Successfully</b></font><br/>';
            include 'savestudent.php';
                        exit;
        }
        else
        {
            $sms1='<font color="red"><b>Failed To Add Student Data</b></font><br/>';
            include 'savestudent.php';
                        exit;
        }
        }
        else
// if a user insert a student code which is already in the database an error message will occur
        {   $sms1='<font color="red"><b>Student Code already exist In The Database</b></font><br/>';
            include 'savestudent.php';
                        exit;
        }
    }
    }
?>

Now what i ask you(Ips) or any member is how can i write a query which will join those two tables namely student table and school table and then retrieve information concerning a student and which school that student belongs and then display in browser in a particular student profile.

punji 0 Newbie Poster · Answer 2 · 2015-04-09T21:12:24+00:00

To Rubberman
1) I'm using mysql database
2) I built the database schema directly in phpmyadmin
3) I'm using xampp
4) As for my code i have just decided to insert those data in separate html form and separate php script to process those data
below is my html form for adding student details in student table

<?php error_reporting(E_ALL ^ E_NOTICE); ?>
<html>
<body>
<div>
<div>
<form action="connstudent.php" method="post">
    <table>
    <tr><td colspan="6">&nbsp;</td></tr>
    <tr>
    <td width="9%">Firstname<?php echo $mf;?></td>
    <td width="17%"><input name="firstname" type="text" size="30" value="<?php echo $fn;?>" /></td>
    <td width="10%">Middlename</td>
    <td width="18%"><input name="middlename" type="text" size="30"  value="<?php echo $mn;?>"/></td>
    <td width="12%">Lastname<?php echo $ml;?></td>
    <td width="34%"><input name="lastname" type="text" size="30" value="<?php echo $ln;?>"/></td>
    </tr>
    <tr><td colspan="6">&nbsp;</td></tr>
    <tr>
    <td>Student Code<?php echo $mstudentcode;?></td>
    <td><input name="student_code" type="text" size="30" value="<?php echo $studentcode;?>"/></td>
    <td>Gender<?php echo $mg;?></td>
    <td><select name="gender">
      <option value="Select Gender">--Select--</option>
      <option value="Male">Male</option>
      <option value="Female">Female</option>
      </select></td>
      <td>&nbsp;</td>
      <td>&nbsp;
      </td>
    </tr>
     <tr><td colspan="4">&nbsp;</td></tr>
    <tr>
     <td>School Code<?php echo $mschoolcode;?></td>
     <td><input name="school_code" type="text" size="30" value="<?php echo $schoolcode;?>"/></td>
     </tr>
      <tr><td colspan="6">&nbsp;</td></tr>
     <tr>
    <td>&nbsp;</td><td colspan="6"><input type="submit" name="save" value="Add Student" /></td>
     </tr>
     <tr><td colspan="6"><?php echo $sms1.$sms.$sms2;?></td></tr>
    </table>
    </form>
    </div>
    <div id="footer">Copyright <?php echo date("Y", time()); ?></div>
</div>
</body>
</html>

And here is my php script to process the above html form and every thing works fine

<?php error_reporting(E_ALL ^ E_NOTICE); ?>
<?php
session_start();
if(isset($_POST['save']))
{
//  validating firstname
    if(empty($_POST['firstname']))
    {
        $mf='<font color="red"><b>**</b></font>';
    }
    else
    {
        $fn=$_POST['firstname'];
    }
// No validation for middlename it can be empty
    if(empty($_POST['middlename']))
    {
        $mn='NILL';
    }
    else
    {
        $mn=$_POST['middlename'];
    }
// validating lastname
    if(empty($_POST['lastname']))
    {
        $ml='<font color="red"><b>**</b></font>';
    }
    else
    {
        $ln=$_POST['lastname'];
    }
// validating student code
    if(empty($_POST['student_code']))
    {
        $mstudentcode='<font color="red"><b>**</b></font>';
    }
    else
    {
        $student_code=$_POST['student_code'];
    }

// validating school code
    if(empty($_POST['school_code']))
    {
        $mschoolcode='<font color="red"><b>**</b></font>';
    }
    {
        $school_code=$_POST['school_code'];
    }
// validating student gender
    if($_POST['gender']=='Select Gender')
    {
        $mg='<font color="red"><b>**</b></font>';
    }
    else
    {
        $gn=$_POST['gender'];
    }
// checking if there is any error message, if no error proceed, if there is error, display the error
//  Then exit the script and redirect at the same page
    if($mf||$ml||$mstudentcode||$mschoolcode||$mg||$sms)
    {
        $sms1='<font color="red"><b>Error found,please check **</b></font><br/>';
        include 'savestudent.php';
        exit;
    }
// if there is no error include connection file
    if($fn&&$mn&&$ln&&$student_code&&$school_code&&$gn)
    {
//      include 'mysqli_connect.php';
      require_once ('../../mysqli_connect.php');
// check for duplicate student code
        $checkstudent= "select * from student where student_code='".$student_code."'";
        $r = mysqli_query($dbc, $checkstudent);
        if(mysqli_num_rows($r)==0)
// if zero row is returned then add the student details and then populate student_school table in order to know which school each student belongs
        {
        $addstudent= "insert into student(student_code,firstname,middlename,lastname,gender, school_code) values ('".$student_code."','".$fn."','".$mn."','".$ln."','".$gn."', '".$school_code."')";
        $k = mysqli_query($dbc, $addstudent);
        if ($k)
        {
       $sms1='<font color="green"><b>Student Data Submitted Successfully</b></font><br/>';
            include 'savestudent.php';
                        exit;
        }
        else
        {
            $sms1='<font color="red"><b>Failed To Add Student Data</b></font><br/>';
            include 'savestudent.php';
                        exit;
        }
        }
        else
// if a user insert a student code which is already in the database an error message will occur
        {   $sms1='<font color="red"><b>Student Code already exist In The Database</b></font><br/>';
            include 'savestudent.php';
                        exit;
        }
    }
    }
?>

just help me with the join query for those two tables also, can you give me a hint on how can i write a php script which will add result to a particular student, let say a student id is 10, so how can i add his/ her marks to that student using his id, i mean those marks should come from an html form and processed by php script and then stored in a database, but a key point is using student id. please just a hint. sorry for distubarnce and inconvenience. it's just that i'm novice to php.

lps 71 Posting Pro in Training · Answer 3 · 2015-04-13T01:16:19+00:00

Wonder I get to your issue correctly, I will provide a sql query to retrieve data from these 2 tables in 1 query.
select student.student_code,student.firstname,student.middlename,student.lastname,student.gender,school.school_name from school,student where student.school_code=school.school_code
With this, I believe you can continue as I saw you managed to handle the querying using mysqli_query.