CODEWITHSUNDEEP
mysqlsearch
Richard_Bedford
Richard_Bedford

Reputation: 11

MySQL PHP searching multilpe fields

I have a search box on my website which works well, but I want it to search more than one field in the table. For instance, I have 3 sections in the table for text labeled text1, text2 and text3. The following code works for seaching a single field, but it's not looking in all 3.

        $sqlcommand = "SELECT id, page_title, url, text1, text2, text3 FROM pages WHERE text1 LIKE '%$searchquery%'";

I've tried changing it to; 

        $sqlcommand = "SELECT id, page_title, url, text1, text2, text3 FROM pages WHERE text1, text2, text3 LIKE '%$searchquery%'";

But that doesn't produce a result, and I've tried;

        $sqlcommand = "SELECT id, page_title, url, text1, text2, text3 FROM pages WHERE (text1, text2, text3) LIKE '%$searchquery%'";

which someone else suggested, but that produced an error.

Help gratefully received.

Richard

Upvotes: 0

Views: 100

Answers (3)

mahdilamb
mahdilamb

Reputation: 535

$sqlcommand = "
    SELECT id, page_title, url, text1, text2, text3 
    FROM pages 
    WHERE text1 LIKE '%$searchquery%' OR text2 LIKE '%$searchquery%' OR text3 LIKE '%$searchquery%'
";

^Does that work?

Upvotes: 1

php_drupal_guy
php_drupal_guy

Reputation: 1

To search multiple columns in your table, make sure that you have all columns referenced in the WHERE clause, separated by the operator AND, as follows:

select id, page_title, url, text1, text2, text3
FROM pages
WHERE   text1 LIKE '%searchfield1%'
    AND text2 LIKE '%searchfield2%'
    AND text3 LIKE '%searchfield3%'

of course, you'll want to place this query inside your php formatted script, probably as:

$sqlcommand = ".................."; like you had in your question.

Alternatively, if you are searching all three columns on the same exact input field, you could use:

select id, page_title, url, text1, text2, text3
FROM pages
WHERE   text1 LIKE '%searchquery%'
    AND text2 LIKE '%searchquery%'
    AND text3 LIKE '%searchquery%'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You can try concat'ing them together:

    $sqlcommand = "SELECT id, page_title, url, text1, text2, text3
                   FROM pages
                   WHERE concat(text1, ':', text2, ':', text3) LIKE '%$searchquery%'";

I've also included a delimiter, so the search query should only match one field.

Upvotes: 1

Related Questions