PHP Search Function with MySQL

Question

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.

You mean %$terms%, right? Surely you wouldn't just write the word terms in a string and expect it to know you wanted to inject a variable... Assuming you did that, you need to use the LIKE operator instead of =. Then you need to worry about SQL injection. Then you need to consider that you might need to combine multiple terms with OR. — paddy
– paddy, Commented Mar 14, 2013 at 2:40
Yes - My bad, was meant to be %$terms%. I'll edit the original — David Jackson
– David Jackson, Commented Mar 14, 2013 at 2:42

Alejandro · Accepted Answer · 2013-03-14 02:43:32Z

1

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."%'";

answered Mar 14, 2013 at 2:43

Alejandro

5822 gold badges7 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

David Jackson Over a year ago

I did attempt this - no luck. Didn't return anything.

Alejandro Over a year ago

type echo $sql; the line after the query, run the query directly in the DB, do you have any result or error there?

David Jackson Over a year ago

It's returning "SQL: Resource id #10"

Alejandro Over a year ago

you echoed the $result variable, not the $sql. Try to running the query directly into phpmyadmin or a mysql client. To be sure that the query is OK.

David Jackson Over a year ago

I echoed $sql, not $result. I've tested the "LIKE" query in phpmyadmin and it doesn't return results unless I put the EXACT product name? I'm running mysql 5.0.67

|

Jordan Schnur · Accepted Answer · 2013-03-14 02:52:57Z

1

Use this:

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

No need to concatenate.

answered Mar 14, 2013 at 2:52

Jordan Schnur

1,3633 gold badges15 silver badges30 bronze badges

Comments

Michael Benjamin · Accepted Answer · 2013-03-14 02:50:20Z

0

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.

answered Mar 14, 2013 at 2:50

Michael Benjamin

2,9251 gold badge18 silver badges18 bronze badges

Comments

user1220713 · Accepted Answer · 2016-09-10 19:56:39Z

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

Collectives™ on Stack Overflow

PHP Search Function with MySQL

4 Answers 4

6 Comments

Comments

Comments

Install

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

Comments

Install

Comments

Your Answer

Sign up or log in

Post as a guest

Related