Hi I am trying to teach myself object and function on php, I am at a point where i can create my own defined functions but i have a little problem with mysql on how to check if something exist on the other table or invoke my function to work properly

let me explain it here I have a table1 Student_Outstanding(Server1) which only holds student number of people who have outstanding fees

and I have a second table2 which Student_Information(Server 2) which holds all the student information registered in that institution,so since this table holds a lot of information like thousands of records

How do i fetch the student information(table 2) of only student numbers which exist in table 1
1st function fetches student number

function fetchStudentNumber($num)
	{

require(CONNECTIONS.'conn.php');

$sql = "SELECT * FROM Students WHERE Student_Number='".$num."' ";
$result=mysql_query($sql);

$lines = array();	

while($rows=mysql_fetch_array($result)){

	$lines[] = $rows;
}
return $lines;
}

second function fetches student information(outstanding) and add it to a new table before it can email it but my problem is here how do i check if the student in table one exist in table2 and only select that information help with mysql

function fetchStudentInfo(){

require (CONNECTIONS.'connection.php');

$resource1 = "SELECT Student_Name, Reg_Date, StudentNumber , Course, Tel, DOB_Date FROM Student_Out";

	$result1=mysql_query($resource1);

	while ($rows = mysql_fetch_array($result1)) {


	$stuname = $rows['Student_Name'];
	$regdate = $rows[' Reg_Date'];
	$stunum = $rows['StudentNumber'];
	$cos= $rows['Course'];
	$tel= $rows['Tel'];
	$dob= $rows['DOB_Date'];

	require (CONNECTIONS.'db_conn.php');

	$way = true;
		if ($stunum != "") {
			if ($way) {
	$resource2 = "INSERT IGNORE INTO Student_outstandingfees
	(Student_Name, Reg_Date, StudentNumber , Course, Tel, DOB_Date) VALUES 
	('".$stunum."', '".$regdate."', '".$stunum."', '".$cos."', '".$tel."', '".$dob."')";

	$way = false;
			}
			else {
	$resource2 = $resource2.",
	('".$stunum."', '".$regdate."', '".$stunum."', '".$cos."', '".$tel."', '".$dob."')";
			}
		}

	$result2=mysql_query($resource2) or die("Cannot insert into student outstanding");

	}	

}

    I think that you need to read up on tutorials on database normalisation and then look into SQL statements including JOINS.

    Normalisation will show the basic table structures that you would require to hold your data and details the links between each table with which you maintain referential integrity.

    What this will achieve is showing you that you can join the two tables together based on common fields, and then apply a where clause limiting the output to only those records that your require.

    Hope this helps...

      Thank you ,i will read through normalization, my joins work perfectly i have a huge script that is fetching all this student information and has inner/left/right joins but my problem is to check through the result fetched(table 1) and see if the student number in table 2 exist that is my problem, I only want to fetch student info of student numbers which exist only on the other table because the student info has many records i only nwat to select from table only columns taht exist in

        11 days later

        Hi everyone.

        I am stuck on this for quite a while. Lets say i have 2 tables.

        Table 1 with these columns:

        Student_Name,
        Reg_Date,
        StudentNumber ,
        Course,
        Tel
        DOB_Date

        Table 2 with this column only:
        StudentNumber

        I need to create a VIEW that will show me all the the data in Table 1 but only for the column that exists in table 2. Example:

        Something like this:

        select (all the columns from table 2)

        from table 1

        Thanks.

          Write a Reply...