PHP/MySQL Search Functionality

Question

I've followed this tutorial thoroughly, and had success with it. However, it leads me to a dead end where I have to pay to access the tutorial on implementing a "Search" functionality to it.

I was wondering if anyone could help me out on how I can implement a search functionality given how the tutorial is constructed? Tutorials seems like they don't work with this.

I've tried this on a separate page so far, but it doesn't seem to work - also I don't know how it can be implemented on my actual site.

<?php
mysql_connect("localhost","root","") or die("could not connect");
mysql_select_db("kkp") 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 personal_info WHERE FirstName LIKE '%$searchq%' OR SurName LIKE '%$searchq%'") or die("could not search");
    $count = mysql_num_rows($query);
    if($count == 0){
        $output = 'There was no search results!';
    }else{
        while($row = mysql_fetch_array($query)){
            $fname = $row['FirstName'];
            $lname = $row['SurName'];
            $id = $row['id'];

            $output .= '<div>'.$fname.''.$lname.'</div>';
        }
    }
}
?>
<!DOCTYPE html>
<html>
<head>
    <title>Search</title>
</head>
<body>
    <form method="post" action="search.php"></form>
        <input type="text" name="search" placeholder="Search for student">
        <input type="submit" value="Submit">
</body>
</html>

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

Also, here's where I'm trying to implement a search functionality. It just displays a table with the entries I've added to the database.

<div class="row">
    <h3>List of Student Volunteers</h3>
</div>
<div class="row">
    <table class="table table-striped table-bordered">
    <thead>
        <tr>
            <td>Name</td>
            <td>Email Address</td>
            <td>Mobile Number</td>
            <td>Action</th>
        </tr>
    </thead>
    <tbody>
    <?php
        include 'database.php';
        $pdo = Database::connect();
        $sql = 'SELECT * FROM personal_info ORDER BY id DESC';
        foreach ($pdo->query($sql) as $row) {
            echo '<tr>';
            echo '<td>'. $row['FirstName'] . '</td>';
            echo '<td>'. $row['EmailAddress'] . '</td>';
            echo '<td>'. $row['MobileNum'] . '</td>';
            echo '<td width=250>';
            echo '<a class="btn" href="read.php?id='.$row['id'].'">Read</a>';
            echo ' ';
            echo '<a class="btn" href="update.php?id='.$row['id'].'">Update</a>';
            echo ' ';
            echo '<a class="btn" href="delete.php?id='.$row['id'].'">Delete</a>';
            echo '</td>';
            echo '</tr>';
        }
        Database::disconnect();
    ?>

Post codes of your setup-> what you are trying, .. and where you are stuck please.. — yardie
– yardie, Commented Mar 2, 2016 at 16:31
mysql_connect ... - find a newer tutorial, one that's not using deprecated/obsolete functions and ridding the code with SQL injection vulnerabilities. You probably want to start with pdo : php.net/manual/en/book.pdo.php and, for search functionality in MySQL, FULLTEXT indexes : dev.mysql.com/doc/refman/5.7/en/fulltext-natural-language.html — CD001
– CD001, Commented Mar 2, 2016 at 16:36
yeah, wasn't using that anyway. I'm trying to implement a search functionality on the second snippet. — nitan
– nitan, Commented Mar 2, 2016 at 16:40
Ah - gotcha :) I'd still take a look at FULLTEXT indexes and MATCH ... AGAINST with MySQL - the search functionality will be way, way quicker than anything you try with LIKE '% ... %' .... though you may hit the odd issue with stopwords — CD001
– CD001, Commented Mar 2, 2016 at 16:42

Kirk Beard · Accepted Answer · 2016-03-02 17:13:45Z

2

Your HTML is incorrect. You're closing your <form> tag too early, so the <input> is not submitted to the PHP file.

For example, change your HTML to this:

<form method="post" action="search.php">
    <input type="text" name="search" placeholder="Search for student">
    <input type="submit" value="Submit">
</form>

To test and debug what data is being submitted to the PHP file, you can use print_r($_POST) to view all the details.

For example, based on your code, add:

<?php
mysql_connect("localhost","root","") or die("could not connect");
mysql_select_db("kkp") or die("could not find db!");
$output ='';
print_r($_POST);
// ...

Based on the code you posted, you should see an empty array. This would have helped you identify the issue.

Edit: Expanding on search functionality...

Your SQL already has the LIKE '%$searchq%' parts, which will filter your SQL results. You just need to update your other SQL query to do something similar:

$sql = 'SELECT * FROM personal_info ORDER BY id DESC';

Should be like:

$sql = "SELECT * FROM personal_info WHERE FirstName LIKE '%$searchq%' OR LastName LIKE '%$searchq%' ORDER BY id DESC";

You just need to customise the SQL to add the appropriate LIKE entries.

edited Mar 2, 2016 at 17:13

answered Mar 2, 2016 at 16:36

Kirk Beard

9,89312 gold badges45 silver badges48 bronze badges

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

2 Comments

nitan Over a year ago

oh damn -.- I feel dumb. Thanks! I've updated the post, is there a way I can implement this to what I have right now as my main? -- or should I redo everything?

Kirk Beard Over a year ago

@nitan Updated my example. You already have some search functionality in there, but you need to add it to your $pdo->query($sql) SQL query as well.

Collectives™ on Stack Overflow

PHP/MySQL Search Functionality

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related