Joining Datas from two tables

[I-AM-OBODO]

Hello,

I have two tables i'd like to pull specific data from. the relationship btw the two table is the email address. how do i do a join.

 

thanks

[cags]

SELECT t1.*, t2.* FROM table1 t1 JOIN table2 t2 ON t1.email=t2.email

[I-AM-OBODO]

Thanks for your response, but i seem not able to figure it out.

my code is this

$query = "SELECT friend, relation, community FROM table1 AND street, people FROM table2 WHERE username = '$username AND Email = '$email'";
$result = mysql_query($query);

if(mysql_num_rows($result) < 1){
echo ("<font size=3 color=red><b>No Match Found. <input type='button' value='Try Again' onClick='history.go(-1)'>");
}else{

$num_rows = mysql_num_rows($result);
print "There are $num_rows records.<P>";
echo "<table width= '100%' border='1' bordercolor='#000000' cellpadding='1' cellspacing='1'>
<tr >
<th>Friend</th>

<th>Relation</th>

<th>Community</th>

<th>Street</th>

<th>People</th>
</tr>";
while ($get_info = mysql_fetch_row($result)){ 
print "<tr >\n";
foreach ($get_info as $field) 
print "\t<td ><font face=arial size=2/>$field</font></td>\n";
print "</tr>\n";
}
print "</table>\n";
print "<br>";

}

What i want is the query from table1 and table2 so that they can output as one

 

Thanks

 

[Alex]

Like cags said:

$query = "SELECT t1.friend, t1.relation, t1.community, t2.street, t2.people FROM table1 t1 JOIN table2 t2 ON t1.email=t2.email WHERE username = '$username'";

[awjudd]

Your syntax for joining the two is completely wrong.  It needs to be done as Cags suggested.

 

SELECT `t1`.`friend`, `t1`.`relation`, `t1`.`community`, `t2`.`street`, `t2`.`people` FROM `table1` `t1` JOIN `table2` `t2` ON `t1`.`email` = `t2`.`email`  WHERE `t1`.`username` = '$username' AND `t1`.`email` = '$email'

 

(As AlexWD said, except with the second condition which they forgot to mention that was in your previous post).

 

Something like this would do the trick.

 

~juddster

[I-AM-OBODO]

I don't know why am not getting it. this is the error i am getting:

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\wamp\www\Nexpress\admin\nxt_pcl_report4.php on line 63

 

and this is my code:

 

$parcel_desc = $_Post['parceldesc'];

if($parcel_desc ==''){
echo "<font size=3 color=red><b>Search field empty. <input type='button' value='Try Again' onClick='history.go(-1)'><br>";
}else{


$query = "SELECT t1.ParcelDesc, t1.DeliveryAddress, t1.New_Address, t2.status, t2.remarks FROM parcels t1 JOIN parcel_status t2 ON t1.Email=t2.username WHERE t1.ParcelDesc = $parcel_desc AND Email = '$username'  ORDER BY date_of_order DESC";
$result = (mysql_query($query));


if(mysql_num_rows($result) < 1){
echo ("<font size=3 color=red><b>No Match Found. <input type='button' value='Try Again' onClick='history.go(-1)'>");
}else{

$num_rows = mysql_num_rows($result);
print "There are $num_rows records.<P>";
echo "<table width= '100%' border='1' bordercolor='#000000' cellpadding='1' cellspacing='1'>
<tr >
<th>Parcel-Desc</th>

<th>Recipient Name & Address</th>

<th>New Address</th>

<th>Remarks</th>

<th>Status</th>
</tr>";
while ($get_info = mysql_fetch_row($result)){ 
print "<tr >\n";
foreach ($get_info as $field) 
print "\t<td ><font face=arial size=2/>$field</font></td>\n";
print "</tr>\n";
}
print "</table>\n";
print "<br>";
//mysql_close($link);
}


}

}

?>

PS: PS: table names are parcels and parcel_status respectively.
[b]parcels table    parcel_status table[/b]
ParcelDesc     =       parcel_desc
Email              =       username

Hope this is helpful
Thanks you all

[fenway]

Then dump $resource and see what it really is... those extra parens don't help.

[awjudd]

Try putting an echo of the actual query before it is being run.

 

For example,

$query = ...;

 

echo $query;

 

...

 

Then copy this query into phpMyAdmin and run it.  If there is a problem with the SQL query, it will tell you.

 

