MySQL PHP searching multilpe fields

Question

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

Gordon Linoff · Accepted Answer · 2013-08-30 21:08:58Z

1

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.

answered Aug 30, 2013 at 21:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

amaster · Accepted Answer · 2013-08-30 21:26:55Z

1

$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?

edited Aug 30, 2013 at 21:26

amaster

2,1536 gold badges28 silver badges55 bronze badges

answered Aug 30, 2013 at 21:09

mahdilamb

5353 silver badges11 bronze badges

Comments

php_drupal_guy · Accepted Answer · 2013-08-30 21:13:47Z

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%'

Collectives™ on Stack Overflow

MySQL PHP searching multilpe fields

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