Join with * from one table and a few volumns from anothe
Posted: Fri Nov 22, 2002 11:01 am
This should be simple but I cant seem to work out the query. I am using php and MySQL.
I am trying to get all the information from one table and some from another where the ID in one table matches the ID in the other, at the moment I use two while statements but would like to combine them into one so I can introduce or make it easier to introduce a NEXT and PREVIOUS links on the entries returned, I'm new to this so there might be an easier way although up to now what I am using works although I feel I need to simplify it for the links.
The first query gets the information from the property table:
$sql="SELECT * FROM forrent WHERE $country $price $bed $type $r_order";
after reading a rows contents and setting the row entries to variables "including $sc_ID taken from the first query" I set the following query
$sql = "SELECT email, phone, fax, website FROM clients WHERE client_ID='$sc_ID'";
I have looked at joins but can't seem to quite find a way of creating a join for my needs, maybe someone could point me in the right directionor tellme if the type of join I need is at all possible?
The code I am using with two while statements is as follows:
I am trying to get all the information from one table and some from another where the ID in one table matches the ID in the other, at the moment I use two while statements but would like to combine them into one so I can introduce or make it easier to introduce a NEXT and PREVIOUS links on the entries returned, I'm new to this so there might be an easier way although up to now what I am using works although I feel I need to simplify it for the links.
The first query gets the information from the property table:
$sql="SELECT * FROM forrent WHERE $country $price $bed $type $r_order";
after reading a rows contents and setting the row entries to variables "including $sc_ID taken from the first query" I set the following query
$sql = "SELECT email, phone, fax, website FROM clients WHERE client_ID='$sc_ID'";
I have looked at joins but can't seem to quite find a way of creating a join for my needs, maybe someone could point me in the right directionor tellme if the type of join I need is at all possible?
The code I am using with two while statements is as follows:
Code: Select all
<?php
$country="country='France'"
$price = "AND rent_max BETWEEN '250' AND '500'";
$bed = "AND rent_beds BETWEEN '2' AND '5'";
$type = " AND rent_com = '$r_com_type'";
$r_order = "ORDER BY '$r_order'";
$sql="SELECT * FROM forrent WHERE $country $price $bed $type $r_order";
$mysql_rental_result=mysql_query($sql,$connection);
$rent_row=@mysql_fetch_row($mysql_rental_result);
while ($r_row = mysql_fetch_array($mysql_rental_result)) {
$sr_ID = $r_rowї"ID"];
$sc_prop = $r_rowї"client_ID"]; // pass it to prop to hide the ID
$sr_name = $r_rowї"rent_name"];
// LOAD THE REST OF THE VERIABLES
// NOW GET THE USER INFORMATION FOR THE PROPERTY
$sql = "SELECT email, phone, fax, website FROM clients WHERE client_ID='$sc_prop'";
$mysql_client_result=mysql_query($sql,$connection);
$c_num_rows=@mysql_num_rows($mysql_rental_result);
if ( $c_num_rows > 0 ) {
while ($c_row=mysql_fetch_array($mysql_client_result)) {
$sc_phone = $c_rowї"phone"];
$sc_fax = $c_rowї"fax"];
$sc_email = $c_rowї"email"];
$sc_website = $c_rowї"website"];
}
} else {
// ----------- SEND ME AN ERROR E-MAIL
}
include "search_rental_table.php"; // LOAD THE RESULTS TABLE
}
}
}
?>