PHP Search fetch where data exist in fields

Question

I am trying to create a search, anyways my site is:

mysite.com/?p=search&t=[SEARCH VARIABLE]

So for in my case if my url is that I want it to search through my table of posts and give me the results where [SEARCH VARIABLE] exists in the table. Mainly I want it to check these columns: title, content and author. If there is any [Search variable] in there I want it to show up.

Attempt that works [with only 1 column] but does not give me all the results.

<?php
if (isset($_GET['t']))
{
    $stmt = $dbh->prepare('SELECT * FROM posts WHERE `title` like :t');
    echo ' <h1>Search results for: '.$_GET['t'].'</h1> ';
    if (!$stmt->execute(array(':t' => $_GET['t'])))
    {
        exit('Could not exec query with param: '.$_GET['t']);
    }

    while($row = $stmt->fetch(PDO::FETCH_ASSOC))
    {
        echo '<font size="4pt">'.$row["title"].'</font><br>
                        ';

    }
    }
 ?>

My attempt the same as the first one but checks the content field. Does not work either.

 <?php
    if (isset($_GET['t']))
    {
        $stmt = $dbh->prepare('SELECT * FROM posts WHERE `title` like :t AND `content` like :t;');
        echo ' <h1>Search results for: '.$_GET['t'].'</h1> ';
        if (!$stmt->execute(array(':t' => $_GET['t'])))
        {
            exit('Could not exec query with param: '.$_GET['t']);
        }

        while($row = $stmt->fetch(PDO::FETCH_ASSOC))
        {
            echo '<font size="4pt">'.$row["title"].'</font><br>
                            ';
        }
    }
  ?>

With the first one, it only shows one field with the title of it. But I want it to show anything with the word in it. For example if you search for "hey' and have "hey theres apples", i want it to show that hey theres apples.

EDIT:

<?php
if (isset($_GET['t']))
{

    $stmt = $dbh->prepare('SELECT * FROM posts WHERE `title` like %:t%');

    echo ' <h1>Search results for: '.$_GET['t'].'</h1> ';

    while($row = $stmt->fetch(PDO::FETCH_ASSOC))
    {
        echo '<font size="4pt">'.$row["title"].'</font><br>
                        ';

                            }
                            }

                    ?>

Still needing help.

@яша There is no error, its just not showing. With the first one, it only shows one field with the title of it. But I want it to show anything with the word in it. For example if you search for "hey' and have "hey theres apples", i want it to show that hey theres apples. — user3124460, Dec 21 '13 at 02:53
The problem is your lack of wildcards for the `LIKE` query. Without the wildcards, it’s just a literal `this` equals `that` query. — Giacomo1968, Dec 21 '13 at 04:26

Giacomo1968 · Answer 1 · 2013-12-21T04:25:26.343

The problem is you are not placing LIKE wildcards (%) around the search terms. Without wildcards, the query will look for the literal terms in your params which is not really what you’d want to use LIKE for.

So a statement like this:

$stmt->execute(array(':t' => $_GET['t'])

Would be this instead:

$stmt->execute(array(':t' => '"%'.$_GET['t'].'%"')

EDIT Perhaps doing it this way would be better. Leave your code as the original question, ignore the changes above and adjust your statements like this:

    $stmt = $dbh->prepare('SELECT * FROM posts WHERE `title` like %:t%');

Also for your combined statement do a similar changed and perhaps change that AND to an OR:

    $stmt = $dbh->prepare('SELECT * FROM posts WHERE `title` like %:t% OR `content` like %:t%;');

But somehow you need those wildcards in place to get the results you are implying by the question.

Im afraid this did not work. – user3124460 Dec 21 '13 at 02:43 — user3124460, Dec 21 '13 at 02:43
See my new edit. But the issue is lack of wildcards. – Giacomo1968 Dec 21 '13 at 04:27 — Giacomo1968, Dec 21 '13 at 04:27

PHP Search fetch where data exist in fields

1 Answers1