help with searching a database

symoore22

i am trying to search a set of products in a database.
eg:

this is product one
this is product two
this is product three

i cant get the search to work if the search terms are this and one i.e not together in the products table

My question is therefore what can i do to the keywords variable in order to search multiple keywords.

ive found a couple of tutorials on the web but they all seem to be full of features i dont need - can anybody help.

<?php
$keywords = $_POST["keywords"];
?>

<form name="frmKW" action="searchdocs.php" method="post">
<input type="text" name="keywords" maxlength="100">
<input type="submit" name="submit" value="Search">
</form>

<?php
include "db_conn_open.php";
$query = "select * from products where name = '$??????????????'";
$result=mysql_query($query);
$num=mysql_numrows($result);

mysql_close();

$i=0;
while ($i < $num) {
$name=mysql_result($result,$i,"name");
?>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td><?php echo "$name" ?></td>
  </tr>
</table>
<?
$i++;
}

?>

Najjar

If I understand you correctly you need to use Like instead of = as:

$query = "select * from products where name Like '%??????????????%'";

$num=mysql_numrows($result);

Should be:

$num=mysql_num_rows($result);

adbaez

I am new to php, but I am more familiar with SQL. So I'll give you the SQL code, and you enter it into your php code.

select *
from products
where name in x

x = a single quoted, comma delimited list such as: 'smith','jones','taylor'

in addition, a query that returns a single column from multiple records is also useable. If $result would return lastname, for three records, and you know it is the search criteria that you want, you could write this:

select *
from products
where name in ($result)

That may not be correct $result php syntax, but the SQL syntax is correct.

Najjar

I don't know if something like this would help

arr_words = Split(" ", this is product one); // split the value from the text field ($string_variable)

$query = "select * from products where ";
//Then loop the array like this:
for($i=0;$i<count(arr_words);$i++) {
$query = $query . 'name like "%' . arr_words[$i] . '%" and ';
}

Then remove the last extra and from the query
this should generate a complete query.

symoore22

thanks for your replies

boul you try and help me incorporate that into my code - i have given it a go but am struggling a little:

<?php
$keywords = $_POST["keywords"];
?>

<form name="searchform" action="searchdocs.php" method="post">
<input type="text" name="keywords" maxlength="100">
<input type="submit" name="submit" value="Search">
</form>

<?php
include "db_conn_open.php";

$arr_words = Split(" ", $keywords); // split the value from the text field ($string_variable) 
$query = "select * from products where ";
for($i=0;$i<count(arr_words);$i++) { 

$query = $query . 'name like "%' . $arr_words[$i] . '%" and '; 
$result=mysql_query($query);
$num=mysql_num_rows($result);



mysql_close();
$name=mysql_result($result,$i,"name");
?>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td><?php echo "$name" ?></td>
  </tr>
</table>
  <?
$i++;
}

?>

Najjar

$arr_words = Split(" ", $keywords);//split the value from the text field ($string_variable) 
$query = "select * from products where ";
  for($i=0;$i<count(arr_words);$i++) { 
     $query = $query . 'name like "%' . $arr_words[$i] . '%" and '; 
  }

$query = substr($query, 0, strlen($query)-5);
echo $query; // just for testing
$result=mysql_query($query);