I am very new to php & mysql. i have made a webste with a database in mysql. i have developed a search engine to search my database.

I just discovered a weakness in my current search engine. It won't show results if I don't have an exact match
or

if I use two or more words that appear in different fields of the table.

For example, search term 'John' appears in field[wname] and 'canada' appears in field[country]. But when I type 'john canada' into my search form I get no results - unless I only use one or the other in the search form. It also does not work if I type in any extra words whether they are in the table or not.

I need to search multiple fields without having exact matches

if you can help me i would be very greateful to you. i am a librarian working in Saudi Arabia. i will send my seach script to you here. if you can look in to it and give me some instruction i will appreciate.

thank you in advance

this is my script.

<?php
$link = mysql_connect('localhost', 'root', 'vipula');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db('test1');

@mysql_select_db(test1) or die("unable to connect");

$choice=$_POST['choice'];
$name=$_POST['name'];

$query="SELECT * FROM staffpub WHERE";
$query.=" authors LIKE '%$name%'";
$query.=" OR title LIKE '%$name%'";
$query.=" OR source LIKE '%$name%' ORDER BY title";
$result=mysql_db_query("test1", $query);
$num_rows = mysql_num_rows($result);
if ($num_rows==0){$search_result="<p class='error'>No Results Found</p>";}
elseif ($num_rows > 0){$search_result="<p class='error'>".$num_rows. " Results Found</p>";}


mysql_close();

echo '<b><center><font size="4" color="#FF0000">Search Result</font></center></b><br><br>';

$i=0;
while ($i < $num_rows) {

$authors=mysql_result($result,$i,"authors");
$title=mysql_result($result,$i,"title");
$source=mysql_result($result,$i,"source");





echo "<b>Authors:</b> $authors<br><b>Title:</b> $title<br><b>Source:</b> $source<br><br><hr><br>";

$i++;
}

?>

You can use explode to create an array from $name (users input) like so:

[PHP]$name = $_POST['name'];
$name_arr = explode(' ', $name);[/PHP]

then create a seperate query for each search term in your array:

[PHP]foreach($name_arr as $key => $name)
{
$query="SELECT * FROM staffpub WHERE";
$query.=" authors LIKE '%$name%'";
$query.=" OR title LIKE '%$name%'";
$query.=" OR source LIKE '%$name%' ORDER BY title";
$result[$key]=mysql_db_query("test1", $query);
$num_rows[$key] = mysql_num_rows($result);
}[/PHP]

Now you have an array of results and the corresponding num_rows in 2 arrays.
I suppose you could still search for the user input as one string, or as combinations of terms(if there is more than two words input) to get more valid results if they exist. There are many ways you can proceed from this point. Make sure that you check out www.php.net if you have not already. Also the function reference is VERY useful.

Dear Dance Instructor

I tried this but it is not working. when i try to search it is hanging and not responding. is there any other easy way to do this. I am a beginner to PHP. if you can give me the complete script to display my results also I would greatly appreciate. i will send you the scrpt which i tried. if there is anything wron please rectify it. thank you very much for your greate help.

<?php
$link = mysql_connect('localhost', 'root', 'vipula');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db('test1');

@mysql_select_db(test1) or die("unable to connect");

$name = $_POST['name'];
$name_arr = explode(' ', $name);


foreach($name_arr as $key => $name)
{
$query="SELECT * FROM staffpub WHERE";
$query.=" authors LIKE '%$name%'";
$query.=" OR title LIKE '%$name%'";
$query.=" OR source LIKE '%$name%' ORDER BY title";
$result[$key]=mysql_db_query("test1", $query);
$num_rows[$key] = mysql_num_rows($result);
}

if ($num_rows[$key]==0){$search_result="<p class='error'>No Results Found</p>";}
elseif ($num_rows[$key] > 0){$search_result="<p class='error'>".$num_rows. " Results Found</p>";}


mysql_close();

echo '<b><center><font size="4" color="#FF0000">Search Result</font></center></b><br><br>';

$i=0;
while ($i < $num_rows) {

$authors=mysql_result($result,$i,"authors");
$title=mysql_result($result,$i,"title");
$source=mysql_result($result,$i,"source");





echo "<b>Authors:</b> $authors<br><b>Title:</b> $title<br><b>Source:</b> $source<br><br><hr><br>";

$i++;
}

?>

Vipula

No that won't work, I don't have time now but I will reply in 6 hours or so with something that does

The following should work. I did not post a complete solution the first time. I posted an idea expecting you to fill in the rest. At any rate you have something to work with now. The search is not really good as it does not take into account results that contain more than one search term, meaning if an entry has 2 search terms in it, that entry will be listed twice. Anyway I hope this helps.

