connect two tables on php

[howie]

I got two tables on a database but I dont know how to run independently. All I want is each table to run independently once tracked.
the first table is doing ok, but i dont know how to put the second table in the coding.

<?php
mysql_connect ("localhost", "root","") or die (mysql_error());
mysql_select_db ("maklumatpesakit");

$term = $_POST['term'];

$sql = mysql_query("select * from main where NoID like '%$term%'");

while ($row = mysql_fetch_array($sql)){
echo 'ID: '.$row['NoID'];
echo '<br/> Name: '.$row['Name'];
echo '<br/> Age: '.$row['Age'];
echo '<br/> Date Of Birth: '.$row['DateOfBirth'];
echo '<br/> Sex: '.$row['Sex'];
echo '<br/> Race: '.$row['Race'];
echo '<br/> Status: '.$row['Status'];
echo '<br/> Address: '.$row['Address'];
echo '<br/> Contact No: '.$row['NoTel'];
echo '<br/> Religion: '.$row['Religion'];
echo '<br/> Mode of Transport: '.$row['MobPatient'];
echo '<br/> Patient Mobility: '.$row['PatientMob'];
echo '<br/> Reference: '.$row['Reference'];
echo '<br/><br/>';
}
?>


my second table is 'reference', where the echo show the data from the table reference in my database. i just want to call from the table to show the data in echo but i cant figure how to do this.
pls help.

[johny10151981]

before echoing retrieve all data and then echo.

If you do any process before your data wont lost or any other trouble wont be generated

[howie]

im still new to this thing.. can u give code example for retrieving data?

[johny10151981]

You already did that

[Abhishek Saha]

Can you nplease provide 'reference' table structure?
What all columns of 'reference' table you want to show?

You need to join the two tables.

select * from main,reference where NoID like '%$term%'

this SQL statement show all data from the two tables.

Is 'Reference' is a column of 'reference' table??

<?php
mysql_connect ("localhost", "root","") or die (mysql_error());
mysql_select_db ("maklumatpesakit");
 
$term = $_POST['term'];
 
$sql = mysql_query("select * from main,reference where NoID like '%$term%'");
 
while ($row = mysql_fetch_array($sql)){
echo 'ID: '.$row['NoID'];
echo '<br/> Name: '.$row['Name'];
echo '<br/> Age: '.$row['Age'];
echo '<br/> Date Of Birth: '.$row['DateOfBirth'];
echo '<br/> Sex: '.$row['Sex'];
echo '<br/> Race: '.$row['Race'];
echo '<br/> Status: '.$row['Status'];
echo '<br/> Address: '.$row['Address'];
echo '<br/> Contact No: '.$row['NoTel'];
echo '<br/> Religion: '.$row['Religion'];
echo '<br/> Mode of Transport: '.$row['MobPatient'];
echo '<br/> Patient Mobility: '.$row['PatientMob'];
echo '<br/> Reference: '.$row['Reference'];
echo '<br/><br/>';
}
?>
 

[howie]

i have try the:
select * from main,reference where NoID like '%$term%'
and it show an error:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\MaklumatPesakit\search.php on line 9

ok, i have two tables 'main' and 'reference', and in 'main' i have a reference field where i hope to connect to 'reference' table to show the data. is it possible?

or i just have to delete the reference field and connect the NoID from 'main' and 'reference' table. but how can i done this?

[Abhishek Saha]

If 'NoID' this column exists in both tables then try

$sql = mysql_query("select * from main,reference where main.NoID=reference.NoID and main.NoID like '%$term%'"); 

[nathj]

It seems to me that you are after the syntax for a JOIN on the tables. Have a look at this page in the MySQL guide

So If you have 2 tables - main and reference and main has an ID field and reference has a mainID field which is a foreign key then you can do something like:

SELECT a.*, b.* from main a left join reference b on b.mainID = a.id where a.NoID like '%$term%'
 

So there you have it, hopefully the guide and the snippet will be of some help to you.

Cheers
nathj

[howie]

thank!! now its doing good.. thanks for the info all.. ^^