PHP Code for HTML Form Creator—Search and Match: Search for Compatible Groups

This script is called match_.php

The PHP Code for HTML Form Creator—Search and Match: Search for Compatible Groups CMS script is one of a group of PHP scripts that handle both the administrative and end-user aspects of a general purpose HTML Form Creator that allows not just input boxes but multiple selection enabled select/option lists as well. In addition to the expectable editing scripts for both administrative and end-user functions, there's also a Search and Match script—the one on this page—so that users can use the scripts to find other users with various individual or group commonalities, including proximity searches, i.e., find all the users within various distances. There are even private messaging scripts in this Content Management System (CMS). And the script on this page, match_.php, includes a word search as well as a proximity search based on zip codes and a criteria matching search.

Administrative HTML Form Creator Scripts

The first code block has been discussed in the Security page which we call HTML Form Creator—Search and Match — Security. If you need to see the whole matching script in one script block, use this: HTML Form Creator—Search and Match.

The script below is called match_.php

<?php
//copyright (c) 2011 by MCS Investments, Inc. all rights reserved

include_once"checkid_.php";
$U=$_SESSION['username'];
define('_NODIRECTACCESS', TRUE);
include_once"includes/config.php";
if (!isset($_SESSION['groupname']) || !isset($_SESSION['userid']) || !isset($_SESSION['username']) || $_SESSION
['username']<>$U || !isset($U) || $U=="" || strlen($U)<6 || !isset($_SESSION['sessionid'])){echo '<script language="javascript">alert("Please login."); window.location = "login_.php";</script>';}
?>

The function get_record_wordsearch() is to get a single search result record and display it on the screen with both a link to a messaging page to send a message to this group and a display of the total number of the search word that were found in this record. Each search word will get highlighted in light blue. This function is run from a while loop that displays all the records that meet the criteria of containing the search word at least once.

Actually, this function only displays the first part of the record that comes from the my_members table. A for loop near the end of this function runs the function theoptions_wordsearch() which displays the user's select/input answers and text input box answers and uses the highlight() function to do it.

We start by getting the relevant membership data using the MySQL SELECT statement. The group name is a link to the message sending script send-message-form_.php. We use the PHP functions htmlentities() and stripslashes() for data display security. Then we use SELECT again to get the relevant user's answer data from the appropriate db table. The use of id (in the table whose name is my_members) and member_id (in the table whose name is in the variable $aa) is because they both point to data from the same user, but it's not until much later in our script that we take advantage of the MySQL LEFT JOIN statement to use criteria from both tables at once to get data from one of them. Still in get_record_wordsearch(), we merely use a WHERE statement that has a logical effect of joining them without doing so: WHERE member_id='$id. Note the use of $row[$i2[$h]] in a for loop, to get each of the table rows with question answer data in it. One doesn't have to use the usual $row['datafield'] if one does not wish to—especially if the field name is a variable rather than a constant.

<?php

function get_record_wordsearch($id,$score2){
global $i1,$aa,$a,$i2,$r,$h,$score2;
$result = mysql_query("SELECT * FROM my_members WHERE id = '$id'") or die(mysql_error());
$num=mysql_num_rows($result);
if($num>0){
$row = mysql_fetch_array($result);
echo "<center><div id='t'><table border='2' id='j'><col width='470'><col width='330'>";
echo "<tr><td colspan='2' style='text-align:center;color:red'>(click link to send message)       <b><span style='color:black'>Group Name:</span> <a HREF='send-message-form_.php?gr=".htmlentities(stripslashes($row['groupname']), ENT_QUOTES)."'>".htmlentities(stripslashes($row['groupname']), ENT_QUOTES)."</a></b>       (word search results)</td></tr><br>";
echo "<tr><td colspan='2' style='text-align:center'><b>Location: ".htmlentities(stripslashes($row['city']), ENT_QUOTES).", ";
echo htmlentities(stripslashes($row['state']), ENT_QUOTES)." ";
echo htmlentities(stripslashes($row['zip']), ENT_QUOTES)."</b></td></tr><br>";

$score2=0;echo "<tr><th width='470' align='center'>Questions</th><th width='330' align='center'>Answers</th></tr>";
$sql=mysql_query("SELECT * FROM $aa WHERE member_id='$id'");
while($row = mysql_fetch_array($sql)){
for ($h = 0; $h < count($i2); $h++) {$r=$row[$i2[$h]];theoptions_wordsearch();}}
echo "<tr><td colspan='2'><center>(this group has ".$score2." of that search word)</center></td></tr></table><center><BR></center></div>";
}}

The function highlight() is next. It is used by the function theoptions_wordsearch() to highlight words. Each search word we find will get highlighted in light blue. The function uses preg_match_all() which can be used to not only count matches but to do the highlighting as well with clever use of a foreach loop and the PHP function str_replace() to supplement the preg_match_all() function's efforts. We make sure $x (the string to search) and $S (the search string) are not blank, then run the preg_match_all() function using the simple regular expression /$S+/i for a case insensitive pattern matching and the $matches variable to catch the results in an array, and the PHP count() function to grab the number of matches, which we stick in the $cm variable. In a foreach loop we use the str_replace() function to stick a highlighting span tag around the search words for the entire string in the variable $x (the string to search).

The function theoptions_wordsearch() checks the options table to see if there are any select/option list values in the administrator's form configuration for the specific field name whose data is being displayed. If none are found the field name contains a text input box value and that is what is echoed. Otherwise there are options to echo and that's what we do. Note that $i1[$h] is the question from the form and $r is the answer from the user data table. We run the highlight() function on the user data and keep score of the number of search words found. If we are dealing with a select/options situation, we use the PHP explode() function to break down the list of user-selected options we find in the user data table into an array. (The user-selected options are stored as a string that is a comma-separated list of options—perfect for exploding.) We echo the highlighted array element values to the screen, keeping track of the score again.

