1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Php mysql search engine showing all results instead of specific

Discussion in 'PHP' started by sash_007, Nov 4, 2019.

  1. #1
    hello friends,

    I am trying to build a php mysql search engine following a tutorial
    but somehow when i search for specific name its displaying all results
    whats wrong here ?

    here is my code
    
    <?php
    
    require "connect.inc.php";
    global $conn;
      $search_name = $_POST["search_name"];
    if(isset($search_name)){
      
           if(!empty($search_name)){
            
              $query = "SELECT name FROM names WHERE name LIKE '%" .mysqli_real_escape_string($search_name). "%'";
              $query_run = mysqli_query($conn,$query);
              if(mysqli_num_rows($query_run)>=1){
                  echo "Results found" ."<br>";
                  while($row = mysqli_fetch_assoc($query_run)) {
                  echo "id: " . $row["id"]. " - Name: " . $row["name"]. "<br>";
                }
              }else{
                   echo "no results found";
              }
           }
           
         }
    
    ?>
    <form action ="index.php" method ="POST">
    Name:<input type ="text" name="search_name"><input type="submit" value = "Search">
    
    </form>
    PHP:
    and here is the connect.inc.php

    
    
    <?php
    $conn_error = "could not connect";
    $mysql_host= "localhost";
    $mysql_user = "root";
    $mysql_pass ="";
    $mysql_db ="a_database";
    
    $conn = mysqli_connect($mysql_host,$mysql_user,$mysql_pass,$mysql_db);
    
    /*if(!mysqli_connect($mysql_host,$mysql_user,$mysql_pass) && !mysqli_select_db($mysql_db)){
        die($conn_error);
    }
    */
    if(!$conn){
         die("Connection failed: ". mysqli_connect_error());
       
    }
    
    ?>
    
    PHP:
    i am using wampserver to test the code
     
    sash_007, Nov 4, 2019 IP
  2. hdewantara

    hdewantara Well-Known Member

    Messages:
    536
    Likes Received:
    47
    Best Answers:
    25
    Trophy Points:
    155
    #2
    Hi, sadly neither do I :oops:
    Any errors reported in your server log?
    Also, as you use id (in your code line #15) maybe you should code line #10 as:
     $query = "SELECT `id`, `name` FROM ...
    Code (markup):
     
    hdewantara, Nov 4, 2019 IP
  3. sash_007

    sash_007 Well-Known Member

    Messages:
    174
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    125
    #3
    that didnt work
    here is the modified code
    
    <?php
    
    require "connect.inc.php";
    global $conn;
      $search_name = $_POST["search_name"];
    if(isset($search_name)){
       
           if(!empty($search_name)){
             
              $query = "SELECT `name` FROM `names` WHERE `name` LIKE '%" .mysqli_real_escape_string($search_name). "%'";
              $query_run = mysqli_query($conn,$query);
              $query_num_rows = mysqli_num_rows($query_run);
              if($query_num_rows>=1){
                  echo $query_num_rows . " Results found" ."<br>";
                  while($row = mysqli_fetch_assoc($query_run)) {
                  echo "id: " . $row["id"]. " - Name: " . $row["name"]. "<br>";
                }
              }else{
                   echo "no results found";
              }
           }
            
         }
    
    ?>
    <form action ="index.php" method ="POST">
    Name:<input type ="text" name="search_name"><input type="submit" value = "Search">
    
    </form>
    PHP:
     
    sash_007, Nov 5, 2019 IP
  4. sarahk

    sarahk iTamer Staff

    Messages:
    28,494
    Likes Received:
    4,457
    Best Answers:
    123
    Trophy Points:
    665
    #4
    There's a few things wrong with the tutorial you're using but this will give you a good opportunity to learn some debugging skills.
    Here's an updated version.
    
    <?php
    
    require "connect.inc.php";
    global $conn;
    //$search_name = $_POST["search_name"];
    $search_name = filter_input(INPUT_POST, 'search_name', FILTER_SANITIZE_SPECIAL_CHARS);
    // does it hold the value that you entered?
    var_export($search_name);
    
    if(!empty($search_name)){
    
       $query = "SELECT `id`, `name` FROM `names` WHERE `name` LIKE '%?%'";
       //$query_run = mysqli_query($conn,$query);
    
       $stmt = $conn->prepare($query);
       $stmt->bind_param("s", $search_name);
       $stmt->execute();
    
       //$query_num_rows = mysqli_num_rows($query_run);
       
       $result = $stmt->get_result();
    
       $query_num_rows = $result->num_rows;
    
       if($query_num_rows>=1){
          echo $query_num_rows . " Results found<br>";
          //while($row = mysqli_fetch_assoc($query_run)) {
          while($row = $result->fetch_assoc()) {
             echo "id: {$row["id"]} - Name: {$row["name"]}<br>";
          }
       }
       else{
          echo "no results found";
       }
    }
    
    }
    
    ?>
    <form action ="/index.php" method ="POST">
    Name:<input type ="text" name="search_name"><input type="submit" value = "Search">
    
    </form>
    Code (php):
     
    sarahk, Nov 5, 2019 IP
  5. mike30

    mike30 Well-Known Member

    Messages:
    883
    Likes Received:
    34
    Best Answers:
    0
    Trophy Points:
    140
    #5
    I would suggest to use PDO class to do your mysql queries.
    Here below is a PDO class, ready to use. At the bottom of the script, I put some queries as examples, modify them to suite your needs.
    <?php
    //error_reporting( E_ALL ); // uncomment this to show errors on the browser
    
    // Database connection
    define ( 'DB_HOST', 'localhost' );
    define ( 'DB_USER', 'root' );
    define ( 'DB_PASSWORD', 'password' );
    define ( 'DB', 'databaseName' );
    
    class database
    {
        // Properties
        public $isConn;
        protected $datab; 
        function __construct($username=DB_USER, $password=DB_PASSWORD, $host = DB_HOST, $dbname=DB, $options=[]){
            $this->isConn = true;
            try {
                $this->datab= new PDO("mysql:host={$host};dbname={$dbname};charset=utf8",$username,$password,$options);
                $this->datab->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
                $this->datab->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
            } catch (PDOException $e) {
                throw new Exeption($e->getMessage());
            }
        } 
         ////////////////////////////////////////////////////////////
            //
            public function Disconnect()
                {
                //
                //
                $this->datab = null;
                $this->isConn = false;
                }
         ////////////////////////////////////// END of:Disconnect()
         ////////////////////////////////////////////////////////////
                    // To get a single row
                    public function getRow($query,$params = [])
                        {
                        //
                        //
                            try {
                                $stmt = $this->datab->prepare($query);
                                $stmt->execute($params);
    
                                return $stmt->fetch();
                            } catch (PDOException $e) {
                                throw new Exception($e->getMessage());
                             
                            }
                     
                        }
                 ////////////////////////////////////// END of:getRow()
                    // To get multiple rows
                    public function getRows($query,$params = [])
                        {
                        //
                        //
                            try {
                                $stmt = $this->datab->prepare($query);
                                $stmt->execute($params);
                                return $stmt->fetchALL();
                            } catch (PDOException $e) {
                                throw new Exception($e->getMessage());
                             
                            }
                     
                        }
                 ////////////////////////////////////// END of:getRows()
                 
                 ////////////////////////////////////////////////////////////
                    // General Comments about the Method/Function
                    public function insertRow($query,$params=[])
                        {
                        //
                        //
                        try {
                                $stmt = $this->datab->prepare($query);
                                $stmt->execute($params);
                                return true;
                            } catch (PDOException $e) {
                                throw new Exception($e->getMessage());
                             
                            }
                        }
                 ////////////////////////////////////// END of:insertRow()
                 ////////////////////////////////////////////////////////////
                    // update
                    public function updateRow($query,$params=[])
                        {
                        try {
                                $stmt = $this->datab->prepare($query);
                                $stmt->execute($params);
                                return true;
                            } catch (PDOException $e) {
                                throw new Exception($e->getMessage());
                             
                            }
                        }
                 ////////////////////////////////////// END of:updateRow()
                 ////////////////////////////////////////////////////////////
                    // delete
                    public function deleteRow($query,$params= [])
                        {
                        try {
                                $stmt = $this->datab->prepare($query);
                                $stmt->execute($params);
                                return true;
                            } catch (PDOException $e) {
                                throw new Exception($e->getMessage());
                             
                            }
                        }
                 ////////////////////////////////////// END of:deleteRow()
    }// end of db class
    
    
    ////////// USAGE:
    //$database = new database(); <<< type this in your script to create a new instance of the database/
    
    // Query to get a single row:
    //$getRow = $database->getRow("SELECT name FROM ".DB.".names_table where col_name = ?" LIMIT 1, ["%".$searchTerm."%"]);
    
    
    // Query to get an array of rows:
    //$getRows = $database->getRows("SELECT name FROM ".DB.".names_table where col_name LIKE ?", ["%".$searchTerm."%"]);
    
    
    // query to update a row
    //$update_query = $database->updateRow("UPDATE `".DB."`.`tableName` SET col_one =? WHERE col_name =? LIMIT 1",[$colOneValue,$colNameValue]);
    
    $db->Disconnect();// disconnect your database.
    ?>
    Code (markup):
     
    mike30, Nov 5, 2019 IP
    sarahk likes this.
  6. Andrii Ozemko

    Andrii Ozemko Member

    Messages:
    79
    Likes Received:
    11
    Best Answers:
    1
    Trophy Points:
    33
    #6
    Your sql query looks good. Should work. I would suggest to use some MySQL profiler to catch your SQL query. First of all, we need to make sure your request was executed. Then you can try to execute the same request from command line and see results.
     
    Andrii Ozemko, Nov 6, 2019 IP
  7. sash_007

    sash_007 Well-Known Member

    Messages:
    174
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    125
    #7
    thanks for the help

    but i got it resolved

    i changed this line
    from this
    $query = "SELECT name FROM names WHERE name LIKE '%" .mysqli_real_escape_string($search_name). "%'";
    PHP:
    to this
    $query = "SELECT `name` FROM `names` WHERE `name` LIKE '%" .mysqli_real_escape_string($conn,$search_name). "%'";
    PHP:
    and everything works fine now
    thanks everyone for help
     
    Last edited: Nov 6, 2019
    sash_007, Nov 6, 2019 IP
  8. sarahk

    sarahk iTamer Staff

    Messages:
    28,494
    Likes Received:
    4,457
    Best Answers:
    123
    Trophy Points:
    665
    #8
    Aaah, all the database languages I've used dislike it when you use command names as column and table names. The `` come in handy!
     
    sarahk, Nov 6, 2019 IP
    mike30 and JEET like this.
  9. JEET

    JEET Notable Member

    Messages:
    3,825
    Likes Received:
    502
    Best Answers:
    19
    Trophy Points:
    265
    #9
    Please put your database functions in a separate function or a class.
    Don't use these functions directly in the script.

    For example your usage of mysqli_real_escape_string function.
    Use it like this:

    function myEscape( $value ){
    global $conn;
    // you can do some other things here
    $value= strip_tags( trim( $value ));
    $value= mysqli_real_escape_string ( $conn, $value );
    return $value;
    }//function ends
    Code (php):

    Then in your script, do this:
    $search_term= myEscape( $_POST["search_term"] );

    In your query
    $sql=" select * from tableName where columnName like '%$search_term%' ";
     
    Last edited by a moderator: Nov 9, 2019
    JEET, Nov 9, 2019 IP
  10. deathshadow

    deathshadow Acclaimed Member

    Messages:
    9,732
    Likes Received:
    1,998
    Best Answers:
    253
    Trophy Points:
    515
    #10
    Good gravy! When was said tutorial written? 2003?

    global $conn;
    Code (markup):
    Keeping the connection in global scope is a security risk... but since you do the require on the line before this, it must already be in scope since you don't declare it as global in your include!

    
     $search_name = $_POST["search_name"];
    if(isset($search_name)){
    Code (markup):
    Extra variable for NOTHING, and if you check if it is set AFTER you make the copy, it's going to bomb with a fatal variable accesss on the first of these two lines when you try to copy it for nothing.

    
           if(!empty($search_name)){
    
    Code (markup):
    No reason to isset if you're just going to !empty

    
      $query = "SELECT name FROM names WHERE name LIKE '%" .mysqli_real_escape_string($search_name). "%'";
     
    Code (markup):
    See how you're trying to put that variable into the query string? This isn't 2003 stop doing that. It's part of why the outdated mysql_ functions were deprecated and why one would use mysqli or PDO on the first place -- prepare/execute.

    Even though mysqli's prepare/execute can be a pain in the arse compared to PDO... which is why I'd suggest learning PDO instead.

    
              $query_run = mysqli_query($conn,$query);
    
    Code (markup):
    Man up and learn to use the object version of that.

    
              if(mysqli_num_rows($query_run)>=1){
                  echo "Results found" ."<br>";
                  while($row = mysqli_fetch_assoc($query_run)) {
    
    Code (markup):
    if/do/while would likey be more efficient.

                  echo "id: " . $row["id"]. " - Name: " . $row["name"]. "<br>";
    Code (markup):
    String addition and double quotes are slow... if you're going to add the overhead of double quotes use them for whta they're for. Since it's a flat echo, instead of string additon with "." comma delimit them.

    Hence a better version would read:

    
    if (!empty($_POST['search_name']) {
    	
    	$stmt = $conn->prepare('
    		SELECT id, name
    		FROM names
    		WHERE name LIKE ?
    	');
    	$stmt->bindParam('s', '%' . $_POST['search_name'] . '%');
    	$stmt->execute();
    	$stmt->bind_param($id, $name);
    	
    	if ($row = $stmt->fetch()) {
    		echo 'Results found<br>';
    		do {
    			echo 'id: ', $id, ' - Name: ', $name, '<br>';
    		while ($row = $stmt->fetch());
    	} else echo 'No results found<br>';
    	
    }
    
    
    Code (markup):
    if you're dicking around with realEscapeString style methodology or putting variables into the query string, you're doing it all wrong with decade and a half out of date methodologies.
     
    deathshadow, Nov 9, 2019 IP
    JEET likes this.
  11. deathshadow

    deathshadow Acclaimed Member

    Messages:
    9,732
    Likes Received:
    1,998
    Best Answers:
    253
    Trophy Points:
    515
    #11
    Actually my bad, I'm a pdo guy, not a mysqli guy. Wherever you see:

    ($row = $stmt->fetch())

    replace with just

    ($stmt->fetch())

    Because there is no reason to store the value as fetch just plugs into the bound result variables, instead of returning a row. This bit of strangeness is one of the many reasons why I prefer PDO over mysqli.
     
    deathshadow, Nov 9, 2019 IP
  12. NetStar

    NetStar Notable Member

    Messages:
    2,471
    Likes Received:
    541
    Best Answers:
    21
    Trophy Points:
    245
    #12
    Why on earth would you suggest doing this?
     
    NetStar, Dec 5, 2019 IP
  13. deathshadow

    deathshadow Acclaimed Member

    Messages:
    9,732
    Likes Received:
    1,998
    Best Answers:
    253
    Trophy Points:
    515
    #13
    Could you be more specific? Other than my derpy including $row= because I'm more of a PDO guy, what specifically are you objecting to?

    Make the query string, bind the parameters, execute, bind the results, if there are results output the message it was found, then do/while the remainder.

    Though as mentioned, it should actually read:

    if (!empty($_POST['search_name']) {
    	
    	$stmt = $conn->prepare('
    		SELECT id, name
    		FROM names
    		WHERE name LIKE ?
    	');
    	$stmt->bind_param('s', '%' . $_POST['search_name'] . '%');
    	$stmt->execute();
    	$stmt->bind_result($id, $name);
    	
    	if ($stmt->fetch()) {
    		echo 'Results found<br>';
    		do {
    			echo 'id: ', $id, ' - Name: ', $name, '<br>';
    		} while ($stmt->fetch());
    	} else echo 'No results found<br>';
    	
    }
    Code (markup):
    Again, not a mysqli guy, so I goofed pretty bad in the original post. It's cleaner to do in PDO.

    Why get something like the results count involved if you don't have to? Just pull the first one, if it succeeds send the message then do/while. Not rocket science.
     
    deathshadow, Dec 5, 2019 IP