Match and count user inputs against Mysql fields

Hi there,

I am new to PHP & Mysql and I have managed to build a basic search page.
please see the code below
Currently i am able to select single field type and search within....
I would like to search multiple fields at the same time and return the matching

E.g.
FieldType  = Sounds
 User input = ?

FieldType = Generic
User input = ?

FieldType = Activity
User input = ?

SEARCH BUTTON


RESULT

Echo story IDs of matching all three input in each rows in the table
       and how many in total
Echo story ids of matching Two types appearing in a single row
          and how many in total
Echo Story id of matching One
         and how many in total

Echo how many in total each words occurs all three fields  


Thank you..and please ask me if i don't make sense..thanks again
search.php
-----search page
$searchtype=$_POST['searchtype']; 
$searchterm=trim($_POST['searchterm']); 

if (!$searchtype || !$searchterm) { 
echo 'You have not entered search details.  Please go back and try again.'; 
exit; 
} 
if (!get_magic_quotes_gpc()){ 
$searchtype = addslashes($searchtype); 
$searchterm = addslashes($searchterm); 
} 
@ $db = new mysqli('localhost', 'root', 'pass', 'dbname'); 
if (mysqli_connect_errno()) { 
echo 'Error: Could not connect to database.  Please try again later.'; 
exit; 
} 
$query = "select * from stories where ".$searchtype."  like '%".$searchterm."%'"; 
$result = $db->query($query); 
$num_results = $result->num_rows; 
echo "<p>Number of records found: ".$num_results."</p>"; 
for ($i=0; $i <$num_results; $i++) { 
$row = $result->fetch_assoc(); 

echo "<strong> <br />Stor ID:</strong> "; 
echo stripslashes($row['id']);

echo.....

} 

$result->free(); 



form.php
<form action="search.php" method="post">

Search by Person:<br />
<select name="searchtype">
<option value="sounds">Sounds</option>
<option value="generic">Generic</option>
<option value="activity">Activity</option>
</select>
<br />
Enter Search Term:<br />
<input name="searchterm" type=”"text" size="40"/>
<br />
<input type="submit" name="submit" value="Search"/>
</form>

Open in new window

tony_stockmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tekati68Information Technology DirectorCommented:
It's really not making much sense. Can you show an example of what you are looking for.
0
Meir RivkinFull stack Software EngineerCommented:
it seems that the only change u need to make is to the sql query.
if u wish to search multiple fields at the same u need to add integrate those fields in your query.

i see that u have 3 fields from user input: Sounds, Generic and Activity.
0
tony_stockmanAuthor Commented:
Thanks for responding, im sorry i wasnt clear enoungh...okee hear it is... What i want is shown under "E.g" n "RESULT" above and what i have is in the code i hav provided...

My current search page has only one field please see my form php code n search

0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

tony_stockmanAuthor Commented:
Hi.... here is what I want again

The search form should look like this

Example \
FieldName  = "Question"
 User input = ? e.g. what is it?

FieldName = "Answers"
User input = ?  e.g. nothing

FieldName = "Elements"
User input = ?  e.g. animal

SEARCH BUTTON

Once user clicked on search button.... it should retrieve and echo all the Records(rows) which contain each keyword in a single row under each field name.  We display 100% match on the top of the page and then records which 50% matches (Two fields) and One field at the bottom.

the Result page should look like:

 "2 records found with 100% match"
         id    Question     Answers   Elements  
          1    what is it?   nothing    animal
         33    what is it?   nothing    animal


 "2 records match 60%(2/3)"
        id    Question       Answers    Elements  
         25    what is it?    somthing   animal
         60    where is it?   nothing    animal


 [B]"2 record match 30%(1/3)"
  [B]  id    Question       Answers    Elements  [/B]
       30    how is it?     nothing    animal
       80    where is it?   nothing    animal


please help..
Thank you


Currently my search engine retrieve data from single field..please feel free to modify my code and help me.

form.php
<form action="results.php" method="post"> 
Search within:<br /> 
<select name="searchtype"> 
<option value="question">Question</option> 
<option value="answers">Answers</option> 
<option value="elements">Elements</option> 
</select> 
<br /> 
Enter Search Term:<br /> 
<input name="searchterm" type=”"text" size="40"/> 
<br /> 
<input type="submit" name="submit" value="Search"/> 
</form> 





result.php


<?php 
// create short variable names 
$searchtype=$_POST['searchtype']; 
$searchterm=trim($_POST['searchterm']); 

