coding search form question

[Ketchup]

I have a search form that when people type in the exact name of the user it shows results but if they don't add a space inbetween first and last names or they do and that is not the name it won't show results.

Such as John Doe shows results but not Johndoe

Can I change this code below to show results for johndoe as well?

if(isset($_POST['search']))
{
    $searchs = array();
    if(!empty($_POST['contactname']))
    {
        $searchs[]="contactname LIKE '%".$_POST['contactname']."%'";
    }

[JamesM]

if you are pulling data from mysql then you should try search using phpmyadmin first this solves most issue. if you get required output then you can use specific query.

hope this helps.,

tizag[dot]com/mysqlTutorial/mysqlwhere.php

[Tent Pitcher]

Originally Posted by Ketchup

I have a search form that when people type in the exact name of the user it shows results but if they don't add a space inbetween first and last names or they do and that is not the name it won't show results.

Such as John Doe shows results but not Johndoe

Can I change this code below to show results for johndoe as well?

if(isset($_POST['search']))
{
    $searchs = array();
    if(!empty($_POST['contactname']))
    {
        $searchs[]="contactname LIKE '%".$_POST['contactname']."%'";
    }

Without modifying your data structure, you are probably going to want to create a temp table to hold the contact names from the database without spaces:

SELECT id, REPLACE(contactname, ' ', '') AS tmp FROM table WHERE tmp = '%' . $_POST['contactname'] . '%'

Once you have called that query, you can query $_POST['contactname'] with the spaces removed against that instead. The problem is that the code you provided is creating an array of query stubs, so without seeing the full query being called I can't tell you how better to integrate the temp table query. There are more efficient ways to do things, but not without changing the table structure.

[sarettah]

// This assumes that mysql has already been hooked up at the time you construct this

if(isset($_POST['search']))
{
    $searchs = array();
    if(!empty($_POST['contactname']))
    {
        // first for protection against sql injection
        $contact2use='%' . mysql_real_escape_string($_POST['contactname']) . '%';
        // then make a second version to search for
        $compressedcontact=str_replace(' ','',$contact2use);
        // then look for either version 
        $searchs[]="contactname LIKE '" . $contact2use . "' or contactname like '" . $compressedcontact . "'";
    }

[Tent Pitcher]

Originally Posted by sarettah

// This assumes that mysql has already been hooked up at the time you construct this

if(isset($_POST['search']))
{
    $searchs = array();
    if(!empty($_POST['contactname']))
    {
        // first for protection against sql injection
        $contact2use='%' . mysql_real_escape_string($_POST['contactname']) . '%';
        // then make a second version to search for
        $compressedcontact=str_replace(' ','',$contact2use);
        // then look for either version 
        $searchs[]="contactname LIKE '" . $contact2use . "' or contactname like '" . $compressedcontact . "'";
    }

If you go this route prior to running the query, you will probably want to replace the space with a wildcard (%) instead to match an either/or situation. Regardless, you will still need to concatenate the database contact names for instances where the POSTed contact name contains no space.

[sarettah]

Originally Posted by Tent Pitcher

If you go this route prior to running the query, you will probably want to replace the space with a wildcard (%) instead to match an either/or situation. Regardless, you will still need to concatenate the database contact names for instances where the POSTed contact name contains no space.

I am not sure what you are trying to say there.

What I did will match names if they are like what was entered or if they are like what was entered with spaces removed, simple as that.

No need to manipulate the database any further to get at what the OP requested.

Such as John Doe shows results but not Johndoe

Can I change this code below to show results for johndoe as well?

What I wrote will handle that.




I think ;p

[Tent Pitcher]

Originally Posted by sarettah

I am not sure what you are trying to say there.

What I did will match names if they are like what was entered or if they are like what was entered with spaces removed, simple as that.

No need to manipulate the database any further to get at what the OP requested.



What I wrote will handle that.




I think ;p

If the database record for the name is "John Doe", and someone enters "JohnDoe" then a LIKE will not match them. What you did would work if the incoming POST request is for "John Doe" and the database record is either "JohnDoe" or "John Doe", but not if the request is for "JohnDoe" and the database record is "John Doe". So there is nothing wrong with what you said - it will absolutely solve half of the problem. The other half is doing basically exactly what you did on the scripting side, only on the database - which is where I suggested the temp table approach. Although I stand by my disclaimer that there are much better and more efficient ways to do it (the temp table solution that is).

Hope that answers your question.

[sarettah]

Originally Posted by Tent Pitcher

If the database record for the name is "John Doe", and someone enters "JohnDoe" then a LIKE will not match them. What you did would work if the incoming POST request is for "John Doe" and the database record is either "JohnDoe" or "John Doe", but not if the request is for "JohnDoe" and the database record is "John Doe". So there is nothing wrong with what you said - it will absolutely solve half of the problem. The other half is doing basically exactly what you did on the scripting side, only on the database - which is where I suggested the temp table approach. Although I stand by my disclaimer that there are much better and more efficient ways to do it (the temp table solution that is).

Hope that answers your question.

Ok, I see where you were taking it. But there is only so far you should ever have to take it.

For my part, I would never have it stored as a fullname like that anyway. I would have John in a first name field and Doe in a lsst name field. Everything in it's place.

You can do lots of magic with code and a database but you still can't fix stupid, ya know ;p

thnx

[senortriangulo]

Originally Posted by Ketchup

if(isset($_POST['search']))
{
    $searchs = array();
    if(!empty($_POST['contactname']))
    {
        $searchs[]="contactname LIKE '%".$_POST['contactname']."%'";
    }

It looks like your search is probably vulnerable to SQL injections. Are you sanitizing the $_POST at all before this code even runs? If you aren't you could be in for a world of hurt, and you've just let the world know your page is vulnerable to injections.

Check out this StackOverflow post for more on SQL injection attacks:

stackoverflow dot com/questions/60174/best-way-to-prevent-sql-injection

-st

[Tent Pitcher]

Originally Posted by sarettah

Ok, I see where you were taking it. But there is only so far you should ever have to take it.

For my part, I would never have it stored as a fullname like that anyway. I would have John in a first name field and Doe in a lsst name field. Everything in it's place.

You can do lots of magic with code and a database but you still can't fix stupid, ya know ;p

thnx

I agree 100% with everything you said...designing an efficient structure up front will save you a ton of headaches down the line.