troubleshooting Question

My SQL query does not work

Avatar of Peter Kroman
Peter KromanFlag for Denmark asked on
JavaScriptPHPSQL
1 Comment1 Solution118 ViewsLast Modified:
Hi,

I have this page:
http://kroweb.dk/gfdev/godser/
and my trouble is, that when I try to perform a search nothing happens at all and I get no error messages either.

I have tried to create a query directly in the db editor like this: Sk-rmbillede-2018-01-13-10.47.18.png and it works nicely and returns the expected results.

I am connected to the right db (have doubble checked several times :) ), and I simply can't see why this is not working.

I have this main file:
<?php
session_start();
error_reporting(E_ALL);
ini_set('display_errors', 1);
?>

<!DOCTYPE html>
<html lang="da">
<head>
  <!-- Check for title--> 
  <title>Godser</title>

  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css" integrity="sha512-dTfge/zgoMYpP7QbHy4gWMEGsbsdZeCXz7irItjcC3sPUFtf0kuFbDz/ixG7ArTxmDjLXDmezHubeNikyKGVyQ==" crossorigin="anonymous">
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/dt-1.10.16/datatables.min.css" />
  <link rel="stylesheet" type="text/css" href="style_stednavne.css" />

  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
  <script type="text/javascript" src="https://cdn.datatables.net/v/dt/dt-1.10.16/datatables.min.js"></script>
  <script type="text/javascript">
  $(document).ready(function() {
    $('#tdata').dataTable( {
      responsive:true,

    } );
  } );
  </script>
  

</head>

<body>


  <!-- Page content start-->        
  <div class="w3-row">


    <!-- Main page start -->  

    <div class="w3-row">

      <!-- Column1 start-->         
      <div class="w3-col w3-mobile" style="width:50%; margin-top: 30px; margin-left: 20px; margin-bottom:10px; margin-right: 8px; padding-bottom: 10px;">

        <!-- Form that holds the Sogn to the link clicked and the Amt to limit the search - not used at this moment --> 
        <form id="search" style="display:none" target="_blank" method="post" action="sognefakta_ByLand.php">
          <input type="text" name="sogn" id="sogn">
          <input type="text" name="amt" id="amt">
        </form>

        <!-- Radio buttons to choose broad/exact search -->

        <form style="color:#9F5584; padding-bottom:7px;"> 

          <input type="radio" id="broad" style="position:relative; margin-left: 0px; background-color: #eee;" checked="checked" name="radio1" value="bredt" /> Søg bredt 
          <input type="radio" id="exact" style="position: relative;" name="radio1" value="eksakt"/> Søg eksakt Gods
          <input type="radio" id="exacts" style="position: relative;" name="radio1" value="eksakts"/> Søg eksakt Gods

        </form> 


        <!-- Searchfield for Estates -->
        <form action=""  form title="Skriv evt. bare en del af det du søger, eller brug % som jokertegn når du søger bredt. Hvis du søger eksakt skal du skrive det eksakte navn på det du søger" style="padding-top:10px; padding-bottom:10px;" method="POST" >

         <input type="search" id="input_1" placeholder= "Søg" style="width: 300px; height: 35px; border: 1px solid #666666;border-radius:2px;color:#666666; color: #9F5584; font-weight: ligther; font-size: 13px; padding-left:10px;" name="query"  />

         <input type="button" submit title="Klik her, eller tast Enter, for at aktivere søgningen." style="margin-left:15px; padding-right:10px;height:30px;font-family:arial; font-size: 12px; text-align:left; background-color:#cccccc; border: 2px solid #grey;border-radius:2px; color:black; background-color:#cccccc; box-shadow: 0 8px 16px 0 rgba(0,0,0,0.2), 0 6px 20px 0 rgba(0,0,0,0.19);" name="soeg" id="Sog" value="Søg"/>


         <input type="submit" id="nulstil" style="margin-left:15px; height:30px;font-family:arial; font-size: 12px; text-align:left; background-color:#cccccc; border: 2px solid #grey;border-radius:2px; color:black; background-color:#cccccc; box-shadow: 0 8px 16px 0 rgba(0,0,0,0.2), 0 6px 20px 0 rgba(0,0,0,0.19);" value="Nulstil"/>
       </form>  
       

       <div id="list_items"></div>


       <div class="w3-row" style="width:100%;">         
        <br> 
        <hr style= "margin-left: 10px; width: 95%;  border-color: #9F5584;">   
      </div>    

      <!-- Column1, end-->


      <script>
      function radioBtn(){
       var linkPhp;
       if($("#broad").is(":checked")) {
         linkPhp='ajaxListHtml.php';        
         return linkPhp;
       }else if($("#exact").is(":checked")){
         linkPhp='ajaxListHtml2.php';         
         return linkPhp;  
       }else{        
        linkPhp='ajaxListHtml3.php';         
        return linkPhp;  }
      }    


      // "Søg" button function
      SogButton.addEventListener('click',function (){
        $('#list_items > table').remove();    
        $.ajax({
         url: radioBtn(),
         data: {query:input_1.value},
         method:'POST',
         dataType: 'html'
       }).done(function (res){             
        $('#list_items').html(res);
        $('#tdata').DataTable({searching: true, paging: false, info: true, "dom": '<"top"<if>', order: []});
        
      });         

     });


        // Return key search function
        
          input_1.value=this.value;  
          if(e.keyCode===13 && scopeVar!=='GOOGLE'){            
           $.ajax({
             url: radioBtn(),
             data: {query:input_1.value},
             method:'POST',
             dataType: 'html'
           }).done(function (res){
            $('#list_items > table').remove();
            $('#list_items').html(res);
            $('#tdata').DataTable({searching: true, paging: false, info: true, "dom": '<"top"<if>', order: []});
            
          });
         }
    

        //"Reset" button
        NustilButton.addEventListener('click',function (){
         $('#list_items > table').remove();
         

       });

        </script>


      </div>
      <!-- Main page end--> 

    </div>  
  </div>
  <!-- Page content end-->  

  </html>

