[RESOLVED] mysql_fetch_array with two tables

videoman

Hello, I am trying to select data from two tables and using mysql_fetch_array to display them. I have two tables called person and membership. person has 9 fields like personID (primary key), first, last, address etc. and the 2nd table has 5 fields like personID (foreign key), membershipID (primary key), commenced, renewed, type.

What I want to do is search through all the fields on both tables and then print them out in a table. My code worked fine when I was only searching one table yet when I try and search through them both I get many repeated results. I managed to find a similar thread but I still could not get it to work with my script. http://www.phpbuilder.com/board/showthread.php?t=618734

$query = "select * from person,membership WHERE person.personID LIKE '$srch' || person.first LIKE '$srch' || person.last  LIKE '$srch' || person.address LIKE '$srch' || person.town LIKE '$srch' || person.state LIKE '$srch' || person.post LIKE '$srch' || person.email LIKE '$srch' || person.phone LIKE '$srch'|| membership.personID LIKE '$srch' || membership.membershipID LIKE '$srch' || membership.commenced LIKE '$srch' || membership.renewed LIKE '$srch' || membership.type LIKE '$srch'";

$result=mysql_query($query);

if  ($result) 
{ 
echo "Here are the results:<br><br>"; 
echo "<table width=90% align=center border=1><tr> 
  <td align=center bgcolor=#00FFFF>PersonID</td> 
    <td align=center bgcolor=#00FFFF>First</td> 
    <td align=center bgcolor=#00FFFF>Last</td> 
    <td align=center bgcolor=#00FFFF>Address</td> 
    <td align=center bgcolor=#00FFFF>Town</td> 
    <td align=center bgcolor=#00FFFF>State</td> 
    <td align=center bgcolor=#00FFFF>Post</td> 
    <td align=center bgcolor=#00FFFF>Email</td> 
    <td align=center bgcolor=#00FFFF>Phone</td>
	<td align=center bgcolor=#00FFFF>Membership ID</td>
	<td align=center bgcolor=#00FFFF>Commenced</td>
	<td align=center bgcolor=#00FFFF>Renewed</td>
	<td align=center bgcolor=#00FFFF>type</td> 
    </tr>"; 

    while ($r = mysql_fetch_array($result)) {
        $personID = $r["personID"];
		$first = $r["first"]; 
        $last = $r["last"]; 
        $address = $r["address"]; 
        $town = $r["town"]; 
        $state = $r["state"]; 
        $post = $r["post"]; 
        $email = $r["email"]; 
        $phone = $r["phone"]; 
        $membershipID = $r["membershipID"]; 
        $commenced = $r["commenced"]; 
        $renewed = $r["renewed"]; 
        $type = $r["type"]; 

              echo "<tr> 
                    <td>$personID</td> 
                    <td>$first</td> 
                    <td>$last</td> 
                    <td>$address</td> 
                    <td>$town</td> 
                    <td>$state</td> 
                    <td>$post</td> 
                    <td>$email</td> 
                    <td>$phone</td>
                    <td>$membershipID</td>
                    <td>$commenced</td>
                    <td>$renewed</td>
                    <td>$type</td>
           			 </tr>"; 
    }             
    echo "</table>"; 
 } else { echo "problems...."; }

} else {
echo "Search string is empty. <br> Go back and type a string to search";
}

