PHP basic search engine

Question

I have a search engine that does not work, I want the engine to fetch data from a mySQL database and display them in a table, here is my PHP code..

Thank you!

PHP CODE:

<?php

    <?php

$connect = new mysqli('localhost', 'root', '', 'supermazad') or die(mysql_error());
$connect->select_db('supermazad');

//collect

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

    $query = mysqli_query("SELECT * FROM main WHERE title LIKE '%$searchq%'") or die(mysql_error());
    $count = mysql_num_rows($query) or die(mysql_error());

    if($count == 0){
        $output = 'There was no search results.';
    }
    else{
            while($row = mysql_fetch_array($query)){
                $id = $row['ref'];
                $title = $row['title'];
                $desc = $row['description'];

                    foreach( $id && $title && $desc ){
                        $output = '<table class="results-tab"><tr></tr><tr><td>'. $id .'</td>'. '<td>' . $title . '</td>' .'<td>'. $desc . '</td></tr>'; 
                    }
            }
    }
}
?>

What does "does not work" mean? What does it do? What is it supposed to do instead? — jsfan, Mar 16 '16 at 22:29
you are using `mysqli_query` and then `mysql_num_rows` ... ? How does that make sense? BTW: Use prepared statements or PDO — Steven Dropper, Mar 16 '16 at 22:30
Hey @jsfan .. it is meant to search from the database and display the data in a table. — Khalid Almalki, Mar 16 '16 at 22:34
Hey @StevenDropper .. I am new to PHP & MySQL, thats why I am making mistakes likes these, could you please explain? — Khalid Almalki, Mar 16 '16 at 22:35
`mysqli_query` require two parameters. use `mysqli_num_rows` and `mysqli_error` with `mysqli_` — fusion3k, Mar 16 '16 at 22:41
Your code doesn't make sense. You're mixing MySQL APIs http://stackoverflow.com/questions/17498216/can-i-mix-mysql-apis-in-php and seem to be using CI syntax here `$connect->select_db('supermazad');` and 2x opening ` — Funk Forty Niner, Mar 16 '16 at 23:15
You have answers below. You can ask them. I can't make out what to do here. — Funk Forty Niner, Mar 16 '16 at 23:20

score 1 · Accepted Answer · 2016-03-16T23:06:58.703

**NOTE - THIS IS BASED IN YOU SAID, SIMPLE EXAMPLE **

You are mixing mysqli + mysql
The problem is related with your query. You need to index your fields from table you want.

what you need to do?

ALTER TABLE main ADD INDEX title (product_id);
SELECT * FROM main WHERE title LIKE '%".$searchq."%' OR MATCH(field_1, field_2, field_3, field_4) AGAINST('".$searchq."');

The second query is the example for use full-text (https://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html)

Change the code:

<?php
        // data
        $localhost   = 'localhost'; 
        $username    = 'username';
        $password    = 'passw0rd';
        $database    = 'supermazad';

        mysql_connect($localhost,$username,$password) or die(mysql_error());
        mysql_select_db($database) or die(mysql_error());

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

            $query = mysql_query("SELECT * FROM main WHERE title LIKE '%$searchq%'") or die(mysql_error());
            $count = mysql_num_rows($query) or die(mysql_error());

            if($count == 0){
                $output = 'There was no search results.';
            }else{
                echo '<table class="results-tab">';
                while ($row = mysql_fetch_array($query, MYSQL_ASSOC)) {
                   echo '<tr>
                            <td>'.$row["ref"].'</td>
                            <td>'.$row["title"].'</td>
                            <td>'.$row["description"].'</td>
                        </tr>';
                }
                echo '</table>';    
            }
        }
    ?>

Hello @CristianoPerdigão thank you so much, that worked with a few tweaks made!! — Khalid Almalki, Mar 17 '16 at 01:25
You are welcome @KhalidAlmaki :). Good Luck with your objectives! — , Mar 31 '16 at 20:02

score 1 · Answer 2 · edited Mar 18 '16 at 19:47

Use prepared statements, it's more secure as they prevent SQL injection.

I commented almost every step so you can learn prepared statements.

<?php

$mysqli = new mysqli('localhost', 'root', '', 'supermazad') or die(mysql_error());

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

    $stmt = $mysqli->prepare("SELECT * FROM main WHERE title LIKE ?");
    $stmt->bind_param('s',$searchqrep); //bind parameters to your statement
    $stmt->execute(); //you must execute your statement otherwise no results
    $stmt->bind_result(); //bind results to variables, but you must define them after the "Select ... " SQL
    $stmt->store_result(); //store results in case of further usage of them
    while($stmt->fetch()){ // the while loop will pull out all existing results from your table
        if($stmt->num_rows == 0){
            echo "No search results";   
        }else{
            // Echo/Print the binded variables from statement   
        }
    }
    $stmt->close(); //Still close your prepared statements
}
?>

Hey @StevenDropper , I have found a book for advanced PHP, I will take a look at it, any good reads you recommend ? — Khalid Almalki, Mar 17 '16 at 01:25
@KhalidAlmalki - If you want to code, you need to fully understand what php stands for and what you can do with it. Basically read everything though... — Steven Dropper, Mar 17 '16 at 02:04

PHP basic search engine

2 Answers2