and I have this ajax file:
       <?php

       error_reporting(E_ALL);
       ini_set('display_errors', 1);
       
       if ((isset($_POST['query'])) && (trim($_POST['query'])!="")) {
        $con=mysqli_connect("db credentials") or die("Error connecting to database: ".mysql_error());

        mysqli_select_db($con,"navnpaasted_dk_db2");
        /* the name of database we'are working in */
        mysqli_set_charset($con,"utf8");
    // This is an example of config.php
        ?> 

        <?php
        $query = trim($_POST['query']); 
    //print "query=".$query;
    // gets value sent over search form    

        $min_length = 2;
    // you can set minimum length of the query if you want

    if(strlen($query) >= $min_length){ // if query length is more or equal minimum length then

      $query = htmlspecialchars($query,ENT_QUOTES,"utf-8"); 
        // changes characters used in html to their equivalents, for example: < to &gt;     

      $query = mysqli_real_escape_string($con, $query);
        // makes sure nobody uses SQL injection


      $raw_results = mysqli_query($con, "SELECT * FROM godser
        WHERE (`Gods` LIKE '%".$query."%') OR (`Sogn`='$query') OR(`Amt`='$query') OR (`Herred`='$query') ORDER BY Amt ASC, Herred ASC, Sogn ASC, Gods ASC ") or die(mysql_error());


var_dump($query);

        if(mysqli_num_rows($raw_results) > 0){ ?>




        <table id="tdata" class="dataTable">
          <thead>
           <tr>
            <th class="sorting" aria-controls="tdata">Gods</th>
            <th class="sorting" aria-controls="tdata">Sogn</th>
            <th class="sorting" aria-controls="tdata">Herred</th>
            <th class="sorting" aria-controls="tdata">Amt</th>
            <th >Hovedlink</th>
            <th >Historie</th>
          </tr>
        </thead>

         

        <?php while($row = $result=mysqli_fetch_array($raw_results)): ?>
        <tr>
          <td><?php echo $row["Gods"] ?></td>
          <td><?php printf("<a href='%s' target='_blank'>%s</a>", $row['URL'], $row['Sogn']); ?></td>
          <td><?php echo $row["Herred"] ?></td>
          <td><?php echo $row["Amt"] ?></td>
          <td><?php echo $row["Hovedlink"] ?></td>
          <td><?php echo $row["Historie"] ?></td>
        </tr>
      <?php endwhile; ?>
    </table>


    <?php
        }else{ // if there is no matching rows do following

          echo "Ingen resultater for denne søgning";
        }         
    }else{ // if query length is less than minimum

      echo "Minimum antal tegn er ".$min_length;
    }

    mysqli_close($con);
  }
  ?>  
ASKER CERTIFIED SOLUTION
Peter Kroman
Sales Executive

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 1 Comment.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros