PHP script to search MySQL database

Question

I have a script that is supposed to return values from a mysql tables based on search inputs. This script is composed of two files.

search.php

<?php
if ( isset( $_GET['s'])) {
require_once( dirname( __FILE__ ) . '/class-search.php' );
$search = new search();
$search_term = $GET['s'];
$search_results = $search->search($search_term);
}
?>
<!DOCTYPE html>
<html>
<head>
    <title>Search</title>
</head>
<body>
    <h1>Search</h1>
    <div class="search-form">
        <form action="" method="get">
            <div class="form-field">
                <label for="search-field">Search</label>
                <input type="search" name="s" placeholder="Search by name" results="5" value="<?php echo $search_term; ?>">
                <input type="submit" value="Search">
            </div>
        </form>
    </div>
    <?php if ( $search_results ) : ?>
    <div class="results-count">
        <p><?php echo $search_results['count']; ?> results found</p>
    </div>
    <div class="results-table">
        <?php foreach ( $search_results['results'] as $search_result ) : ?>
        <div class="result">
            <p><?php echo $search_result->title; ?></p>
        </div>
        <?php endforeach; ?>
    </div>
    <div class="search-raw">
        <pre><?php print_r($search_results); ?></pre>
    </div>
    <?php endif; ?>
</body>

and class-search.php

<?php
class search {

private $mysqli;
public function __construct() {
    $this->connect();
}
private function connect() {
    $this->mysqli = new mysqli('HOST', 'USERNAME', 'PASSWORD', 'DATABASE' );
}
public function search($search_term) {
    $sanitized = $this->mysqli->query("
    SELECT * FROM `Apple`
    FROM search
    WHERE Last_Name LIKE '%{$sanitized}%'
    ");
    if ( ! $query->num_rows ) {
        return false;
    }
    while( $row = $query->fetch_object() ) {
        $rows[] = $row;
    }
    $search_results = array(
    'count' => $query->num_rows,
    'results' => $rows,
    );
    return $search_results;
}
}
?>

Within my database I have two tables, but I'm only interested in searching the content of one (Apple). Can somebody help me? I can't seem to make this work. No results are returned no matter what I search. As of now I'm only using the Last_Name criteria, but I'd like to add others. Here's a link to the screenshot of my table https://i.stack.imgur.com/A9Ejd.jpg.

I'd really appreciate any feedback possible. Thank you.

WHERE Last_Name LIKE '%{$sanitized}%' OR First_Name LIKE '%{$sanitized}%' OR ... -- but this is not right way. You should use this: https://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html — Deep, Nov 06 '16 at 08:06
Thank you. I've correct the issue but still have the same problem — Tj Keel, Nov 06 '16 at 08:27
(Make the main problem `No results are returned` more prominent - you might want to mention it near the top of your post, as not all readers bother to scroll, and have an explicit (&specific) question at the bottom of a question post long enough for the title to have disappeared when the tags come into view. Time allowing, peruse [How do I ask a good question?](http://stackoverflow.com/help/how-to-ask).) — greybeard, Nov 06 '16 at 09:47

harigorana · Answer 1 · 2016-11-06T08:37:10.333

0

If you check it again,

In search method you're passing $search_term as argument but in the query you're using $sanitized which doesn't exists until the query is executed.

You're result set is in $sanitized but you're checking $query for num_rows which don't even exists. Also, you're returning false in that method so you're not able to identify the actual problem.

public function search($search_term) {
    $sanitized = $this->mysqli->query("
    SELECT * FROM `Apple`
    FROM search
    WHERE Last_Name LIKE '%{$search_term}%'
    ");
    if ( ! $sanitized->num_rows ) {
        //return false;
        retrun [];
    }
    $rows  = [];
    while( $row = $sanitized->fetch_object() ) {
        $rows[] = $row;
    }
    $search_results = array(
    'count' => $query->num_rows,
    'results' => $rows,
    );
    return $search_results;
}

In connect method, add this which will tell whether its getting connected to database or not.

if ($this->mysqli->connect_errno) {
    printf("Connect failed: %s\n", $this->mysqli->connect_error);
    exit();
}

edited Nov 06 '16 at 08:37

answered Nov 06 '16 at 08:10

harigorana

102
1
6

Thank you. I have made the change but still have the same result. My intuition is that I'm not connecting the mysql correctly. – Tj Keel Nov 06 '16 at 08:19
@TjKeel Do you get any error?? Also, i've updated the answer – harigorana Nov 06 '16 at 08:31
@TjKeel after looking carefully there are lot of issues with `search` method. check the updated answer – harigorana Nov 06 '16 at 08:37
I've updated it. I still do not get any results. I added the connection test and it did not change the result, so I think that means I'm connected correctly. – Tj Keel Nov 07 '16 at 18:24
I've confirmed that the connection is successful. Next I should try to see if it is pulling results correctly? Could I do this by adding a "no results" script that displays if no matches were found? How would I do this? Thank you – Tj Keel Nov 07 '16 at 20:50
Sorry to bother you again, but I have another update. I added this script: ' 0){ echo implode($results); } else { echo "No results were found."; } ?>' And found that I'm simply not getting results for anything i searc, even if I search exactly as the value appears in my table. – Tj Keel Nov 08 '16 at 03:55

PHP script to search MySQL database

1 Answers1