How to make a search page using PHP and mysql?

Question

I have been trying to make a website to sell books where I have a mysql database of books and their authors. I wish to add a search bar on the main page (where the list of all books is printed using basic PHP and mysql).

I am still not sure about how to go about it, so here is what I intent to do:

1. Connect to database
2. Take a search query using POST method and make action to, let's say, search.php, for example, let's say the entry was "social change"
3. In search.php, use explode() to break query sentences into words/keywords and make an array of it. So the query becomes an array with [0] => 'social' and [1] => 'change'
4. In my existing database, let's say I have an entry of a book called "dynamics of social change". I make another column in my database that has an array of (dynamics, of, social, change) corresponding to that book. (((HOW to do it using mysql?))))
5. Use SQL Queries to SELECT the entry from WHERE the book-name array contains any of the elements of my keyword array.
6. Print, if any result found.

Here is what I have done: From index.php

    <form action= 'search.php' method='post'>
        <input type='text' name='q'>                        
        <input type='submit'>
    </form>

From search.php, after connecting to database:

$q = $_POST["q"];
$keys = explode(" ", $q);

$x = 0;
echo "<table>";
while ($x < sizeof($keys))
{
    $req = "SELECT * FROM book WHERE name= '$keys[$x]'";
    $run = mysqli_query($connect,$req)
        or die('Query req failed: ' . mysql_error());
    while 
    ($line = mysqli_fetch_array($run, MYSQL_ASSOC))
    {
        echo "<tr>";
        echo "<td>" . $line['name'] . "</td>";
        echo "</tr>";

    } 
$x = $x + 1;
}
echo "</table>";

The problem I am facing is that I am not getting any output. I tried different inputs but I can not figure out the mistake.

What I want to ask: 1. Am I doing it correct logically? Considering the fact that I am a newbie in PHP and mysql, is it logically correct way to do a search? If no, can you suggest any simple/basic way for this simple web project?

2. If what I intend to do is right, can you help me figure out where am I making mistake?

3. I am yet to find out the HOW part in step 4, i.e., making another column in mysql database consisting of an array with elements that are basically words split from book titles. For example, Column A: "Dynamics of social change" Column B: (Dynamics, of, social, change)

Answer 1

You need to use like, not =s. The = requires a match. The like is for something like the value. For example if a database contained firetruck and you did

select * from table where column = 'fire'

You would get no results. However if you did

select * from table where column like '%fire%'

You will get the firetruck row.

The %s allow for anything on that side so for example:

select * from table where column like '%fire'

would not find firetruck because it would only find records that end with fire.

You'll probably want to look at full text searching though. http://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html

You also should never pass user input directly into a SQL query. Use prepared statements.

http://php.net/manual/en/mysqli.quickstart.prepared-statements.php
https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet#Defense_Option_1:_Prepared_Statements_.28Parameterized_Queries.29
How can I prevent SQL injection in PHP?

Answer 2

Maybe you should use a Full-Text index for this problem: http://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html

Create the full-text index:

ALTER TABLE book ADD FULLTEXT(name);

Do the query with:

"SELECT * FROM book WHERE MATCH(name) AGAINST ('" . $_POST["q"] . "')"

EDIT: As mentioned in another answer, you should never use direct user input in the queries. All data coming from user inputs should be sanitized, and all queries to the database should be done thru preparad statements.