|
|
Posted by kenoli on 12/23/06 23:11
Yes. what you offer is pretty much what I have done. I am including
the entire lookup code below. You might notice that Ihave adapted some
of the code you suggested to me in several previous posts into the
script.
Thanks for your support and the support of others. Comments welcomed.
--Kenoli
The script:
There are three tables involved here:
tbl_person - contains information specifically related to the person
tbl_contact_info - contains address and phone information
tlk_person_interest - contains person_id values and interest_id values
associated each person with any number of interest areas
<?php
session_start();
require_once ('mysql_connect.php');
//------------------------ Get person_id from tlk_person_interest table
----------------------//
// This section gets all person_id values associated with interest
areas indicated on the lookup
// page by checking checkboxes. These are stored in the interest[]
array. The query is an OR
// query as we want ever person associated with any interest area.
if ($_POST[interests]) {
$query = "SELECT person_id FROM tlk_person_interest WHERE ";
$query .= "interest_id = '".implode("' OR interest_id = '",
array_values($_POST[interests]))."';";
$result = @mysql_query ($query);
if (!$result) {echo "You fucked up!<br /><br />";}
//else {echo "You succeeded!<br /><br />";}
while ($row = mysql_fetch_array($result)) {
$tid[] = $row[person_id];
}
$tid = array_unique($tid); //Get rid of duplicate array values
}
//---------------------------- Create sql query and execute lookup
--------------------------//
// This section loops through the rest of the $_POST variables and
assigns each that is present
// to one of two arrays $p[] or $c[]. $p[] will be used to query
tbl_person which holds data
// directly related to the person and $c[] will be used to query
tbl_contact_info which holds
// address, phone and other contact information.
foreach ($_POST as $key => $value) {
if(!empty($value)):
switch($key):
case 'first_name':
$p[] = "first_name = '{$value}'";
break;
case 'last_name' :
$p[] = "last_name = '{$value}'";
break;
case 'host' :
$p[] = "host = 'Y'";
break;
case 'graph_facil' :
$p[] = "graph_facil = 'Y'";
break;
case 'wcoc_member' :
$p[] = "wcoc_member = 'Y'";
break;
case 'city_1' :
$c[] = "city_1 = '$value'";
break;
case 'state_1' :
$c[] = "state_1 = '$value'";
break;
case 'country_1' :
$c[] = "country_1 = '$value'";
break;
endswitch;
endif;
}
//----------------------- Get person_id values from tbl_contact_info
------------------------//
// This section queries tbl_contact_info, getting the person_id values
associated with values
// enterred in the lookup table regarding location. AND is used here
as result must meet all
// entered values.
if ($c) {
$sql_contact = "SELECT person_id FROM tbl_contact_info WHERE " .
implode(" AND ", $c);
echo '<p>$sql_person = ' . $sql_contact . '</p>';
$result = @mysql_query ($sql_contact);
if (!$result) {echo "You fucked up!<br /><br />";}
//else {echo "You succeeded!<br /><br />";}
while ($row = mysql_fetch_array($result)) {
$cid[] = $row[person_id];
}
$cid = array_unique($cid); // Get rid of duplicate array values
}
//---------------------------------------- Combine arrays
-------------------------------------//
// This section places person_id values that are present from both
previous queries in $fid[].
if ($cid && $tid) {
$fid = array_intersect($cid, $tid);
} elseif ($tid) {
$fid = $tid;
} elseif ($cid) {
$fid = $cid;
}
//----------------------------- Assemble the main SQL lookup query
----------------------------//
// This section creates an appropriate query based on which fields in
the lookup table were
// selected. AND is used here as all values in the $p[] array must be
met.
if($p) {
$sql_person = "SELECT * FROM tbl_person WHERE " . implode(" AND
", $p);
if ($fid) {
$sql_person .= " AND (person_id = '" . implode("' OR person_id = '",
$fid) . "');";
}
} elseif ($fid) {
$sql_person = "SELECT * FROM tbl_person WHERE ";
$sql_person .= "person_id = '" . implode("' OR person_id = '",
$fid) . "';";
} else {
echo "You have not enterred any search terms. <a
href='../../Pages/admin_lookup_refine.php' >Click here</a> to return to
lookup page.";
break;
}
//--------------------- Set $_SESSION variable and send to "results"
page --------------------//
// This session stores the query in a session variable so it can be
accessed in order to display
// the lookup results on another page. The user will be given a table
with a summary of the people
// associated with her lookup entries and can then select one to have
additional information
// displayed. This could also be accomplished by saving the person_id
values in an array to
// the $_session variable with a little additional code to select those
values.
$_SESSION[lookup_query] = $sql_person;
header ("Location: ../../pages/admin_lookup_results.php");
?>
Koncept wrote:
> In article <1166374788.932930.220360@73g2000cwn.googlegroups.com>,
> kenoli <kenoli@igc.org> wrote:
>
> > Thanks, that function works perfectly.
> >
> > I wonder if anyone has a more elegant way of doing what I am trying to
> > do. My approach seems pretty brute force.
> >
> > --Kenoli
>
> I'm taking a shot in the dark here because I don't know the background,
> but given your query, wouldn't the only id's sent back to you be the
> ones with common id's in all AND conditions? For example, I can only
> see potential for *John* with ids of *4* or *10* ever being returned.
>
> SELECT * FROM tbl_person
> WHERE first_name = "John"
> AND (
> person_id = '2'
> OR person_id = '4'
> OR person_id = '6'
> OR person_id = '10'
> ) AND (
> person_id = '1'
> OR person_id = '4'
> OR person_id = '10'
> OR person_id = '20'
> ) AND (
> person_id = '4'
> OR person_id = '7'
> OR person_id = '6'
> OR person_id = '10'
> );
>
> If this is the case, why not simplify the query by filtering out the
> ID's which are NOT common to each "AND" condition first and then
> simplify your query to something like:
>
> SELECT * FROM `tbl_person` WHERE `first_name` = "John" AND `person_id`
> IN( 4, 10 );
>
> Am I way off on this one or does this make sense? Can you post the
> results of issuing your query in mySQL?
>
> --
> Koncept <<
> "The snake that cannot shed its skin perishes. So do the spirits who are
> prevented from changing their opinions; they cease to be a spirit." -Nietzsche
[Back to original message]
|