If you can provide us with that error message it will help us move onwards ... what also may be helpful is the actual structure of your tables ... but that can come afterwards.

 

~juddster

[I-AM-OBODO]

Thanks all so far.

 

thanks fenway, i forgot to remove  those perans when posting my code cos they are part of some functions i did not include on the code i showed you cos i feel its not part of the problem and if you observe well, i posted the error i had.

 

Now after doing some changes, i got the replicated queries, i mean instead of giving me one result, it duplicates the results into 3.

this is my new query.

 

$query = "SELECT t1.ParcelDesc, t1.DeliveryAddress, t1.New_Address, t2.status, t2.remarks FROM parcels t1 JOIN parcel_status t2 ON t1.Email=t2.username WHERE t1.ParcelDesc = '$parcel_desc' AND t1.Email = '$username'";

[Kryptix]

It's difficult to say without seeing what data is in the tables to be honest.

[I-AM-OBODO]

Hi.

Below is the dump for my table, but the fields i want to join is for parcels( ParcelDesc, DeliveryAddress, New_Address)

for parcel_status(status, remarks)

 

note:

parcels table            parcel_status table

txnref                              transref

Email                                username

ParcelDesc                      parcel_desc

 

and my search criteria is parce_desc

 

SQL DUMP

CREATE TABLE IF NOT EXISTS `parcels` (

  `Firstname` varchar(50) NOT NULL,

  `Surname` varchar(50) NOT NULL,

  `OfficialAddress` varchar(100) NOT NULL,

  `BillingAddress` varchar(100) NOT NULL,

  `Email` varchar(50) NOT NULL,

  `AltEmail` varchar(50) NOT NULL,

  `Phone` varchar(50) NOT NULL,

  `txnref` varchar(50) NOT NULL,

  `amount` int(50) NOT NULL,

  `ParcelDesc` varchar(50) NOT NULL,

  `ItemUnit` int(100) NOT NULL,

  `ApproWei` int(10) NOT NULL,

  `Destination` varchar(100) NOT NULL,

  `DeliveryAddress` varchar(100) NOT NULL,

  `date_of_order` date NOT NULL,

  `New_Address` varchar(45) NOT NULL,

  UNIQUE KEY `txnref` (`txnref`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

 

CREATE TABLE IF NOT EXISTS `parcel_status` (

  `SN` int(10) NOT NULL AUTO_INCREMENT,

  `transref` int(30) NOT NULL,

  `username` varchar(50) NOT NULL,

  `parcel_desc` varchar(50) NOT NULL,

  `item_unit` int(5) NOT NULL,

  `time` time NOT NULL,

  `destination_add` varchar(50) NOT NULL,

  `date` date NOT NULL,

  `status` varchar(20) NOT NULL,

  `remarks` varchar(100) NOT NULL,

  `date_order` date NOT NULL,

  `processed` varchar(30) NOT NULL,

  PRIMARY KEY (`SN`),

  UNIQUE KEY `transref` (`transref`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=31 ;

[I-AM-OBODO]

Hi,

i've gotten somewhere so far but the problem now is the result i get is replicated. tried to find out why but to no avail. pls somebody help my eyes are bleeding now.

thanks

my code below

$query = "SELECT t1.ParcelDesc, t1.DeliveryAddress, t1.New_Address, t2.status, t2.remarks FROM parcels t1 JOIN parcel_status t2 ON t1.Email=t2.username WHERE t1.ParcelDesc = '$parcel_desc' AND t1.Email = '$username' ";
$result = (mysql_query($query));


if(mysql_num_rows($result) < 1){
echo ("<font size=3 color=red><b>No Match Found. <input type='button' value='Try Again' onClick='history.go(-1)'>");
}else{

$num_rows = mysql_num_rows($result);
//print "There are $num_rows records.<P>";
echo "<table width= '100%' border='1' bordercolor='#000000' cellpadding='1' cellspacing='1'>
<tr >
<th>Parcel-Desc</th>

<th>Recipient Name & Address</th>

<th>New Address</th>

<th>Remarks</th>

<th>Status</th>
</tr>";
while ($get_info = mysql_fetch_row($result)){ 
print "<tr >\n";
foreach ($get_info as $field) 
print "\t<td ><font face=arial size=2/>$field</font></td>\n";
print "</tr>\n";
}
print "</table>\n";
print "<br>";