My two tables are as follows-
CREATE TABLE person (
personID int(6) NOT NULL auto_increment,
first varchar(15) NOT NULL,
last varchar(15) NOT NULL,
address varchar(30) NOT NULL,
town varchar(15) NOT NULL,
state varchar(3) NOT NULL,
post int(4) NOT NULL,
email varchar(30) NOT NULL,
phone varchar(15) NOT NULL,
PRIMARY KEY (personID),
UNIQUE KEY id (personID),
KEY id_2 (personID)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE membership (
personID int(6) NOT NULL,
membershipID int(6) NOT NULL auto_increment,
commenced date NOT NULL,
renewed date default NULL,
type varchar(16) NOT NULL,
PRIMARY KEY (membershipID),
KEY personID (personID),
CONSTRAINT membership_ibfk_1 FOREIGN KEY (personID) REFERENCES person (personID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

sneakyimp

looks to me like your query is the problem. that is the biggest, fattest OR clause I think I have ever seen.

$query = "select *
from person,membership
WHERE person.personID LIKE '$srch'
 || person.first LIKE '$srch'
 || person.last LIKE '$srch'
 || person.address LIKE '$srch'
 || person.town LIKE '$srch'
 || person.state LIKE '$srch'
 || person.post LIKE '$srch'
 || person.email LIKE '$srch'
 || person.phone LIKE '$srch'
 || membership.personID LIKE '$srch'
 || membership.membershipID LIKE '$srch'
 || membership.commenced LIKE '$srch'
 || membership.renewed LIKE '$srch'
 || membership.type LIKE '$srch'";

You might want to JOIN the two tables first before the OR clause kicks in. this might reduce some redundancy...like that due to the personID for instance. NOTE: I have removed the requirement that m.personID be searched because we are already checking for p.personID

$query = "select *
from person p
INNER JOIN membership m ON p.personID=m.personID
WHERE p.personID LIKE '$srch'
 || p.first LIKE '$srch'
 || p.last LIKE '$srch'
 || p.address LIKE '$srch'
 || p.town LIKE '$srch'
 || p.state LIKE '$srch'
 || p.post LIKE '$srch'
 || p.email LIKE '$srch'
 || p.phone LIKE '$srch'
 || m.membershipID LIKE '$srch'
 || m.commenced LIKE '$srch'
 || m.renewed LIKE '$srch'
 || m.type LIKE '$srch'";

another possibility would be to use two queries...one to fetch the relevant personIDs using DISTINCT and another to fetch the details about each person. I don't know if the results for this would be any different than the preceding results but it might be worth a try.

query1:

$query = "SELECT DISTINCT p.personID as pid, m.membershipID
FROM person p
INNER JOIN membership m ON p.personID=m.personID
WHERE p.personID LIKE '$srch'
|| p.first LIKE '$srch'
|| p.last LIKE '$srch'
|| p.address LIKE '$srch'
|| p.town LIKE '$srch'
|| p.state LIKE '$srch'
|| p.post LIKE '$srch'
|| p.email LIKE '$srch'
|| p.phone LIKE '$srch'
|| m.membershipID LIKE '$srch'
|| m.commenced LIKE '$srch'
|| m.renewed LIKE '$srch'
|| m.type LIKE '$srch'";

then you'd collect all the person ids into an array

$result = mysql_query($query)
  or die ('darn.  query failed.');
$ids = array();
while ($r = mysql_fetch_assoc($result)) {
  $ids[] = $r['pid'];
}

then you run a 2nd query to get the display info for each matching person:

$query = "SELECT * FROM person p INNER JOIN membership m on p.personID=m.personID WHERE p.personID IN (" . implode(',', $ids) . ")";

NogDog

You almost assuredly want to do the join that sneakyimp suggested. Otherwise you'll get a row for every combination of rows between the two tables which will still match the where clause requirements.

sneakyimp

nog's right...on further reflection, the fact that you don't require the P and M tables to have any common parameter means that you could get an enormous result....i think it would be an 'outer join' or something.

For what it's worth....this will in all likelihood be a really slow query. If you start getting any significant numbers of members, it could get really slow.

Piranha

Why do you use LIKE and not = ? I think that it will be a little bit faster with =, but I'm not sure about it.

Do you need all the search options all the time? It could be a good idea to have just the ones you need in the query, the rest could easily be handled by using if-statements. It would speed up the query, and when you have lots of rows you will notice the difference.

videoman

Thanks alot sneakyimp, that query worked perfectly. Thanks to everyone else who helped as well 🙂