The function theoptions() is much like theoptions_wordsearch() except it displays search and match results, not word search results. The function checks the options table to see if there are any select/option list values in the administrator's form configuration for the specific field name whose data is being displayed. If none are found the field name contains a text input box value and that is what is echoed. Otherwise there are options to echo and that's what we do. Note that $i1[$h] is the question from the form and $r is the answer from the user data table. If we are dealing with a select/options situation, we use the PHP explode() function to break down the list of user-selected options we find in the user data table into an array. (The user-selected options are stored as a string that is a comma-separated list of options—perfect for exploding.) We echo the array element values to the screen.

The function thecheckboxes() is for echoing all the form's question and the user data table's answers to the screen, along with HTML form input checkboxes so searching and matching users can select search criteria. This comes after the inputs for the group name, city, state, zip, and proximity search criteria displaying. We grab only the Q and A relating to form inputs with options. To check which Q and A applies, we consult the $aaa options table where the option data is. So Q and A data fields are not in the $aaa table—the ones that are found ARE select/options list related so they get displayed as HTML input tags of the type "checkbox." Note the $i1[$h] and $i2[$h] arrays. The first is unfiltered and has many types of characters like punctuation and such, while the second has only alphanumerics and underscores, thereby allowing it to be used as field names and variable names. The explode() function is used because of the need to break down the list of user-selected options we find in the user data table into an array. (The user-selected options are stored as a string that is a comma-separated list of options—perfect for exploding.) We echo the array element values to the screen as HTML input tags of the type "checkbox".

The function underscore() has both the $i1[$h] and $i2[$h] arrays. The first is unfiltered and has many types of characters like punctuation and such, while the second has only alphanumerics and underscores, thereby allowing it to be used as field names and variable names. The filtering of the data happens in this PHP function. The middot character (·) is not handled too well in PHP so the preg_replace() function that ought to turn it into _ turns it into _middot_ instead, so we use a str_replace() function to change this into _. Our script needs both the $i1[$h] and $i2[$h] arrays.

function underscore(){
global $i1,$i2;
$replacement="_";
$pattern9 = '/[^A-Za-z0-9_]/i';
for ($h = 0; $h < count($i1); $h++) {
$i2[$h]=preg_replace($pattern9, $replacement, $i1[$h]);$i2[$h]=str_replace("_middot_","_",$i2[$h]);}}

The milesapart() and coords() functions are about getting the distances between 2 zipcodes. There are plenty of distance calculation formulas on the Internet—we chose the simpest and most effective. The function milesapart() uses the latitudes and longitudes from the MySQL database table which correspond to the zipcodes in the table (which are in the same record). The PHP functions milesapart() and coords() are distance scripts that need to compare the zip code of the user with the zip codes of the rest of the users in the database to determine which are within a certain distance (these distances are in the $dist[] array which is $dist=array(0,0,1000,500,200,100,50,10,2,0) and which will appear later) which has been selected by the user by him checking ONE of the checkboxes in the Willing_to_relocate section of the search form.

The zipcode database was free. Thanks, guys. It contains latitudes and longitudes and zips (and other stuff) which is enough info to enable us to calculate distances between zipcodes as long as they are in the USA and not military. We stuck all 42000+ records into a MySQL database table that is nearly 3MB in size. Yes, there are better zipcode databases that are more up to date and have the military zipcodes as well, but they cost a bit and we didn't feel like paying.

The coords() function gets 2 zipcodes sent to it as parameters. Then the MySQL db is queried and the zipcodes are looked up and the longitudes and latitudes are found and stuck in the parameters for calling the milesapart() function. The class is checked, as well, and if it is MILITARY, the function returns a value of 9999 miles. This means it will be too great a distance to ever get found in searches, whose biggest comparison criteria is 1000 miles or less. People in the armed services may be in your groups, but free is free. But the deciding factor is that the free zipcode database has empty strings for longitudes and latitudes for all records of class MILITARY.

If the zipcode is nonmilitary and USA, the coords() function consults the milesapart function for the distance apart the two zipcodes are. A zipcode area can be fairly large, so the map coordinates represent the center of each area only, not precise values for the distance apart of two groups. If the center of an area and an adjacent area are 20 miles apart, and your neighbor lives in one area but you are in the other area, the script will see your groups as 20 miles apart, so a search for groups within 2 miles or even 10 miles will fail to find this nearby group. The vast majority of searches will not involve this situation, of course. But it's a theoretically possible scenario.

The function restorem() is used in the subsearches (which means: searching last search results) to make sure that only the current search results are searched rather than all the data. When a user record has one or more search criteria and is part of the results, there will be a 1 or more in his score field in his my_members db table record. Whenever the user indicates he wants a subsearch, the page is reloaded with the URL query string ?searchtype=first, and when the script finds this, it looks through the my_members db table and all users with scores above 0 have the id fields saved in an array $first[]. Then when a search happens, the first thing the script does is zero all scores in the table, but if this subsearch is detected, the $first[] array is checked out, and all id values stored got results in the "first" search, so they get ones in their just-zeroed score fields. Then only search results are counted, in the subsearches, where the score field turns out to already have a nonzero score. The restorem() function is run whenever there is bad input that requires a page refresh, or whenever no results are found in a subsearch and the user is asked if he wants to search again on the old substring results or on all the data.

