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.

Data search from from multiple tables

Discussion in 'PHP' started by Tuhin1234, May 28, 2016.

  1. #1
    Here is my simple search system[http://108.60.222.225/data/]
    This system search data from a specified table in specified column
    Now i want to search data from another table by select another table .


    [​IMG]


    Here is my code
    Index.php
    <html> 
          <head>
               <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
               <title>Simple search</title>
               <script src="js/jquery.js"></script>
               <script src="js/bootstrap.js"></script>
               <link href="css/bootstrap.css" rel="stylesheet" />
          </head>
          <body>
               <div class="container">
                    <br />
                    <h2 align="center">My simple search system</h2><br />
                    <div class="form-group">
                         <div class="input-group">
                              <span class="input-group-addon">Search</span>
                              <input type="text" name="search_text" id="search_text" placeholder="Search by Customer Name" class="form-control" />
                         </div>
                    </div>
                    <br />
                    <div id="result"></div>
               </div>
          </body>
    </html>
    <script>
    $(document).ready(function(){
          $('#search_text').keyup(function(){
               var txt = $(this).val();
               if(txt != '')
               {
                    $.ajax({
                         url:"fetch.php",
                         method:"post",
                         data:{search:txt},
                         dataType:"text",
                         success:function(data)
                         {
                              $('#result').html(data);
                         }
                    });
               }
               else
               {
                    $('#result').html('');              
               }
          });
    });
    </script>  
    PHP:
    & fetch.php

    <?php 
    $connect = mysqli_connect("localhost", "data", "123456789", "data");
    $output = '';
    $sql = "SELECT * FROM gp_code WHERE name LIKE '%".$_POST["search"]."%'";
    $result = mysqli_query($connect, $sql);
    if(mysqli_num_rows($result) > 0)
    {
          $output .= '<h4 align="center">Search Result</h4>';
          $output .= '<div class="table-responsive">
                              <table class="table table bordered">
                                   <tr>
                                        <th>Song Name</th>
                                        <th>Singer Name</th>
                                        <th>Code</th>
                                        <th>Type</th>
                                        <th>Mobile operator</th> 
                                   </tr>';
          while($row = mysqli_fetch_array($result))
          {
               $output .= '
                    <tr>
                         <td>'.$row["name"].'</td>
                         <td>'.$row["art"].'</td>
                         <td>'.$row["code"].'</td>
                         <td>'.$row["type"].'</td>
                         <td>'.$row["compay"].'</td>    
                    </tr>
               ';
          }
          echo $output;
    }
    else
    {
          echo 'Data Not Found';
    }
    ?>  
    PHP:
    Here is my mysql table image [all table are same just different name like gp_code,bl_code,cl_code,dl_code But column are same like art,code,compay,name,type etc]
    [​IMG]


    So now how to do this
    [​IMG]

     
    Tuhin1234, May 28, 2016 IP
  2. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #2
    I'm a bit confused. What exactly is it you want to search for?
    I get that you currently search for for example a singer, by name, I guess. Do you want to search for a singer (for instance), but also something else, like for instance a specific album? Or what?

    Btw, you are using mysqli_ which is good, but the way you're using it is just a redo of using mysql_, and the query is open to SQL-injection - there is NO validation or check of the value you put into the query, and you're not using a prepared query (which sort of would be the point of using mysqli_ or PDO).

    But, if you just want to search multiple tables for results containing one of the search-words (or mulitple tables with one search word), you just have to loop the search-values.
     
    PoPSiCLe, May 28, 2016 IP
  3. Tuhin1234

    Tuhin1234 Active Member

    Messages:
    178
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    71
    #3
    want to make like this not this
    [​IMG]

    See here http://108.60.222.225/data/
    this script search from a specified table or column . But i want to make like it . My database have 3 table so user can search data from any table . like this picture .
     
    Tuhin1234, May 28, 2016 IP
  4. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #4
    So... just make a input for search, and search all the tables you want to search? I really don't understand the problem?

    Something like this: (example, not actual code)
    
    SELECT
           t1.*,t2.*,t3.*,t4.*
             FROM {$dbprefix}users t1
             LEFT JOIN {$dbprefix}user_settings t2 ON t1.user_id = t2.user_id
             LEFT JOIN {$dbprefix}user_groups t3 ON t1.user_id = t3.user_id
             LEFT JOIN {$dbprefix}groups t4 ON t3.group_id = t4.group_id
             WHERE ((t1.fullname LIKE :searchvalue1 OR t1.loginemail LIKE :searchvalue2 OR t4.name LIKE :searchvalue3)) GROUP BY t1.user_id ORDER BY t1.fullname");
    
    PHP:
    What this does is it searches through a couple of different tables (while joining others to get more information), and if nothing is found, it returns a "nothing found" message.
    To search multiple tables within the same query, however, you'll have to have "hooks" so you can join them together - what those hooks are, I have no idea, but if the database is in any way normalized, you should have common IDs for content in different tables.
     
    PoPSiCLe, May 29, 2016 IP
  5. Tuhin1234

    Tuhin1234 Active Member

    Messages:
    178
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    71
    #5
    See here http://108.60.222.163/data/
    Simply just i want user can change table or column by clicking search by Please see my site
    http://108.60.222.163/data/
     
    Tuhin1234, May 29, 2016 IP
  6. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #6
    You've been told what to do, I really don't understand the problem? Join the tables (or if there is no common link between them, like an ID, run the query once for each table), and search through the relevant columns in each table (make sure you index the columns you're gonna be searching to minimize the time it takes) - there is no need to add a selection for singer, song or album?
     
    PoPSiCLe, May 30, 2016 IP