How to query mysql automatically to retrieve name

Question

I have three Mysql table as follows

1: registration

id |student_id | name |class | date | country | address

2: payment

id |student_id | name |class |school_fee | hostel_fee

3: exam table

id |student_id | name |class | Math | english

The first one table used to hold names of student who registered in school, Second table used to hold payment record of students and the last one (3) used to carry examination record.

Note: student_id is id number of student AND id is primary key auto_increment.

What I want and hard to me to query is! I want after registering a student and student want to make payment of any service, when I insert student_id example (001) the system automatically retrieve the name of student, or once I insert student name the system automatically retrieve student_id, this means that if student name is "Idan John Simon" and student_id is "001" once I insert in text field "Idan John Simon" automatically in text field of student_id of my form should display "001" as her idnumber and Vise versa.

Any help I w'l be thankfully.

Here the codes used to insert data in registration table

 <?php

$con=mysqli_connect("localhost","root","mcl");
if(!$con){
 die("The connection is not initiated,please try again");
 }
     else
       {
     mysqli_select_db("mcl",$con);

     $student_id=$_POST['student_id']; 
     $insert="INSERT INTO registration(student_id,name,class,date,country,address)       
     VALUES('".$_POST["student_id"]."','".$_POST["name"]."','".$_POST["class"]."',
    '".$_POST["date"]."','".$_POST["country"]."','".$_POST["address"]."')";

    }
       echo"Student already registered<br>";

        }
           mysqli_query($insert,$con);
           mysqli_close($con);
        ?>

This is for payment table

    <?php

$con=mysqli_connect("localhost","root","mcl");
if(!$con){
 die("The connection is not initiated,please try again");
 }
     else
       {
     mysqli_select_db("mcl",$con);

     $student_id=$_POST['student_id']; 
     $insert="INSERT INTO payment (student_id,name,class,school_fee,hostel_fee)       
     VALUES('".$_POST["student_id"]."','".$_POST["name"]."','".$_POST["class"]."',
    '".$_POST["school_fee"]."','".$_POST["hostel_fee"]."')";

    }
       echo"payment already sent<br>";

        }
           mysqli_query($insert,$con);
           mysqli_close($con);
        ?>

This is for exam table

    <?php

$con=mysqli_connect("localhost","root","mcl");
if(!$con){
 die("The connection is not initiated,please try again");
 }
     else
       {
     mysqli_select_db("mcl",$con);

     $student_id=$_POST['student_id']; 
     $insert="INSERT INTO exam (student_id,name,class,math,english)       
     VALUES('".$_POST["student_id"]."','".$_POST["name"]."','".$_POST["class"]."',
    '".$_POST["math"]."','".$_POST["english"]."')";

    }
       echo"data already sent<br>";

        }
           mysqli_query($insert,$con);
           mysqli_close($con);
        ?>

What are you using, PDO or MySQLi? [Here's the PDO's one](http://stackoverflow.com/questions/289916/alternative-to-pdolastinsertid-mysql-insert-id) — Francisco Presencia, Dec 03 '13 at 15:25
possible duplicate of [Select last insert id](http://stackoverflow.com/questions/2266604/select-last-insert-id) — Francisco Presencia, Dec 03 '13 at 15:26
I have to remind you about data validation. Nothing explains it better than Bobby-Tables: http://xkcd.com/327/. This simple POST variable will delete your registration table with all your data: student_id=%27%2C%27%27%2C%27%27%2C%27%27%2C%27%27%2C%27%27)%3BDROP%20TABLE%20registration%3B-- — Radu Maris, Dec 03 '13 at 16:04

score 1 · Answer 1 · answered Dec 03 '13 at 15:37

1

Depending on what extension you are using:

MySQL: http://uk3.php.net/manual/en/function.mysql-insert-id.php

MySQLi: http://uk3.php.net/manual/en/mysqli.insert-id.php

PDO: http://uk3.php.net/manual/en/pdo.lastinsertid.php

answered Dec 03 '13 at 15:37

transilvlad

13,974
13
45
80

How to query mysql automatically to retrieve name

1 Answers1