function restorem(){
global $first;
for ($i=0;$i<count($first);$i++) {
$sql="UPDATE my_members SET score=1 WHERE id='$first[$i]'";
$result=mysql_query($sql);}}

The function get_record() is a lot like the get_record_wordsearch() function except it is about displaying results from search and match searches, not word searches like with the get_record_wordsearch() function. The function get_record() is to get a single search result record and display it on the screen with a link to a private messaging page to send a message to this group. This function is run from a while loop that displays all the records that meet the criteria of containing at least one search criterion. It even informs the user how many search criteria were filled by the record.

Actually, this function only displays the first part of the record that comes from the my_members table. A for loop near the end of this function runs the function theoptions() which displays the user's select/input answers and text input box answers.

We start by getting the relevant membership data using the MySQL SELECT statement. The group name is a link to the message sending script send-message-form_.php. We use the PHP functions htmlentities() and stripslashes() for data display security. Then we use SELECT again to get the relevant user's answer data from the appropriate db table. The use of id (in the table whose name is my_members) and member_id (in the table whose name is in the variable $aa) is because they both point to data from the same user, but it's not until much later in our script that we take advantage of the MySQL LEFT JOIN statement to use criteria from both tables at once to get data from one of them. Still in get_record(), we merely use a WHERE statement that has a logical effect of joining them without doing so: WHERE member_id='$id. Note the use of $row[$i2[$h]] in a for loop, to get each of the table rows with question answer data in it. One doesn't have to use the usual $row['datafield'] if one does not wish to—especially if the field name is a variable rather than a constant.

We've already started a session at the start of the whole script. Now let's use it. It turns out that session variables are a much easier way to deal with values that need to survive refresh/restart and submissions than sticking hidden fields, POSTs, and GETs everywhere full of flags or values. The first thing the user does on this record entry page is to enter the table prefix name for this form and it is POSTed to $d. If the POST has reasonable content, it will (if found in the MySQL db as part of a table name) be used as the table prefix (since the prefix will be used before _database, _options, and _input tables in relating to MySQL tables). The db query SHOW TABLES LIKE '$aa' will be used to check whether the table prefix exists and therefore the tables exist. The $_SESSION['db']=$db command establishes a session variable, but if the mysql_num_rows() function finds no rows in the result set, this is quickly unset and the user sees "Table does not exist. Try again."

The very next command is that session variable being checked, and if the result is finding out it is unset, the echoed table prefix entry form will be presented again. Users cannot get anywhere until a valid name is entered. The input is filtered in the JavaScript checkdb() function. Bad input gets an alert and a refocusing of the cursor in the input box. Good input lets the user into the rest of the script, as you can see by the next PHP "if" conditional.

Next, we define some arrays and table name suffixes. Then we do a query to grab all the items from the db table $db."_inputs" and we stick them in the $i1[] array, using a php while() statement and array_push() function. Now the underscore() function is run.

$d=mysql_real_escape_string($_POST['db']);if(strlen($d)>2){
$db=$d;$aa=$db."_database";$exists = mysql_query("SHOW TABLES LIKE '$aa'") or die(mysql_error());
$_SESSION['db']=$db;$num_rows = mysql_num_rows($exists);
if($num_rows==0){unset($_SESSION['db']);unset($db);unset($d);mysql_close();
echo '<script language="javascript">alert("Table does not exist. Try again");</script>';}}

if(!isset($_SESSION['db'])){
echo "<div id='getdbname'><form name='nameform' id='nameform' method='post' action='match_.php' onsubmit='return checkdb()'>db name <input type='text' id='db' name='db' maxlength='20' size='20'><input type='submit' value='submit'></form></div>";}