[PHP]<?php
$link = mysql_connect('localhost', 'root', 'vipula');
if (!$link)
{
die('Could not connect: ' . mysql_error());
}

@mysql_select_db(test1) or die("unable to select database");

$name = $_POST['name'];
$name_arr = explode(' ', $name);
$search_result = '';

foreach($name_arr as $key => $name)
{
$query="SELECT * FROM staffpub WHERE";
$query.=" authors LIKE '%$name%'";
$query.=" OR title LIKE '%$name%'";
$query.=" OR source LIKE '%$name%' ORDER BY title";
$result_arr[$key]=mysql_db_query("test1", $query);
$num_rows_arr[$key] = mysql_num_rows($result_arr[$key]);
$search_result.= "Found $num_rows_arr[$key] results for the term $name.<br />";
}

mysql_close();

echo($search_result);

echo '<b><center><font size="4" color="#FF0000">Search Result</font></center></b><br><br>';

foreach($result_arr as $key => $result)
{
$i=0;
while ($i < $num_rows_arr[$key])
{

$row = mysql_fetch_row($result);
$search_term = $name_arr[$key];
$authors = $row[1];
$title = $row[2];
$source = $row[3];

echo "<b>Search Term:</b> $search_term<br><b>Authors:</b> $authors<br><b>Title:</b> $title<br><b>Source:</b> $source<br><br><hr><br>";

$i++;
}
}

?>[/PHP]

One more thing:
Where I have
[PHP]$authors = $row[1];
$title = $row[2];
$source = $row[3];[/PHP]
If you have an ID field in staffpub the above is fine. If you only have 3 columns in staffpub you will need them to be defined this way:
[PHP]$authors = $row[0];
$title = $row[1];
$source = $row[2];[/PHP]

Dear Sir

I tried this. When i search for "vipula php" i get an error messege like this

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in c:\inetpub\wwwroot\vipula\search_staffpub.php on line 74

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in c:\inetpub\wwwroot\vipula\search_staffpub.php on line 74
Found results for the term vipula.
Found results for the term php.

Search Result


line no 74 is $num_rows_arr[$key] = mysql_num_rows($result_arr[$key]);

please can you look at this again and see what is the error.

Thank you so much for spending your valuable time for me

Dear Sir

You are greate!! I am very hapy. It is working. but I have a small problem. When i search for " terry bouchard" my result will display all the articles contain both words like below. It display the same article twice.one for the word "terry" and one for the word "bouchard". If i add more terms to my search it will increase the number of results. actually i want to search only for the articles contain both words. not all the articles like below. it shows 8 results. but it is actually only 4 articles. I need to serch for articles contain all the search terms only. i want to narrow down my search result.I hope u can understand my problem

thank you very much in advance

Found 4 results for the term terry.
Found 4 results for the term bouchard.

Search Result


Search Term: terry
Authors: Terry Bouchard
Title: Make a website for your library
Source: Health Library Review


--------------------------------------------------------------------------------

Search Term: terry
Authors: Terry Bouchard
Title: Needs assessment and utilization determinants of continuing medical education for primary health care physicians in Al Madinah Al Munawarah province
Source: SMJ


--------------------------------------------------------------------------------

Search Term: terry
Authors: Terry Bouchard
Title: Organization of information using a database driven website
Source: Health Library Review


--------------------------------------------------------------------------------

Search Term: terry
Authors: Terry Bouchard
Title: Womens health and human rights : the promotions and protection of women' he
Source: Health Library Review


--------------------------------------------------------------------------------

Search Term: bouchard
Authors: Terry Bouchard
Title: Make a website for your library
Source: Health Library Review


--------------------------------------------------------------------------------

Search Term: bouchard
Authors: Terry Bouchard
Title: Needs assessment and utilization determinants of continuing medical education for primary health care physicians in Al Madinah Al Munawarah province
Source: SMJ


--------------------------------------------------------------------------------

Search Term: bouchard
Authors: Terry Bouchard
Title: Organization of information using a database driven website
Source: Health Library Review


--------------------------------------------------------------------------------

Search Term: bouchard
Authors: Terry Bouchard
Title: Womens health and human rights : the promotions and protection of women' he
Source: Health Library Review

In that case I think you will need a new query. The problem though is you can't know what the query will be if you don't know how many search terms will be entered. SQL queries are not my strong point. I suggest you make a post in the MySQL forum. I'm sure someone there will have an idea for making a query that works for you.

Good luck