MySQL Database Search Strings - Partial words

Question

Is it possible to search for results as partial words?

For example... I am currently looking up 2 fields in a basic form Brand and Name

How do i code the table so that i can search for "Hei" and "Tom Sou" and not have to search for Tomato Soup?

Can someone show me an example?

Current code

<?php
$connect = mysql_connect($dbServer,$dbUsername,$dbPassword);
if(!$connect){
    die('Could not connect: '.mysql_error());
}

?>

<form action="" method="post">  
Brand <input type="text" name="Brand" />Item <input type="text" name="Item" /><input type="submit" value="Submit" />  
</form>  
<?php

$Brand = mysql_real_escape_string($_REQUEST['Brand']);   
$Item = mysql_real_escape_string($_REQUEST['Item']);     

$sql = "SELECT * FROM DB WHERE item_brand LIKE '%".$brand."%' AND item_name LIKE '%".$item."%'"; 
$r_query = mysql_query($sql); 

echo ('<table><tr><td>ID</td><td>Brand</td><td>Item</td></tr>');
    while ($row = mysql_fetch_array($r_query)){  
        echo '<tr><td>' .$row['item_ID'].'</td>';  
        echo '<td>' .$row['item_brand'].'</td>';    
        echo '<td>'.$row['item_name'].'</td></tr>';    
    }  
echo ("</table>");
?>

Your current code has also syntax errors

Mubo
– Mubo

2015-01-02 13:32:32 +00:00
Commented Jan 2, 2015 at 13:32 — Mubo
– Mubo, Commented Jan 2, 2015 at 13:32
mysql_extesion is deprecated, you better not use it all

Mubo
– Mubo

2015-01-02 14:55:17 +00:00
Commented Jan 2, 2015 at 14:55 — Mubo
– Mubo, Commented Jan 2, 2015 at 14:55

Suchit kumar · Accepted Answer · 2015-01-02 13:50:33Z

1

try using this:

$item="Tom Sou";
    $like="'";
    $srtarray=explode(' ',$item);
    foreach($srtarray as $key=>$val){// here you can use implode also 
        $like.="%$val%";
    }
    $like.="'";

$sql = "SELECT * FROM DB WHERE item_brand LIKE '%".$brand."%' AND item_name LIKE  $like";

edited Jan 2, 2015 at 13:50

answered Jan 2, 2015 at 13:45

Suchit kumar

11.9k3 gold badges26 silver badges46 bronze badges

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

Comments

Tacticus · Accepted Answer · 2015-01-02 13:23:29Z

0

You could search for whitespaces in the search word and replace them with % signs, so "Tom Sou" gets replaced with Tom%Sou, which in the whole query would lead to ... WHERE item_name LIKE '%Tom%Sou%' ....

Note that depending on your database solution you need to do a case-insensitive select / Like.

EDIT : Please make sure to use PDO or mysqli instead of the deprecated mysql module, as it may not be as secure as the newer soultions and will finally get removed from PHP. Both PDO and mysqli support new database mechanisms like prepared statements, while (at least mysqli) still providing similiar functions.

answered Jan 2, 2015 at 13:23

Tacticus

57111 silver badges25 bronze badges

Comments

Mubo · Accepted Answer · 2015-01-02 14:52:49Z

I have modified the code according to your needs as i think: This should work for you

<?php
$mysqli = mysqli_connect("localhost", "root", "Password", "DatabaseName");

if (mysqli_connect_errno($mysqli)) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
?>

<form action="" method="post">
    Brand <input type="text" name="Brand" />
    Item <input type="text" name="Item" /><br />
         <input type="submit"  name="submit" value="submit" /></br>
</form>
<?php

if( $_POST['submit']){
//remove tags from user inputs
$Brand  =       strip_tags('%'.$_POST['Brand'].'%');    
$Item   =       strip_tags('%'.$_POST['Item'].'%'); 

//escape mysql injection 
$Brand = $mysqli->real_escape_string($Brand);
$Item = $mysqli->real_escape_string($Item);


$query = "SELECT * FROM  DB WHERE  item_brand LIKE '$Brand' AND  item_name LIKE '$Item'";
$res = mysqli_query($mysqli, $query);

if($res === FALSE) { 
    die(mysqli_error()); // TODO: better error handling
}
$row = mysqli_fetch_array($res);


echo '<table><tr><td>ID</td><td>Brand</td><td>Item</td></tr>';
        echo '<tr><td>' .$row['id'].'</td>';  
        echo '<td>' .$row['item_brand'].'</td>';    
        echo '<td>'.$row['item_name'].'</td></tr>';    

echo '</table>';


}

Collectives™ on Stack Overflow

MySQL Database Search Strings - Partial words

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related