CODEWITHSUNDEEP
phpmysqlsearch
David Jackson
David Jackson

Reputation: 31

PHP Search Function with MySQL

I have the following code - 

<?php # search.inc
error_reporting(E_ALL); // 
ini_set('display_errors', '1');
include "connect_to_mysql.php";
?>
<?php # search.inc.php

//header file:
include_once ('./includes/header.inc');
?>
<?php

$terms= $_GET['terms'];
echo "You've searched for: " . $terms;

$sql= "SELECT * FROM products WHERE product_name ='Big Tree'"; 
//-run  the query against the mysql query function 
$result=mysql_query($sql);

while ($row = mysql_fetch_array($result)) { // While there are products that match the search criteria, assign their data to a variable and echo their details on a webpage


             $id = $row["id"];
             $product_name = $row["product_name"];
             $price = $row["price"];
             $details = $row["details"];
             $category = $row["category"];
                echo '<br/> <img name="image" src="inventory_images/' . $row['id'] . '.jpg" width="100" height="150"/>';
                echo '<br/>';
                echo '<br/> Product Name: '.$row['product_name'];
                echo '<br/>';
                echo '<br/> <a href="modules/productsdetails.inc.php?id=' . $id . '">Click Here To View Product Page</a>';
                echo '<br/>';
                echo '<br/>';

        }
mysql_close(); //closes the MySQL connection.
?>
<?php
// Include footer file 
include_once ('./includes/footer.inc');
?>

As you can see - I've hard coded it to search for "Big Tree". My question is - How do I code it to search for the users input.

I've tried %$terms% but no results are printed.

Upvotes: 0

Views: 7611

Answers (4)

user1220713
user1220713

Reputation: 25

I've created a function for mysql that parses strings with percentage sorting. You can use it if you like.

It can find almost every combination you search for.

Install

First you need to create a function in mysql. Here is the code for that. I did it in phpmyadmin

DELIMITER //

CREATE FUNCTION search ( search VARCHAR(255), string VARCHAR(255) )
RETURNS FLOAT
BEGIN

   DECLARE searchlength INT;
   DECLARE charcount INT DEFAULT 1;
   DECLARE ch VARCHAR(10); 

   DECLARE a INT DEFAULT 0; 
   DECLARE b INT DEFAULT 1;
   DECLARE c INT DEFAULT 0; 

   SET searchlength = LENGTH(search);

   WHILE charcount <= searchlength DO 
    SET ch = SUBSTRING(search,charcount,1); 

    IF LOCATE(ch,string) > 0 THEN 
      IF LOCATE(ch, string) - 1 = b THEN 
        SET c = c + 1; 
      END IF; 
      SET b = LOCATE(ch, string, b);
      SET a = a + 1; 
    END IF;

    SET charcount = charcount + 1;

   END WHILE;

   IF searchlength = a THEN 
    RETURN (c/30)*100; 
   END IF; 
   RETURN -1; 

END; //

DELIMITER ;

Now you can use this function when you make a mysql call from php like this.

$searchneedle = "Some string you like to search for"

SELECT name,username 
FROM users 
WHERE search(LOWER('$searchneedle'),CONCAT(LOWER(test_cullum))) > -1
ORDER BY search(LOWER('$searchneedle'),CONCAT(LOWER(test_cullum))) DESC

Upvotes: 0

Jordan Schnur
Jordan Schnur

Reputation: 1353

Use this: 

$sql = "SELECT * FROM products WHERE product_name ='$terms'";

No need to concatenate.

Upvotes: 1

Michael Benjamin
Michael Benjamin

Reputation: 2915

If you're using the innodb engine on mysql 5.5 or below you'll have to use 'LIKE' like so:

$sql= "SELECT * FROM products WHERE product_name like '%".$terms."%'";

If you're on mysql 5.6 or you're using a myisam table, you can use MATCH AGAINST if you have a fulltext index on the column you're searching. 

$sql= "SELECT match(product_name) against('".$terms."') as relevance 
FROM products 
WHERE relevance>0 
ORDER BY relevance desc";

Please look into parameterized queries or at the very least use mysql_real_escape_string to sanitize your variables.

Upvotes: 0

Alejandro
Alejandro

Reputation: 573

You have to concatenate in the query the variable you want to search for

$sql= "SELECT * FROM products WHERE product_name ='".$terms."'";

or

$sql= "SELECT * FROM products WHERE product_name LIKE '%".$terms."%'";

Upvotes: 1

Related Questions