How to search in PHP in each columns

Question

I want to search in the database from phpmyadmin, but the resulting search is done in the first column only which mean the result shown the data of the first column only

I tried the below code and the results show only the (college) columns, which is the first column.

<!DOCTYPE html>
<html>
<head>
</head>
<body>    
    <form action="college.php" method="post">
        <select name=type>
            <option vlaue="college">college</option>
            <option vlaue="supervisor">supervisor</option>
            <option value="pro_name">prog_name</option>
        </select>
        <label align='right'>search about</label><br>
        <input type="text" name="valueToSearch">
        <label align='right'>search</label><br>
        <input type="submit" name="search" value="search">
   </form><br><br>
<?php
    if(isset($_POST['search'])) {
        $valueToSearch = $_POST['valueToSearch'];
        $query = "SELECT * FROM `programs` WHERE CONCAT(`college`, 
        'supervisor', 'pro_name') LIKE '%".$valueToSearch."%'";
        $search_result = filterTable($query);
    } else {
        $query = "SELECT * FROM `programs`";
        $search_result = filterTable($query);
    }
    // function to connect and execute the query
    function filterTable($query) {
        $connect = mysqli_connect("localhost", "root", "", "IAU");
        $filter_Result = mysqli_query($connect, $query);
        return $filter_Result;
    }
?> 
    <table border="1">
        <caption><b>البرامج</b></caption>
        <tr>
            <th align=right>college</th>
            <th align=right>supervisor</th>
            <th align=right>prog_name</th>
        </tr>
        <?php while($row = mysqli_fetch_array($search_result)):?>
        <tr>
            <td align=right><?php echo $row['college'];?></td>
            <td align=right><?php echo $row['supervisor'];?></td>
            <td align=right><?php echo $row['prog_name'];?></td>
        </tr>
        <?php endwhile;?>
    </table>   
</body>
</html>

the result for the above code is for the first column only while I want the result for each columns

`SELECT * FROM programs WHERE college like '%$valuetosearch%' or supervisor like '%$valuetosearch%'` and so on … no? — Danyal Sandeelo, May 27 '19 at 10:13
Possible duplicate of [When to use single quotes, double quotes, and backticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql) — Dharman, May 27 '19 at 11:58

score -1 · Answer 1 · answered May 27 '19 at 10:33

-1

In your sql

 $query = "SELECT * FROM `programs` WHERE CONCAT(`college`,'supervisor', 'pro_name') LIKE '%".$valueToSearch."%'";

only college is interpreted as field name as it has backticks

Supervisor and pro_name are in single quotes. Put all of those in backticks and it should show result correctly.

 $query = "SELECT * FROM `programs` WHERE CONCAT(`college`,`supervisor`, `pro_name`) LIKE '%".$valueToSearch."%'";

answered May 27 '19 at 10:33

Rajesh Kakkad

186
7

For giving *working solution* who in the world is down voting ? I have tested this code before posting. Have the courtesy to mention the reason for doing so. – Rajesh Kakkad May 27 '19 at 12:11
A. It is a duplicate B. It is not warning about the pitfalls of SQL query concatenation. – Dharman May 28 '19 at 02:40
@Dharman (A) You had said it is 'possible' duplicate.(B) Possible Duplicate link was added AFTER my answer (C) OP is given working code as he wanted to know why his code not working, he didn't want to know pitfalls of concatenation. (D) You failed to provide better code. (E) You should be ashamed of yourself for down-voting and discouraging helping people when you dont have answer yourselves. Shame. – Rajesh Kakkad May 28 '19 at 03:59

How to search in PHP in each columns

1 Answers1