Join with * from one table and a few volumns from anothe

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Join with * from one table and a few volumns from anothe

Post by mikebr »

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:

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 
}
}
}
?>
Rob the R
Forum Contributor
Posts: 128
Joined: Wed Nov 06, 2002 2:25 pm
Location: Houston

Post by Rob the R »

All you have to do is preface the "*" with the table or table alias, and use a LEFT JOIN to bring in the CLIENTS table.

Code: Select all

<?php

// be sure that $r_com_type and $r_order have the
// "fr." in front of each table column, just in case
// there are shared column names between the tables

$country="fr.country='France'" 
$price = "AND fr.rent_max BETWEEN '250' AND '500'"; 
$bed = "AND fr.rent_beds BETWEEN '2' AND '5'"; 
$type = " AND fr.rent_com = '$r_com_type'"; 
$r_order = "ORDER BY '$r_order'"; 

$sql="SELECT fr.*, c.email, c.phone, c.fax, c.website " .
   "FROM forrent fr " .
   "LEFT JOIN clients c ON fr.client_ID = c.client_ID " .
   "WHERE $country $price $bed $type $r_order "; 

?>
The code you have to detect when the error email should be sent to you if there are no records from the CLIENTS table will have to be changed too, since in the above query you could get records returned without the CLIENTS table contributing any matches. I expect that a check that if all of the email, phone, fax, and website array elements are null, then your error email can be sent.
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Post by mikebr »

This works great and it has the added advantage of helping my understanding of using an alias in joins also.

Thanks
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Post by mikebr »

As I said this works great but how do I set count(*) with it?, I have tried the following along with a few other inventions without success:

Code: Select all

<?php$sql="SELECT fr.count(*), c.email, c.phone, c.fax, c.website " . 
   "FROM forrent fr " . 
   "LEFT JOIN clients c ON fr.client_ID = c.client_ID " . 
   "WHERE $country $price $bed $type $r_order LIMIT $from, $to";  

$sql="SELECT count(fr.*), c.email, c.phone, c.fax, c.website " . 
   "FROM forrent fr " . 
   "LEFT JOIN clients c ON fr.client_ID = c.client_ID " . 
   "WHERE $country $price $bed $type $r_order LIMIT $from, $to"; 
?>
Thanks
Rob the R
Forum Contributor
Posts: 128
Joined: Wed Nov 06, 2002 2:25 pm
Location: Houston

Post by Rob the R »

You can't mix "count(*)" in a query with other columns unless you're using a GROUP BY clause. I think you can use your earlier call to MYSQL_NUM_ROWS to get the count of results. Just realize that this will return 1 even if there are no rows from CLIENTS, because the query will return a row from FORRENT. So this count will only be correct if you've established there are some data from CLIENTS in the query.
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Post by mikebr »

Yes I was returning the number of rows from the earlier query, so if I run both queries, one to get the join and another to get the number of rows can that be acceptable programming behavior, what would be the norm here? The query for the number of rows would only need to be run once but then does this not make the initial search very slow "it already takes about 2 seconds to return the 15 entries in the table on localhost" or is it better to go back and use the two while loops? which if I am right would mean I would need to run the wuery twice anyway, once to get the number of rows and another time to get the "x" results as I am using LIMIT to set the returns to page links

Thanks
Rob the R
Forum Contributor
Posts: 128
Joined: Wed Nov 06, 2002 2:25 pm
Location: Houston

Post by Rob the R »

Ah. If you're using LIMIT to return partial queries, then I don't see any other alternative to running two queries.

As far as the technique you use to get both the count and the groups of page links, I would run the count query once at the beginning and then the LIMIT query to get the various page results. But honestly, whatever is faster and gives you the results you want is what you should do.
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Post by mikebr »

That is what I was going do but then I thought what would happen if a user added or worse removed an entry the count would have changed from the initial number of rows, anyway I will try a couple of different ways and see what comes up fastest for getting the correct results.

Thanks for all your help on this.
Post Reply