Solved

Match and count user inputs against Mysql fields

Posted on 2010-08-14
16
336 Views
Last Modified: 2013-12-13
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

0
Comment
Question by:tony_stockman
16 Comments
 
LVL 2

Expert Comment

by:Tekati68
ID: 33439385
It's really not making much sense. Can you show an example of what you are looking for.
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 33439508
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
 

Author Comment

by:tony_stockman
ID: 33439780
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
 

Author Comment

by:tony_stockman
ID: 33472396
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 33480767
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
 

Author Comment

by:tony_stockman
ID: 33482602

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 108

Expert Comment

by:Ray Paseur
ID: 33485191
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
 

Author Comment

by:tony_stockman
ID: 33499170

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
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 33501342
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
 

Author Comment

by:tony_stockman
ID: 33505890

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

Author Comment

by:tony_stockman
ID: 33505895

Thank you for your time Ray.
I will go through the code now...
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34358734
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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now