how to search a string

[blueguy777]

How to search a string in table i.e. searchstring = "Gandhi Chowk"

actual code:

if(isset($_POST['companyId'])){
    $companyId = $_POST['companyId'];
    }
if(isset($_POST['RouteName'])){
    $routeName = '%'.$_POST['routeName'].'%';
    }   

if(isset($_POST['cityName'])){
    $cityName = $_POST['cityName'];
    }

// We Will prepare SQL Query
    $STM = $dbh->prepare("SELECT routeName FROM route WHERE routeName LIKE :route");
// bind paramenters, Named paramenters alaways start with colon(:)
    $STM->bindParam(':route', $routeName);
// For Executing prepared statement we will use below function
    $STM->execute();
// Count no. of records 
$count = $STM->rowCount();

if($count==1)
    {   

echo "The entered string already exist";

    }
    else
    {
$sql = "INSERT INTO route(companyId, routeName, cityName) VALUES (:companyId, :routeName, :cityName)";

$stmt = $dbh->prepare($sql);

$stmt->bindParam(':companyId', $_POST['companyId'], PDO::PARAM_STR);       
$stmt->bindParam(':routeName', $_POST['routeName'], PDO::PARAM_STR); 
$stmt->bindParam(':cityName', $_POST['cityName'], PDO::PARAM_STR);

$stmt->execute(); 
    header("location:RouteList.php?NewRouteAdded=77083368");    
}

[pritaeas]

What exactly is the problem?

Confusing:

if($count==1)

This means a single record. It will not enter this branch if more than one record exists. That's OK in itself I suppose, but you're entering a LIKE comparitor in your SQL using wildcards before AND after the supplied name. This may return more than one record and thus insert a record. You may think that this will not go past records = 1 anyway when using this code, but it CAN and most likely WILL.

[blueguy777]

it should check the entered string exist or not that's it.

[pritaeas]

I suggest you use:

SELECT routeName FROM route WHERE routeName = :route

and then bind $routeName without % pre- and appended. That way you check only exact matches.

pritaeas +1 - take heed of the advice given above

The problem, blueguy777, is that your search term is pretty limp. Consider the following using your original SQL:

//first submit
$routeName = '%Jo%'; //Jo does not exist so inserted
//second submit
$routeName = '%Joe%'; //Joe does not exist so inserted
//third submit
$routeName = '%Jo%'; //Jo and Joe exist (count == 2) so Jo inserted again

Every time you search for 'Jo', it will continue to insert another record into the table.

[blueguy777]

thanks everyone problem solved