[SOLVED] Searching 2 tables getting multiple repeats

[Boo-urns]

First off, i'm not positive what the best way to JOIN the tables would be.  The first table has all the membership information name / location / that kind of stuff.  The second table contains their pet(s) if they have inputted any, and their unique user ID is passed to a petID (that also has a uniqueID for that) On my search I'm getting multiple repeats of the same member.  I'm not sure why i'm getting so many rows outputted as I've checked the loops here is my code:

 

<?php
// of course I'm getting all the variables and connecting to the db above this statement
$result = mysql_query("SELECT * FROM member, pet WHERE member.age LIKE '%$age%' AND member.state LIKE '%$state%' AND member.city LIKE '%$suburb%' AND member.postcode LIKE '%$postcode%' AND pet.petAge LIKE '%$petAge%' AND pet.petType LIKE '%$type%'");
?>

<?php
//in the html
if(mysql_num_rows($result) > 0) { 
while($u=mysql_fetch_array($result)){	
   $userID=$u['id'];
   $userAge=$u['age'];
   $state=$u['state'];
   $city=$u['city'];
   $zip=$u['postcode'];
 // Pet info
	$petAge = $u['petAge'];
	$petType = $u['petType'];
	$petBreed = $u['petBreed'];
	$petGender = $u['petGender'];
	//display the result (At the moment I'm just displaying this, I entered in city and state that only 1 member has and it repeated 
   echo "<td align='left'><a href='profile.php?id=$profileID'>$petName</a><br />Type: $type<br />Location: $city, $state</td></tr>";
}	
}
?>

 

It is repeating the echo 14 times! Agh! Thanks for your tips in advance to get this to display correctly

[Barand]

You have omitted the critical JOIN condition "WHERE member.id = pet.member_id" or whatever your column names are.

 

If you don't specify that then every member record is joined with every pet record (cartesian join)

 

A better syntax is

SELECT * 
FROM member m
    INNER JOIN pet p ON m.id = p.member_id
WHERE m.age LIKE '%$age%' 
    AND m.state LIKE '%$state%' 
    AND m.city LIKE '%$suburb%' 
    AND m.postcode LIKE '%$postcode%' 
    AND p.petAge LIKE '%$petAge%' 
    AND p.petType LIKE '%$type%'

 

so the join condition is separated from the selection criteria.

 

Query: " AND p.petAge LIKE '%$petAge%' "

 

So if they enter "1" do you want pets aged 1, 10, 11, 12, 13, 14 etc?

[Boo-urns]

Thanks for showing me that I forgot to put in the JOIN statement.  I do want the age to just show the age inputted not 11, 13 etc.. if i input 1 would it i need to do p.PetAge LIKE '$petAge%' ?

 

Thanks again 

 

[Barand]

use WHERE p.PetAge = '$petAge'

 

.. LIKE '%$petAge%' means any petAge containing $petAge

[Boo-urns]

ahh ok thanks a lot for the help!!