New to table joins...

Aesop

Hello all,

So I'm working through my first webapp and I have come to the point that I dread. Joining tables in mysql. I have no clue how to do this. I am currently using fabforce DBDesigner to have a visual aid in building blank tables and creating the relationships, however when I go into phpmyadmin it doesnt appear to be joined. The structure of how this needs to be done is like this;

(Parent Table)
properties
bldg_id is the primary

(Child Table)
f_lease
flease_id is the primary

(Child Table)
f_sale
fsale_id is the primary

Ideally, I need to link the child tables to a field called address in the parent table. The parent table serves as a repository of over 9,000 records, specifically buildings and properties. Now the f_lease table is populated with information of a retail space or office suite (for example) for lease on that property record from the parent table.

Should I set the field address from the parent table up as a foreign key in all the child tables? If so, what kind of php do I use in my query for update, delete, and insert? HELP!! lol

acey99

It's kinda like this:

you'll never "see" the join unless you make a new table that represents that join.

a simple join statement in mysql looks something like :

$sql=mysql_query("SELECT * FROM table1,table2 where table1.id='$variable' and table2.id='$variable'); # this allows you to print fields from both tables. - this is the "join"

$myObject=mysql_fetch_object($sql);

echo $myObject->fieldname;

leatherback

Although this is true.. It is also very inefficient use of resources, I have noticed (Slow-down of queries etc.)
That I solved by using the actual join command:

select STUFF from FIRST_TABLE, left join SECOND_table on FIRST_TABLE_ID_COLUMN = SECOND_TABLE_MATCHING_COLUM

But you can have a little read on this in the manual.

It really only is a small trick, where you tell the database-system to link each record to every matching record in an other table, according to some rule. The rules can vary:

inner join
Left join
Right join

and have to do with the way the matching is performed: All records need to exist in both tables, get all records from the left, and any from the right that match, else have no data, and vice versa. As I said: look in the manual

acey99

That's also assuming you have Mysql 4.0+

Mine assumes 3.0+

Aesop

sorry for the delayed response. First of all, thank you for responding. I really appreciate the help. This has been something I have dreaded doing just out of my own noob fear of the unknown lol

I tried out both examples and read up on the manual and still feel pretty lost....

Ideally I am trying to make it so when someone opens up the page containing a record for a property/building pulling data off of the bldgs table, there will also be a link to anything for lease, opens that page and the data there pulls from the for_lease table. The 2 fields that are shared between the tables will be bldg_id.

Let me show you some of the example php I've tried using to do this, and maybe you can point out what I did wrong

require 'globals/dbconnect.php';
$query = "select * from bldgs, left join forlease on bldg_id = bldg_id ";
//then call the query and display it
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
$numofrows = mysql_num_rows($result);
echo "<TABLE BORDER=\"0\">\n";
echo "<TR bgcolor=\"#E9F0F3\"><TD>Name</TD></TR>\n";
for($i = 0; $i < $numofrows; $i++) {
    $row = mysql_fetch_array($result); //get a row from our result set
    if($i % 2) { //this means if there is a remainder
        echo "<TR bgcolor=\"#F0F0F0\">\n";
    } else { //if there isn't a remainder we will do the else
        echo "<TR bgcolor=\"white\">\n";
    }
	echo '<TD>'.$row['bldgs.bldg_id'].'</TD>';
    echo "</TR>\n";
}
//now let's close the table and be done with it
echo "</TABLE>\n";

The error i get with this is;

Query failed: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'left join forlease on bldg_id = bldg_id' at line 1

Suggestions?

tunage

You need to leave out the comma and specify the table names on the join, this should work -

$query = "select * from bldgs left join forlease on bldgs.bldg_id = forlease.bldg_id ";

Aesop

thank you very much that worked like a charm. The finished code is

require 'globals/dbconnect.php';
$query = "select * from bldgs left join forlease on bldgs.bldg_id = forlease.bldg_id ";
//then call the query and display it
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
$numofrows = mysql_num_rows($result);
echo "<TABLE BORDER=\"0\">\n";
echo "<TR bgcolor=\"#E9F0F3\"><TD>Name</TD></TR>\n";
for($i = 0; $i < $numofrows; $i++) {
    $row = mysql_fetch_array($result); //get a row from our result set
    if($i % 2) { //this means if there is a remainder
        echo "<TR bgcolor=\"#F0F0F0\">\n";
    } else { //if there isn't a remainder we will do the else
        echo "<TR bgcolor=\"white\">\n";
    }
	echo '<TD>'.$row['address'].'</TD>';
    echo "</TR>\n";
}
//now let's close the table and be done with it
echo "</TABLE>\n";

So I guess my final question would be this;

As admin of the db, lets say I open the page for the building/property and I want to add a suite for lease to this property record, should I pass a variable containing the bldg_id over to the for lease input form? Then upon that form submission, the bldg_id that was passed is a hidden field that goes to bldg_id field in the forlease table? Does that make sense?😕 :queasy: