Join Syntax Help

transfield

Hello,
My code below gives me triplicate results for every query. I've attached a screen shot of my results. I want to prevent this but I don't know what I'm doing wrong. My table developer_log has got 3 records in it and my table developer has got thousands of records. When I do the JOIN, something gets messed up & results in the triplicate results.

Please tell me how to correct my mistake.

Thanks a lot.

<?php
$username="abc123";
$password="abc123";
$database="abc123";
$host="localhost";

mysql_connect ("$host","$username","$password");
mysql_select_db($database) or die( "Where's the database man?");

$mktime = date('Y-m-d');

$query1=("SELECT * FROM developer_log,developer WHERE developer.date_updated < developer_log.expiry");

$result1=mysql_query($query1);
$num=mysql_num_rows($result1);

while ($row1 = mysql_fetch_array($result1))
{
echo"<b>Year Approved: </b> ".$row1['year'].
"<p><b>Name of Developer: </b> ".$row1['name'].
"<p><b>Address: </b> ".$row1['development'].
"<p><b>Type of Property: </b> ".$row1['type'].
"<p><b>Levels: </b> ".$row1['levels'].
"<p><b>Number of Units: </b> ".$row1['quantity'].
"<p>"
;
}
?>

etully

I think your select statement has two problems. The first problem is easier to fix: Instead of "select *", you should be specifying which fields you want to read. For example:

select developer_log.year,developer_log.development,developer.name...

The second problem is that I think you're missing the main point of a join. Typically, there is going to be some connector in the "where" statement that connects fields in one table to fields in another. For example, imagine a customers table and an orders table. Maybe you want to see all the customers in New York... and all their orders... when the orders were less than $50. That select statement would look like this:

select orders.id,orders.total,customers.name,customers.phone
from customers,orders
where customers.state="NY" and
orders.total < 50 and
customers.id = orders.customer_id

Notice how the last line ties each customer to their own particular orders. This is the main point of a join. Without that, you are going to get screwy results.

This page has a good tutorial about how to do joins:
http://www.w3schools.com/sql/sql_join.asp

transfield

Thanks for your reply, etully. Actually what I want to do is to query developer based on the records found in developer_log. Therefore:-
developer_log.company should query developer.name
developer_log.project should query developer.development
developer_log.property should query developer.type

Screen shots of my tables are attached.

I posted this message on a few forums without explaining myself clearly & (naturally) I got vague answers. However all answers pointed towards a JOIN query which is why I'm experimenting with joins.

Would you be able to show me how to do this query? As far as displaying the correct results, I think I got it right in the while loop.

Thanks for your help.

etully

Write a sentence that explains precisely what the database should be looking for. Then write a select statement that you think accomplishes that and we'll take a look at it to see if you got it right and we'll make some suggestions. As an example, the sentence for my customer's orders query (above) would look like this:
"I want a series of rows where each row contains the customer's name, customer's phone, the order's id number and the order's total for every order where the customer lives in New York and the order was less than $50."

transfield

Thanks for your reply, etully.

...precisely what the database should be looking for...

It should be looking for keywords in developer_log which are also found in developer in accordance to :-
developer_log.company should query developer.name
developer_log.project should query developer.development
developer_log.property should query developer.type

...write a select statement that you think accomplishes that...

$query1=("SELECT * FROM developer,developer_log WHERE developer_log.company Like %developer.name% or developer_log.project Like %developer.development% or developer_log.property Like developer.type");