CODEWITHSUNDEEP
php
NewbieLearner
NewbieLearner

Reputation: 85

php Msql search multiple columns

Hello I got the below code from Youtube tutorial and it works if I search for a string that only exists in 1 column. If I combine search for something that is in two different columns then I get no results. For example if Jack is in column 1 and Rabbit in column 2, If I search for "Jack Rabbit" then I get no results at all, If I search for "Jack" then it works, same for "Rabbit".

I know the fix is in the line below but when I changed the line according to other posts here on stackoverflow that I searched for, I got errors because their code was a bit different than mine.

$query = mysql_query("SELECT * FROM myTable WHERE columnOne LIKE '%$searchq%' OR columnTwo LIKE '%$searchq%'  ") or die("Could not search!");

Whole search code below

<?php
mysql_connect("localhost", "Name_Name", "Password") or die("Could not connect");
mysql_select_db("Name_Databse") or die("Could not find db!");
$output = ' ' ;
//collect

if(isset($_POST['search'])) {
        $searchq = $_POST['search'];
        $searchq = preg_replace("#[^0-9a-z]#i", " ", $searchq);

        $query = mysql_query("SELECT * FROM myTable WHERE columnOne LIKE '%$searchq%' OR columnTwo LIKE '%$searchq%'  ") or die("Could not search!");
        $count = mysql_num_rows($query);
        if($count == 0) {
        $output = 'There was no such results!';

        }else{
            while($row = mysql_fetch_array($query)){
            $id = $row['id'];
            $aHref = $row['aHref'];
            $columnOne = $row['columnOne'];
            $columnTwo = $row['columnTwo'];
            $inputDiv = $row['inputDiv'];
            $image = $row['image'];


$output .= '<a href="' . $aHref . '.html" class="link"> <div class="Poster">' .  ' <div class="columnOne">' . $columnOne . ' </div> <div class="columnTwo">' . $columnTwo . ' </div>    <div class="inputDiv">' . $inputDiv . ' </div>  <div class="image"><img src="' . $image . '.jpg"/>' . '</div> </div></a>';


            }
    }   

}


?>

<!DOCTYPE html>
<html>
<head>              
        <title> Search </title>
        <link rel='stylesheet' type='text/css' href='Styler.css'/>

</head>
<body>



                    <form action='search.php' method='post' style="margin: 0 0 25px;">
                    <input type='text' name='search' size='50' placeholder="Search here"/>
                    <input type='submit' value='Search' />
                    </form>

                        <?php print("$output"); ?> 





</script>               
</body>
</html>

Upvotes: 0

Views: 86

Answers (1)

Lys777
Lys777

Reputation: 456

How about combining both columns and searching that as well:

SELECT * FROM myTable WHERE columnOne LIKE '%$searchq%' OR columnTwo LIKE '%$searchq%' OR CONCAT_WS(' ', columnOne, columnTwo) LIKE  '%$searchq%'

This would only work though if they search for "columnOne + [space] + columnTwo". If you want to be able to match ANY word they enter (i.e., searching for "Jack W. Rabbit" still matches), then you would use the exploding method as previously suggested.

Upvotes: 3

Related Questions