if (!$searchtype || !$searchterm) { 
echo 'You have not entered search details.  Please go back and try again.'; 
exit; 
} 
if (!get_magic_quotes_gpc()){ 
$searchtype = addslashes($searchtype); 
$searchterm = addslashes($searchterm); 
} 
@ $db = new mysqli('localhost', 'root', '', ''); 

if (mysqli_connect_errno()) { 
echo 'Error: Could not connect to database.  Please try again later.'; 
exit; 



} 
$query = "select * from table where ".$searchtype." like '%".$searchterm."%'"; 
$result = $db->query($query); 
$num_results = $result->num_rows; 

echo "<p>Number of records found: ".$num_results."</p>"; 

for ($i=0; $i <$num_results; $i++) { 
$row = $result->fetch_assoc(); 




echo "<p><strong><br>".($i+1).". First Name: </strong>"; 
echo htmlspecialchars(stripslashes($row['fname'])); 
echo "<strong><br />Last Name: </strong>"; 
echo stripslashes($row['lname']); 

echo "<strong>Question: </strong>"; 
echo stripslashes($row['question']);
echo "<strong> <br />Answer: </strong>"; 
echo stripslashes($row['answers']);
//echo "</p>";

echo "<strong> <br />Elements:</strong> "; 
echo stripslashes($row['elements']);



$db->close(); 
?>

Open in new window

0
Ray PaseurCommented:
Here's how I might approach the task.  Take the client's input search term, and do three separate queries - one each against each of the three salient columns in the table.  The number of rows in the three results sets will tell you whether you've found the terms in (for example) Questions only, or Questions plus Answers, etc.
0
tony_stockmanAuthor Commented:

hi Ray..
yes.. i want the output to be like this

If the user input "what is it?" for the Question, "nothing" in "answers" and "animal" in Elements search fields and hit search button result shuld will come up like this...

 "2 records found with 100% match (3/3)"
         id    Question     Answers   Elements  
          1    what is it?   nothing    animal
         33    what is it?   nothing    animal


 "2 records match 60%(2/3)"
        id    Question       Answers    Elements  
         25    what is it?    somthing   animal
         60    how is it?   nothing    animal


 [B]"2 record match 30%(1/3)"
  [B]  id    Question       Answers    Elements  [/B]
       30    how is it?     something   animal
       80    where?       nothing         nonanimal


how do i modify the query and out put result in Result.php above and the  and code in form.php

Thanks for your time
0
Ray PaseurCommented:
I don't really have the time to work out all the details here, so I'll just try to suggest some ways to approach this.

First, create a temporary table with all the matches in the disjunctive ('OR') so you're looking for this kind of thing:

Question = 'what is it?' OR Answers = 'nothing' OR Elements = 'animal'

This would give you all 6 of the rows you've got above.  Next you can create queries against this (smaller, faster) temporary table to match the conjunctive ('AND') for the 100% match, and combinations of AND/OR for the 2/3 and 1/3 matches.
0
tony_stockmanAuthor Commented:

HI Ray,

Can you please write just the SQL query  for me lease.. I am not an expert in php.. i need help
Thank you
0
Ray PaseurCommented:
Like I said, I have paid work, and I do not have time to program this for you.  But this code snippet should help you get started.  It teaches how to connect and select the data base, how to create the test data, how to down-select only the relevant records, how to find the 100% matches.  You'll need to take it from here.

The important moving parts start near line 110.

You might want to buy this book: http://www.sitepoint.com/books/phpmysql4/ -- it won't make you a professional programmer, but it is easy to read, has good examples, and comes with a downloadable code library so you can easily test and modify the examples.

Best of luck with your project, ~Ray
<?php // RAY_temp_tony_stockman.php
// UNTESTED CODE SAMPLE
// http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/Q_26403543.html

error_reporting(E_ALL);
echo "<pre>";

// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://us2.php.net/manual/en/ref.mysql.php
// MAN PAGE: http://us2.php.net/manual/en/mysql.installation.php
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-error.php

// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES




// CREATING A TABLE OF TEST DATA.
$sql
= "CREATE TEMPORARY TABLE my_table
( i int         NOT NULL AUTO_INCREMENT
, q varchar(24) NOT NULL DEFAULT ''
, a varchar(24) NOT NULL DEFAULT ''
, e varchar(24) NOT NULL DEFAULT ''
, PRIMARY KEY(i)
) ENGINE=MEMORY"
;
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}