if(isset($_SESSION['db'])){$db=$_SESSION['db'];

$dist=array(0,0,1000,500,200,100,50,10,2,0);
$id=$_SESSION['userid'];
$i1=array();$i2=array();$j=array();
$a=$db."_inputs";$aa=$db."_database";$aaa=$db."_options";

$sql=mysql_query("SELECT item FROM $a ORDER BY N");
$num_rows = mysql_num_rows($sql);
while($row = mysql_fetch_array($sql)){
array_push ($i1, htmlentities(stripslashes($row['item']), ENT_QUOTES));}
underscore();

We now use GET to get the search type the user has selected (by use of the links at the top of the screen). He can choose a word or matching search or matching/word subsearch. In other words, one can do subsearches on any set of results. We use POST to grab any search terms the user has entered in the word search HTML form to do a word search with. If his search term is less than 3 characters long, he gets a message and the page reloads—utilizing a URL query string ?searchtype=word to keep him in word search mode. The PHP function strip_tags() and preg_replace() and mysql_real_escape_string() are used on his search term. The first dumps tags for security reasons, and the last escapes unsafe characters for security reasons, and the middle one dumps unacceptable characters using a regular expression pattern as a data policeman to filter out iffy data.

Next we zero all the score fields in the my_members MySQL database table. Obviously, we cannot keep track of which user records are filling criteria and need displaying if we don't keep score somewhere, so we need to start with a clean slate, as it were.

Then we use the SELECT . . . WHERE . . . LIKE statements to see which records have search term matches. We use the result set from the winners to grab their data fields and use the PHP function substr_count() to count how many matches there are with the search term. However many there were is added to their score field by use of the MySQL UPDATE statement. If the $gotwordmatch variable stays 0 we give a "The search was unsuccessful." message and let them choose between 2 links: Search Again for a Word, and Search All.

The query "SELECT id,score FROM my_members WHERE score>0 ORDER BY score DESC LIMIT 30" is run to grab the search results from the db table. Note the limit 30, which prevents any more than 30 results from displaying. Note also the order by score desc, which makes sure that the results with the highest scores are displayed first. Next we run the get_record_wordsearch() function—the main display script. Finally we show the user how many results there were and ask if he wishes to search again.

$R=$_GET['searchtype'];
if($R<>"all" && $R<>"first" && $R<>"word"){$R="all";}

$S=$_POST['search'];
if($R=="word" && isset($S)){
if (strlen($S)<3) {echo '<script language="javascript">alert("Enter longer search terms.");window.location="match_.php?searchtype=word";</script>';unset($S);
}else{

$S=strip_tags($S);
$pattern2 = '/[^a-zA-Z0-9\\s\\.\\,\\!\\?]/i';
$replacement = '';
$S=preg_replace($pattern2, $replacement, $S);
$S=mysql_real_escape_string($S);

$gotwordmatch=0;
$sql="UPDATE my_members SET score=0";
$result=mysql_query($sql);

for ($h = 0; $h < count($i1); $h++) {$z=0;
$r=mysql_query("SELECT member_id,$i2[$h] FROM $aa WHERE $i2[$h] LIKE '%$S%'") or die('Error ,search failed');
$num_rows = mysql_num_rows($r);
if($num_rows>0){$gotwordmatch=1;
while($row = mysql_fetch_array($r)){
$word=$row[$i2[$h]];
$z=substr_count($word,$S);
$ID=$row['member_id'];
$sql="UPDATE my_members SET score=score+$z WHERE id='$ID'";$result=mysql_query($sql);}}}

if ($gotwordmatch==0){echo '<script language="javascript">alert("The search was unsuccessful.");</script><center><a HREF="match_.php?searchtype=word">Search Again for a Word</a>      <a HREF="match_.php?searchtype=all">Search All</a></center>';unset($S);
}else{

echo "<BR><BR><BR><BR>";
$rr=mysql_query("SELECT id,score FROM my_members WHERE score>0 ORDER BY score DESC LIMIT 30");
while($row = mysql_fetch_array($rr)){
$id=$row['id'];
$score=$row['score'];
get_record_wordsearch($id,$score);}
echo '<span style="background-color:red"><b>You got '.mysql_num_rows($rr).' results.</b></span>      <a HREF="match_.php?searchtype=word">Search Again</a>      <a HREF="match_.php?searchtype=all">Search All</a>';unset($S);
}}
}else

If the user chooses to do a word search but has not yet entered any input, the word search form is displayed and he sees "No hyphens (-) or underscores (_) or Enter/Return allowed in search terms. Use letters, numbers, spaces and these: , . ? ! in searches. Relevancy will determine the order of groups returned as search results—whatever group has the most word matches will be displayed first as it is the most relevant to your search." That takes care of word searches—now we will look at matching searches with ten allowed criteria.

if($R=="word" && !isset($S)){
echo '<div class="title">
<h1>Search Groups for Word/Phrase</h1>
<form method="post" action="match_.php?searchtype=word">
<table width="700" border="0" cellpadding="2" cellspacing="2" align="center">
<tr>
<td width="130">Search for:</td>
<td width="570"><input name="search" type="text" size="100"></td>
</tr>
<tr>
<td></td><td><input name="retrieve" type="submit" class="submit2" value="Search for word or exact phrase">
</td>
</tr>
</table>
</form>
</div>
<div id="info" class="info">No hyphens (-) or underscores (_) or Enter/Return allowed in search terms. Use letters, numbers, spaces and these: <B> , . ? ! </b> in searches. Relevancy will determine the order of groups returned as search results—whatever group has the most word matches will be displayed first as it is the most relevant to your search.<br></div>';
}

Now we get some of the POSTed search criteria input and sanitize it; recall from code above that we are done dealing with the possible situation of no search type yet selected, in which case the default is "all". Note that $fieldname and $fieldvalue come in as strings to be exploded into arrays $fieldnames[] and $fieldvalues[].

$Entry=$_POST['entry'];
if($Entry==1){
$replacement = '';
$fieldname = $_POST['fieldname'];
$fieldvalue = $_POST['fieldvalue'];
$relocate=0;$nolocate=0;$gotmatchrelocate=0;$gotmatch=0;

if(strlen($fieldvalue)<>0){
$pattern7= '/[^a-zA-Z0-9\\_\\,]/i';
$pattern8='/[^A-Za-z0-9\\!\\?\\$\\%\$\$\\_\\s\\.\\-\\·\\,]/i';
$fieldname=strip_tags($fieldname);
$fieldname=preg_replace($pattern7, $replacement, $fieldname);
$fieldvalue=strip_tags($fieldvalue);
$fieldvalue=preg_replace($pattern8, $replacement, $fieldvalue);
$fieldnames = explode(',',$fieldname);
$fieldvalues = explode(',',$fieldvalue);

We need to process the arrays. If a field name in the $fieldnames array is "Willing_to_relocate", we need to fill the $relocate variable with a value from the $dist[] array that corresponds to the parallel $fieldvalues[] array being used as the element number in $dist[]. As soon as we have filled the $relocate variable with a $dist[] value, we unset() the $fieldvalue and $fieldname variables if the array lengths are exactly 1. I.e., if you search only for the distance criteria, dump these variables as they will be used as flags later. Additionally, we unset the array elements corresponding to the "Willing_to_relocate" name and its value. Next we use the PHP function array_values() to renumber the other array elements one lower so there is no gap.

for($i=0;$i<count($fieldvalues);$i++){
if($fieldnames[$i]=="Willing_to_relocate"){$ii=$i;$relocate=$dist[$fieldvalues[$i]];
}}
if($relocate>0 && count($fieldvalues)==1){unset($fieldvalue);unset($fieldname);}
if($relocate>0){unset($fieldvalues[$ii]);unset($fieldnames[$ii]);$fieldvalues=array_values($fieldvalues);$fieldnames=array_values($fieldnames);}}

Next we get the user's zipcode into $z1. Then—if the search type flag $R is "first"—we push the IDs of the records with nonzero scores into the $first array. Then we deal with any use of groupname, city, state, or zip as search criteria. We get the POSTs and process them, using the substr() function to ensure they are not too long. We filter them using the strip_tags() function to dump tags and the preg_replace() function to dump unacceptable characters. If their search strings are too short, they get alerts and must start the search over, but the type of search is maintained by putting the flag into a query string.

$r=mysql_query("SELECT zip FROM my_members WHERE username='$U'");
$row = mysql_fetch_array($r);
$z1=$row['zip'];

if($R=="first"){
$first=array();
$r=mysql_query("SELECT id FROM my_members WHERE score>0 ORDER BY score DESC LIMIT 30");
while($row = mysql_fetch_array($r)){
array_push ($first, $row[0]);}}

$G=$_POST['groupname'];
$C=$_POST['city'];
$S=$_POST['state'];
$Z=$_POST['zip'];

$G=substr($G,0,20);
$C=substr($C,0,33);
$S=substr($S,0,2);
$Z=substr($Z,0,5);

$pattern1 = '/[^a-zA-Z\\-\\s]/i';
$pattern3 = '/[^a-zA-Z0-9\\_]/i';
$pattern5 = '/[^0-9]/';
$pattern6 = '/[^A-Z]/';
$G=strip_tags($G);
$C=strip_tags($C);
$S=strip_tags($S);
$Z=strip_tags($Z);
$C=preg_replace($pattern1, $replacement, $C);
$Z=preg_replace($pattern5, $replacement, $Z);
$G=preg_replace($pattern3, $replacement, $G);
$S=preg_replace($pattern6, $replacement, $S);

if (strlen($G)<6&&strlen($G)>0) {restorem();echo '<script language="javascript">alert("Please enter 6 to 20 characters for group name."); window.location = "match_.php?searchtype='.$R.'"; </script>';
}else{
if (strlen($C)<2&&strlen($C)>0) {restorem();echo '<script language="javascript">alert("Please enter 2 to 33 characters for city."); window.location = "match_.php?searchtype='.$R.'"; </script>';
}else{
if ((strlen($S)<2 || strlen($S)>2)&&strlen($S)>0) {restorem();echo '<script language="javascript">alert("Please use dropdown list for state."); window.location = "match_.php?searchtype='.$R.'"; </script>';
}else{
if (strlen($Z)<5&&strlen($Z)>0) {restorem();echo '<script language="javascript">alert("Please enter 5 digits for zip code."); window.location = "match_.php?searchtype='.$R.'"; </script>';
}else{

Now the searching begins. We zero the field called score. We search for any of these: groupname, city, state, and zip. We use the flag $gotmatch to either signal we tried to find one of these but failed (-1) or we got a match (1). We increment the score of records with found search criteria. Next we use if(strlen($fieldvalue)<>0){ to decide if we will search for any of the fields that are not groupname, city, state, and zip. If the distance search from the "Willing_to_relocate" input is used alone as a criteria, the $fieldvalue variable will have been unset so we skip this section. Again we use the $gotmatch=-1 as a tried but failed flag until it changes to 1 because a criteria is found. We look at the values in fields whose name is the one stored in the $fieldname[] array element value, in a loop. If they are LIKE the $fieldvalues[$i] value stored in $v, $gotmatch gets a 1 and we UPDATE the my_members table's score field for the user with a match by using his id. The MySQL LIKE operator, lets the field be searched in a substring style, when we use the % wildcard on either side of the search target. The mysql_num_rows() function will work to get the number of rows in the result. The variable $gotmatch gets a 1 if the result set is not 0. We will always increment the score of records with positive results because this is our sole scorekeeping method.

If the search type flag ($R) is "first"—meaning we are doing Search Your Results—we use the IDs of the LAST search's records with positive results by looping through the $first[] array and putting a 1 in those records' score fields. Now, only records with nonzero score fields will get searched. We again search for groupname, city, state, and zip and search for any of the fields that are not groupname, city, state, and zip if the $fieldvalue variable is not 0. If $gotmatch is 1, we now decrement the score field of records with scores>0. As explained before, this will cause the search results to contain only the records whose score field had been GREATER THAN 1 before we decremented the score field.

Note the LEFT JOIN in our SELECT statement. Because we need to select data from one table if it meets criteria in two separate tables, we figured at first that we were in for a rough ride—we were not even convinced that our goal was doable. Consulting W3Schools LEFT JOIN page was little help. The closest we could find was the SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1; statement found on the MySQL Manual's LEFT JOIN and RIGHT JOIN Optimization page. The t1.column1=t2.column1 statement merely declares the common column that allows the join since they both have column1. But our tables' common columns were not even the same name (id and member_id). And one of our table's names was a variable ($aa) which was not allowed in our LEFT JOIN, so we had to prepare simple variables and hope we could sneak them by the MySQL parser. But, more importantly, no example anywhere on the internet showed how to select data from one table if it meets criteria in two separate tables in one SELECT. All examples show WHERE statements with search criteria relating to ONE table. So we tried a few configurations and JOIN types and finally hit on one that worked: SELECT member_id FROM $aa LEFT JOIN my_members ON my_members.id = $l WHERE my_members.score>0 AND $q LIKE '%$v%', with $v=$fieldvalues[$i], $l=$aa.".member_id", and $q=$aa.".".$fieldnames[$i]. Whew!

We guess that all searches for select data from one table if it meets criteria in two separate tables from now on will end up at our doorstep—right, Google? We searched for it and got squat, nada, doodah, zip, bupkiss, zipadeedoodah, and irrelevant crap. Surely we are not the first people with the need to have criteria from 2 tables in one SELECT! I guess we are simply the first to put it on the Web.

If there are no results to display, $gotmatch is less than 1 && $relocate is 0, and we display a message and the user sees "No results were found. If you want to Search Your Results, click OK. If you want to Search All, click Cancel." A JavaScript confirm function allows the choice. Note that the restorem() function is run and the score fields are incremented from the $first[] array. There is one other variable combination that will lead to the "no results" message: $relocate>0 && $gotmatchrelocate<1 OR $relocate==0 && $nolocate==0. This would mean that the distance option ($relocate>0) was used in the search but no results ($gotmatchrelocate<1) were found OR that the distance option was not searched for and no non-distance results were found. Again the restorem() function is run and the score fields are incremented from the $first[] array.

Note that in the 2 "successful search" code blocks, we use $w=-1;if($R=="first"){$w=0;}, then when we UPDATE the db table the score>'$w' has the effect of updating only if the score is greater than 0 already if we are in Search Your Results mode, but updating only if the score is greater than -1 (which it always is) if we are in Search All mode. Before finishing this code area, if we are in Search Your Results mode we decrement the score field so only records whose score field is GREATER THAN 1 will be in the displayed results.

The query "SELECT id,score FROM my_members WHERE score>0 ORDER BY score DESC LIMIT 30" is run to grab the search results from the db table. Note the limit 30, which prevents any more than 30 results from displaying. Note also the order by score desc, which makes sure that the results with the highest scores are displayed first. Next we run the get_record() function—the main display script. Finally we show the user how many results there were. And display the navigation menu, of course.

//SEARCH HERE
$gotmatch=0;
$sql="UPDATE my_members SET score=0";
$result=mysql_query($sql);

if($R=="all"){$gotmatch=-1;

if(strlen($G)>0){
$r=mysql_query("SELECT id FROM my_members WHERE groupname='$G'");
$num_rows = mysql_num_rows($r);
if($num_rows>0){$gotmatch=1;
while($row = mysql_fetch_array($r)){
$ID=$row['id'];$sql="UPDATE my_members SET score=score+1 WHERE id='$ID'";$result=mysql_query($sql);}}}

if(strlen($C)>0){
$r=mysql_query("SELECT id FROM my_members WHERE city='$C'");
$num_rows = mysql_num_rows($r);
if($num_rows>0){$gotmatch=1;
while($row = mysql_fetch_array($r)){
$ID=$row['id'];$sql="UPDATE my_members SET score=score+1 WHERE id='$ID'";$result=mysql_query($sql);}}}

if(strlen($S)>0){
$r=mysql_query("SELECT id FROM my_members WHERE state='$S'");
$num_rows = mysql_num_rows($r);
if($num_rows>0){$gotmatch=1;
while($row = mysql_fetch_array($r)){
$ID=$row['id'];$sql="UPDATE my_members SET score=score+1 WHERE id='$ID'";$result=mysql_query($sql);}}}

if(strlen($Z)>0){
$r=mysql_query("SELECT id FROM my_members WHERE zip='$Z'");
$num_rows = mysql_num_rows($r);
if($num_rows>0){$gotmatch=1;
while($row = mysql_fetch_array($r)){
$ID=$row['id'];$sql="UPDATE my_members SET score=score+1 WHERE id='$ID'";$result=mysql_query($sql);}}}

if(strlen($fieldvalue)<>0){$gotmatch=-1;

for($i=0;$i<count($fieldvalues);$i++){

if($fieldvalues[$i]<>"-1"){
$v=$fieldvalues[$i];
$r=mysql_query("SELECT member_id FROM $aa WHERE $fieldnames[$i] LIKE '%$v%'");}
$num_rows = mysql_num_rows($r);
if($num_rows>0){$gotmatch=1;}

while($row = mysql_fetch_array($r)){
$ID=$row['member_id'];$sql="UPDATE my_members SET score=score+1 WHERE id='$ID'";
$result=mysql_query($sql);}}}

}else{$gotmatch=-1; //the else is if $R=="first" -- so below is if it's "first"

for($i=0;$i<count($first);$i++){
$sql="UPDATE my_members SET score=1 WHERE id='$first[$i]'";
$result=mysql_query($sql);}

if(strlen($G)>0){
$r=mysql_query("SELECT id FROM my_members WHERE groupname='$G' AND score>0");
$num_rows = mysql_num_rows($r);
if($num_rows>0){$gotmatch=1;
while($row = mysql_fetch_array($r)){
$ID=$row['id'];$sql="UPDATE my_members SET score=score+1 WHERE id='$ID'";$result=mysql_query($sql);}}}

if(strlen($C)>0){
$r=mysql_query("SELECT id FROM my_members WHERE city='$C' AND score>0");
$num_rows = mysql_num_rows($r);
if($num_rows>0){$gotmatch=1;
while($row = mysql_fetch_array($r)){
$ID=$row['id'];$sql="UPDATE my_members SET score=score+1 WHERE id='$ID'";$result=mysql_query($sql);}}}

if(strlen($S)>0){
$r=mysql_query("SELECT id FROM my_members WHERE state='$S' AND score>0");
$num_rows = mysql_num_rows($r);
if($num_rows>0){$gotmatch=1;
while($row = mysql_fetch_array($r)){
$ID=$row['id'];$sql="UPDATE my_members SET score=score+1 WHERE id='$ID'";$result=mysql_query($sql);}}}

if(strlen($Z)>0){
$r=mysql_query("SELECT id FROM my_members WHERE zip='$Z' AND score>0");
$num_rows = mysql_num_rows($r);
if($num_rows>0){$gotmatch=1;
while($row = mysql_fetch_array($r)){
$ID=$row['id'];$sql="UPDATE my_members SET score=score+1 WHERE id='$ID'";$result=mysql_query($sql);}}}

if(strlen($fieldvalue)<>0){$gotmatch=-1;

for($i=0;$i<count($fieldvalues);$i++){

if($fieldvalues[$i]<>"-1"){
$v=$fieldvalues[$i];
$l=$aa.".member_id";
$q=$aa.".".$fieldnames[$i];
$r=mysql_query("SELECT member_id FROM $aa LEFT JOIN my_members ON my_members.id = $l WHERE my_members.score>0 AND $q LIKE '%$v%'") or die(mysql_error());}
$num_rows = mysql_num_rows($r);
if($num_rows>0){$gotmatch=1;}

while($row = mysql_fetch_array($r)){
$ID=$row['member_id'];$sql="UPDATE my_members SET score=score+1 WHERE id='$ID' AND score>0";$result=mysql_query($sql);}}}

if($gotmatch==1){$sql="UPDATE my_members SET score=score-1 WHERE score>0";$result=mysql_query($sql);}

}//the end of the else that is if $R=="first" (the stuff below is for either "first" or "all")

if ($gotmatch<1 && $relocate==0){restorem();echo '<script language="javascript">var answer = confirm ("No results were found. If you want to Search Your Results, click OK. If you want to Search All, click Cancel.");if (answer){window.location = "match_.php?searchtype='.$R.'";}else{window.location = "match_.php";} </script>';
}else{

if($gotmatch==1 && $relocate==0){$nolocate=1;}
$gotmatchrelocate=0;

if($gotmatch==1 && $relocate>0){
$nolocate==0;$w=-1;if($R=="first"){$w=0;};
$r=mysql_query("SELECT id,zip FROM my_members WHERE score>'$w'");
while($row = mysql_fetch_array($r)){
$id=$row['id'];$z2=$row['zip'];$zz=coords($z1,$z2);
if($relocate>=$zz){$sql="UPDATE my_members SET score=score+1 WHERE id='$id' AND score>'$w'";$result=mysql_query($sql);$gotmatchrelocate=mysql_affected_rows();
}else{

$sql="UPDATE my_members SET score=0 WHERE id='$id'";$result=mysql_query($sql);
}}
if($w==0){$sql="UPDATE my_members SET score=score-1 WHERE score>0";$result=mysql_query($sql);}}

if($gotmatch<1 && $relocate>0){
$nolocate==0;$w=-1;if($R=="first"){$w=0;};
$r=mysql_query("SELECT id,zip FROM my_members WHERE score>'$w'");
while($row = mysql_fetch_array($r)){
$id=$row['id'];$z2=$row['zip'];$zz=coords($z1,$z2);
if($relocate>=$zz){$sql="UPDATE my_members SET score=score+1 WHERE id='$id' AND score>'$w'";$result=mysql_query($sql);$gm=mysql_affected_rows();if($gm==1){$gotmatchrelocate=1;}
}else{

$sql="UPDATE my_members SET score=0 WHERE id='$id'";$result=mysql_query($sql);
}}
if($w==0){$sql="UPDATE my_members SET score=score-1 WHERE score>0";$result=mysql_query($sql);}}

if(($relocate>0 && $gotmatchrelocate<1) || ($relocate==0 && $nolocate==0)){restorem();echo '<script language="javascript">var answer = confirm ("No results were found. If you want to Search Your Results, click OK. If you want to Search All, click Cancel.");if (answer){window.location = "match_.php?searchtype='.$R.'";}else{window.location = "match_.php";} </script>';
}else{

echo "<BR><BR><BR><BR>";
$rr=mysql_query("SELECT id,score FROM my_members WHERE score>0 ORDER BY score DESC LIMIT 30");
while($row = mysql_fetch_array($rr)){
$id=$row['id'];
$score=$row['score'];
get_record($id,$score);}
echo '<span style="background-color:red"><b>You got '.mysql_num_rows($rr).' results.</b></span>';}

mysql_close();

echo '<center><div class="j"><center><h1>Search and Match Results</h1><br>
<a HREF="http://www.css-resources.com/">Home</a>     <a HREF="profile-and-account-management.php">Account Management</a>     <a HREF="match_.php?searchtype=all">Search All</a>     <a HREF="match_.php?searchtype=first">Search These Results</a>     <a HREF="#" onclick="saveresults()">Save These Results</a>     <a HREF="match_.php?searchtype=word">Word Search</a></center></div></center>';

}}}}}}}

if($Entry==0 && isset($_SESSION['db']) && $R<>"word" && !isset($S)){ ?>

<center><div class='j'><center><h1>Search and Match</h1><form id='formpw' name="formpw" method="post" action="match_.php?searchtype=<?php echo $R; ?>" onsubmit="return validatepassword()"><br>
<a HREF="http://www.css-resources.com/">Home</a>       <a HREF="profile-and-account-management.php">Account Management</a>       <a HREF="register-with-captcha_.php">Register</a>       <a HREF="login_.php">Login</a>       <a HREF="match_.php?searchtype=word">Word Search</a></center></div></center>

<div id='myform'><center><h3>Search Criteria</h3><u><i><span style='color:maroon;font-weight:bold;font-size:18px'>Check or enter up to 10 search criteria.</span></i></u></center>
<center>
<table id='y' cellspacing=0 cellpadding=0 border=0>
<tr><th colspan='2' class='c'><b>Profile</b></th></tr>
<tr><td class='i'><label for="Group Name"><b>Group Name: </b></td><td><input type="text" name="groupname" size="20" maxlength="20" value=""></label></td></tr>
<tr><td class='i'><label for="City"><b>City: </b></td><td><input type="text" name="city" size="20" maxlength="33" value=""></label></td></tr>
<tr><td class='i'><label for="State"><b>State: </b></td><td>
<select name="state" size='4'>
<option value="AL">Alabama</option>
<option value="AK">Alaska</option>
<option value="AZ">Arizona</option>
<option value="AR">Arkansas</option>
<option value="CA">California</option>
<option value="CO">Colorado</option>
<option value="CT">Connecticut</option>
<option value="DE">Delaware</option>
<option value="DC">District of Columbia</option>
<option value="FL">Florida</option>
<option value="GA">Georgia</option>
<option value="HI">Hawaii</option>
<option value="ID">Idaho</option>
<option value="IL">Illinois</option>
<option value="IN">Indiana</option>
<option value="IA">Iowa</option>
<option value="KS">Kansas</option>
<option value="KY">Kentucky</option>
<option value="LA">Louisiana</option>
<option value="ME">Maine</option>
<option value="MD">Maryland</option>
<option value="MA">Massachusetts</option>
<option value="MI">Michigan</option>
<option value="MN">Minnesota</option>
<option value="MS">Mississippi</option>
<option value="MO">Missouri</option>
<option value="MT">Montana</option>
<option value="NE">Nebraska</option>
<option value="NV">Nevada</option>
<option value="NH">New Hampshire</option>
<option value="NJ">New Jersey</option>
<option value="NM">New Mexico</option>
<option value="NY">New York</option>
<option value="NC">North Carolina</option>
<option value="ND">North Dakota</option>
<option value="OH">Ohio</option>
<option value="OK">Oklahoma</option>
<option value="OR">Oregon</option>
<option value="PA">Pennsylvania</option>
<option value="RI">Rhode Island</option>
<option value="SC">South Carolina</option>
<option value="SD">South Dakota</option>
<option value="TN">Tennessee</option>
<option value="TX">Texas</option>
<option value="UT">Utah</option>
<option value="VT">Vermont</option>
<option value="VA">Virginia</option>
<option value="WA">Washington</option>
<option value="WV">West Virginia</option>
<option value="WI">Wisconsin</option>
<option value="WY">Wyoming</option>
</select>
</label></td></tr>
<tr><td class='i'><label for="Zip"><b>Zip: </b></td><td><input type="text" name="zip" size="5" maxlength="5" value=""></label></td></tr>
<tr><td colspan='2'>
<center><b>Proximity Search</b></center></td></tr>
<tr><td class='k'> </td><td align='left' style='color:blue'>*Selecting any of these will limit your results to the distance selected.</td></tr>

<tr><td class='k'><input type="checkbox" name="Willing_to_relocate" value="2" onchange="c()"></td><td>*Only find groups within 1000 miles of my group's zipcode</td></tr>
<tr><td class='k'><input type="checkbox" name="Willing_to_relocate" value="3" onchange="c()"></td><td>*Only find groups within 500 miles of my group's zipcode</td></tr>
<tr><td class='k'><input type="checkbox" name="Willing_to_relocate" value="4" onchange="c()"></td><td>*Only find groups within 200 miles of my group's zipcode</td></tr>
<tr><td class='k'><input type="checkbox" name="Willing_to_relocate" value="5" onchange="c()"></td><td>*Only find groups within 100 miles of my group's zipcode</td></tr>
<tr><td class='k'><input type="checkbox" name="Willing_to_relocate" value="6" onchange="c()"></td><td>*Only find groups within 50 miles of my group's zipcode</td></tr>
<tr><td class='k'><input type="checkbox" name="Willing_to_relocate" value="7" onchange="c()"></td><td>*Only find groups within 10 miles of my group's zipcode</td></tr>
<tr><td class='k'><input type="checkbox" name="Willing_to_relocate" value="8" onchange="c()"></td><td>*Only find groups within 2 miles of my group's zipcode</td></tr>

<tr><th colspan='2'><b> </b></th></tr>
<tr><th colspan='2' class='c'><b>Questions</b></th></tr>
<?php thecheckboxes(); ?>
<tr><th colspan='2'><b> </b></th></tr>
<input name="entry" type="hidden" value="1">
<input name='fieldname' type='hidden' value=''>
<input name='fieldvalue' type='hidden' value=''>
<tr><td class='k'> </td><td><BR><input type="submit" value="Submit" class='submit'>
           
<input type="reset" value="Reset" class='submit'></td></tr></table>
</form><BR><BR>
</div>

<?php
mysql_close();
}
?>

</body>
</html>

Free Personal Status Boards (PSB™)

PHP Code for HTML Form Creator—Search and Match: Search for Compatible Groups

Administrative HTML Form Creator Scripts

End-User HTML Form Creator Scripts