[RESOLVED] Joins

SoulAssassin

How do I join these tables in a select statement?
I have tried to select a complex, then on the next page "select from unit where com_is = $com_id".

Table Complex:
com_id (key)
com_name
com_address
com_contactname
com_number
com_turnkey
com_caretaker
sub_electrical
sub_plumber
sub_tv
sub_security
sub_painting
sub_roofing

Table Units:
com_id
unit_number (key)
unit_description
unit_rooms
unit_lastsold
unit_parkingbay
unit_garage

Piranha

SELECT com_name, unit_number // And all other columns you want
FROM Complex
INNER JOIN Units ON Complex.com_id = Units.com_id
WHERE... // and the where statements

SoulAssassin

Thanks for your quick response.
This will help me out.
appreciated.

SoulAssassin

Just a quick question.
Can you spot any errors here?
Specially on units.php page
I keep on getting "Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource" on my units.php page.

complex.php

<a href="units.php?view=<?php echo $row['com_id']?>"> 
        <?php  echo $row['com_name'];?>

units.php

session_start();

include "../login/config.php";

if ($view){
$result = mysql_query("SELECT com_name, unit_number FROM complex INNER JOIN unit ON complex.com_id = unit.com_id WHERE com_id = '$com_id'",$con);
$row = mysql_num_rows($result);

echo $row['unit_number'];
}

liquorvicar

Yes, you need to specify which table MySQL should look in for the com_id in the WHERE clause. Even though you are joining the tables so the com_id is the same ine ach table, MySQL will get confused because your reference to com_id is ambiguous.

Hint: use mysql_error to get full details of any MySQL errors.

SoulAssassin

I managed to fix that one, but this joining thing is causing me a headache.
I have a $edit , and $view , that holds the Unit and Complex numbers.

$tenant = mysql_query("SELECT com_name, unit_number, ten_id, ten_name FROM units INNER JOIN tenants ON units.unit_number = tenants.unit_number WHERE units.unit_number = '$complex' and tenants.unit_number = '$edit'",$con);

units table:
com_id
unit_number
unit_description
unit_rooms
unit_lastsold
unit_parkingbay
unit_garage

tenants table:
com_id
unit_number
ten_id
ten_initials
ten_name
ten_surname
ten_title
ten_idnumber
ten_homenr
ten_worknr
ten_cell
ten_postal
ten_residential
ten_fromdate
ten_todate

liquorvicar

Read my comments above. They all apply again. Use mysql_error. It usually gives you good feedback on what the problem is.

SoulAssassin

Thanks
I think I might have the Joining thing sorted out.
mysql_error helped.

Do you think it's better to do joins, or carry values in the URL like this:
/edit_tenant.php?tenant=1&unit=22&complex=1

It's just much easier for me, but is it right?

liquorvicar

Don't really understand what you're getting at. Joins and querystrings are usually different solutions to the same problem...

Piranha

Then someone comes and sends a URL like this:

/edit_tenant.php?tenant=2&unit=27&complex=5

And messes the database up totally. In other words, always make sure that the values you get is correct, and in this case it is probably best to do that by joining tables.