php mysql how to join two table

Question

I have these codes:

<?php

$records = mysql_connect('localhost', 'root', '') or die(mysql_error());

mysql_select_db('records', $records);
if(isset($_GET['src']))
{

$sql = "SELECT * FROM students where studentnumber like '%{$_GET['src']}%'";
$cnt = mysql_num_rows(mysql_query($sql));
if ($cnt == 0)
{
echo "<script>alert('No Record Found');</script>";
}

$result = mysql_query($sql, $records);
echo "<table border='0' class='table table-striped table-bordered table-hover'>";
echo "<tr class='info'><td width='10%'>Name</td><td width='11%'>Course Yr-Sec</td><td width='10%'>Student Number</td><td width='10%'>Violation</td><td width='10%'>Punishment</td><td width='9%'>Violation Date</td><td width='7%'>Punishment Date</td><td width='5%'>CS Length</td><td width='4%'>CS Done</td><td width='4%'>CS Left</td><td width='17%'><center>Action</center></td></tr></tr>";

while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>";
echo $row['Lastname'];
echo ", ";
echo $row['Firstname'];
echo " ";
echo $row['Middleinitial'];
echo "</td>";
echo "<td>";
echo $row['Course'];
echo " ";
echo $row['Year'];
echo "-";
echo $row['Section'];
echo "</td>";
echo "<td>";
echo $row['Studentnumber'];
echo "</td>";
echo "<td>";
echo $row['Violation'];
echo "</td>";
echo "<td>";
echo $row['Punishment'];
echo "</td>";
echo "<td>";
echo $row['Violationdate'];
echo "</td>";
echo "<td>";
echo $row['Punishmentstartdate'];
echo "</td>";
echo "<td>";
echo $row['CSlength'];
echo "</td>";
echo "<td>";
echo $row['CSDone'];
echo "</td>";
echo "<td>";
echo $row['CSLeft'];
echo "</td>";
echo "<td>";
echo "<a href='edit.php?no={$row['ID']}'><input type='button' name='edit' value='Edit' class='btn btn-success'></a>";
echo "   <a href='delete.php?no={$row['ID']}'><input type='button' name='delete' value='Delete' class='btn btn-danger'></a>";
echo "   <input type='button' name='view' value='View' class='btn btn-info'>";echo "</td>";
echo "</tr>";
}
echo "</table>";
}
else
{
$sql = 'SELECT * FROM students';
$result = mysql_query($sql, $records);
echo "<table border='0' class='table table-striped table-bordered table-hover'>";
echo "<tr class='info'><td width='10%'>Name</td><td width='11%'>Course Yr-Sec</td><td width='10%'>Student Number</td><td width='10%'>Violation</td><td width='10%'>Punishment</td><td width='9%'>Violation Date</td><td width='7%'>Punishment Date</td><td width='5%'>CS Length</td><td width='4%'>CS Done</td><td width='4%'>CS Left</td><td width='17%'><center>Action</center></td></tr></tr>";

while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>";
echo $row['Lastname'];
echo ", ";
echo $row['Firstname'];
echo " ";
echo $row['Middleinitial'];
echo "</td>";
echo "<td>";
echo $row['Course'];
echo " ";
echo $row['Year'];
echo "-";
echo $row['Section'];
echo "</td>";
echo "<td>";
echo $row['Studentnumber'];
echo "</td>";
echo "<td>";
echo $row['Violation'];
echo "</td>";
echo "<td>";
echo $row['Punishment'];
echo "</td>";
echo "<td>";
echo $row['Violationdate'];
echo "</td>";
echo "<td>";
echo $row['Punishmentstartdate'];
echo "</td>";
echo "<td>";
echo $row['CSlength'];
echo "</td>";
echo "<td>";
echo $row['CSDone'];
echo "</td>";
echo "<td>";
echo $row['CSLeft'];
echo "</td>";
echo "<td>";
echo "<a href='edit.php?no={$row['ID']}'><input type='button' name='edit' value='Edit' class='btn btn-success'></a>";
echo "   <a href='delete.php?no={$row['ID']}'><input type='button' name='delete' value='Delete' class='btn btn-danger'></a>";
echo "   <input type='button' name='view' value='View' class='btn btn-info'>";
echo "</td>";
echo "</tr>";
}
echo "</table>";
}
?>

It contains search, edit, delete and view functions...now my question is...I wanted to join the two tables in the database by the column studentnumber... my table students contains the column Lastname, Firstname, Middleinitial, Course, Year, Section, Studentnumber, Violation, Punishment, Violationdate, Punishmentstartdate, CSlength, ID, CSDone, CSLeft...now my another table named students2 contains the following rows ID, Studentnumber, Violation, Punishment, Violationdate, Punishmentstartdate, CSlength, CSDone, CSLeft...I want to display the information from my both tables...for example I want to view all the records from database with a studentnumber of 20101000...do I have to inner join the tables? I'm just a newbie in php... Thank you in advance... :)

please add both the table structures on ur question and expected columns to be fetched. — Abhik Chakraborty, Apr 01 '14 at 16:23
possible duplicate of [MySQL join with where clause](http://stackoverflow.com/questions/1219909/mysql-join-with-where-clause) — mamdouh alramadan, Apr 01 '14 at 16:23
Lastname(varchar), Firstname(varchar), Middleinitial(varchar), Course(varchar), Year(int), Section(text), Studentnumber(varchar), Violation(varchar), Punishment(varchar), Violationdate(date), Punishmentstartdate(date), CSlength(int), ID(int, primary key, auto increment), CSDone(int), CSLeft(int)... I want to fetch all columns in both tables...given the another table doesn't have all the information as the 1st table have it... @AbhikChakraborty... — Xthiahn29, Apr 01 '14 at 16:27

score 0 · Answer 1 · answered Apr 02 '14 at 14:47

This is a LEFT JOIN. It will return all of the records from students1, as well as any records from students2 where the record has the same studentnumber as a record in students1:

SELECT * FROM students1
LEFT JOIN students2
ON students1.studentnumber = students2.studentnumber
AND students1.studentnumber = 20101000

An INNER JOIN returns only records that produce a match, so you will only get records where there is an identical studentnumber in both students1 and students2. Based on your comments, I believe this is the style you are looking for:

SELECT * FROM students1
INNER JOIN students2
ON students1.studentnumber = students2.studentnumber
AND students1.studentnumber = 20101000

If you want to get your head around using JOINs, I'd recommend trying both of these statements to observe the results. Then try a few other approaches, perhaps using this excellent tutorial on the Coding Horror Blog.

php mysql how to join two table

1 Answers1