// SETTING UP SOME TEST DATA IN ARRAY OF ASSOCIATIVE ARRAYS - ORDER IS IRRELEVANT
$t
= array
( array ( 'i' =>  1, 'q' => 'what is it?', 'a' => 'nothing',   'e' => 'animal' )
, array ( 'i' => 25, 'q' => 'what is it?', 'a' => 'something', 'e' => 'animal' )
, array ( 'i' => 30, 'q' => 'how is it?',  'a' => 'nothing',   'e' => 'animal' )
, array ( 'i' => 33, 'q' => 'what is it?', 'a' => 'nothing',   'e' => 'animal' )
, array ( 'i' => 60, 'q' => 'how is it?',  'a' => 'nothing',   'e' => 'animal' )
, array ( 'i' => 80, 'q' => 'where?',      'a' => 'nothing',   'e' => 'nonanimal' )
, array ( 'i' => 99, 'q' => 'when is it?', 'a' => 'tomorrow',  'e' => 'vegetable' ) // THIS ONE IS AN OUTLIER
)
;

// LOADING THE TEST DATA INTO THE TABLE
foreach ($t as $arr)
{
    // ESCAPING THE DATA VALUES IN EACH SUB-ARRAY
    foreach ($arr as $key => $val)
    {
        // MAN PAGE: http://us2.php.net/manual/en/function.mysql-real-escape-string.php
        $arr[$key] = mysql_real_escape_string($val);
    }

    // CREATE THE QUERY FROM THE KEYS AND VALUES OF EACH SUB-ARRAY
    $sql
    = "INSERT INTO my_table ("
    . implode(',', array_keys($arr))
    . ") VALUES ("
    . "'" . implode("', '", array_values($arr)) . "'"
    . ")"
    ;

    // RUN EACH QUERY TO LOAD EACH ROW OF THE TABLE
    if (!$res = mysql_query($sql))
    {
        $errmsg = mysql_errno() . ' ' . mysql_error();
        echo "<br/>QUERY FAIL: ";
        echo "<br/>$sql <br/>";
        die($errmsg);
    }

    // SEE THE QUERIES AS THEY ARE RUN
    echo PHP_EOL . $sql;
}




// EXTRACTING THE IMPORTANT TEST DATA FROM THE BIG TABLE INTO THE SMALLER TABLE
$sql
= "CREATE TEMPORARY TABLE small_table
( i int         NOT NULL AUTO_INCREMENT
, q varchar(24) NOT NULL DEFAULT ''
, a varchar(24) NOT NULL DEFAULT ''
, e varchar(24) NOT NULL DEFAULT ''
, PRIMARY KEY(i)
) ENGINE=MEMORY"
;

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res = mysql_query($sql))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}




// DOWN-SELECTING THE RELEVANT ROWS OF DATA USING THE SEARCH TERMS IN THE DISJUNCTIVE
$sql
= "INSERT INTO small_table (i,q,a,e) SELECT i,q,a,e FROM my_table "
. " WHERE "
. " ( q = 'what is it?' ) OR "
. " ( a = 'nothing'     ) OR "
. " ( e = 'animal'      ) "
;

if (!$res = mysql_query($sql))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}

// SEE THE DATA IN THE DOWN-SELECTED TABLE
$sql = "SELECT i,q,a,e FROM small_table";
if (!$res = mysql_query($sql))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
echo PHP_EOL;
while ($row = mysql_fetch_assoc($res))
{
    var_dump($row);
}




// FIND NUMBER OF RECORDS DATA THAT MATCHES ANYTHING
$sql = "SELECT i FROM small_table";
if (!$res = mysql_query($sql))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
$num = mysql_num_rows($res);
echo PHP_EOL . 'NUMBER OF ROWS WITH ANY MATCH: ' . $num;




// FIND RECORDS THAT MATCH 100%
$sql
= "SELECT i FROM small_table "
. " WHERE "
. " ( q = 'what is it?' ) AND "
. " ( a = 'nothing'     ) AND "
. " ( e = 'animal'      ) "
;
if (!$res = mysql_query($sql))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
$num = mysql_num_rows($res);
echo PHP_EOL . 'NUMBER OF ROWS WITH 100% MATCH: ' . $num;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tony_stockmanAuthor Commented:

Thank you for your time Ray.
I will go through the code now...
0
tony_stockmanAuthor Commented:

Thank you for your time Ray.
I will go through the code now...
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.