Link to home
Avatar of Peter Kroman
Peter KromanFlag for Denmark

asked on

My SQL query does not work

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: User generated image 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>

Open in new window


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);
  }
  ?>  

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Peter Kroman
Peter Kroman
Flag of Denmark image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial