List Table A, Search Table B and update Table A with result

Question

for my dissertation I need to categorize images. The image links are stored in table "ocr". In my search page I am showing all the images in rows. Using AJAX I am searching Table "diss_kategorien" and showing the results in a dropdown in the table OCR. Now I need to update table ocr with the results, but I do not know how to pass the variable from the row with the current image which I am processing

This is my search page:

<div class="card-body">
            <table width="95%" class="table table-bordered tablehover">
                <tr>
                    <th width="40">ID</th>
                    <th>Image</th>
                    <th width="200">Hauptgegenstand</th>
                    <th width="130">Save</th>
                    <th width="137">Löschen</th>
                    <th width="137">Update</th>
                </tr>
                <?php
                foreach($result as $r)
                {
                ?>
                <form action="<?php echo $_SERVER['PHP_SELF'];?>"  method="post" enctype="application/x-www-form-urlencoded">
                <tr>
                    <td><input readonly name="id" type="text" class="form-control input-sm" value="<?php echo($r->id); ?>" id="id" /></td>
                    <td width="232"><img style="max-width: 400px" src="uploads/<?php echo($r->file_name);?>"></td>
                    <td>
                         <input type="text" name="search" id="search" autocomplete="off" placeholder="Gegenstand suchen">
                         <div id="output"></div>
                    </td>
                    </div>    
                    <td><input type="submit" class="btn btn-primary" name="submit"></td>
                    <td><input type="submit" class="btn btn-primary" name="delete"></td>
                    <td width="5%"><a href="index.php?ocr_id=<?php echo($r->id);?>">Update</a> </td>
                    
                </tr>
                
                </form>
                <?php }?>
            </table>
        </div>

Here is the AJAX Code:

<script type="text/javascript">
    $(document).ready(function(){
       $("#search").keyup(function(){
          var query = $(this).val();
          if (query != "") {
            $.ajax({
              url: 'ajax-db-search.php',
              method: 'POST',
              data: {query:query},
              success: function(data){
 
                $('#output').html(data);
                $('#output').css('display', 'block');
 
                $("#search").focusout(function(){
                    $('#output').css('display', 'none');
                });
                $("#search").focusin(function(){
                    $('#output').css('display', 'block');
                });
              }
            });
          } else {
          $('#output').css('display', 'none');
        }
      });
    });
  </script>

And here is my ajax-db-show.php

$conn=mysqli_connect($servername,$username,$password,$dbname);
        mysqli_query($conn,"SET CHARACTER SET 'utf8'");
        mysqli_query($conn,"SET SESSION collation_connection ='utf8_unicode_ci'");
    
          if(!$conn){
              die('Could not Connect MySql Server:' .mysql_error());
            }


    if (isset($_POST['query'])) {

    $val = '%' . $_POST['query'] . '%';
    
    $stmt = $conn->prepare("SELECT * FROM diss_kategorien WHERE ebene_1_txt LIKE ? OR ebene_2_txt LIKE ? or ebene_3_txt like ?");
    $stmt->bind_param("sss", $val, $val, $val);
    $stmt->execute();
    $result = $stmt->get_result();

 
  if (mysqli_num_rows($result) > 0) {
     while ($user = mysqli_fetch_array($result)) {
     // echo "Ebene 1: " . $user['ebene_1_txt']. "Ebene 2: " . $user['ebene_2_txt'] . "Ebene 3: ". $user['ebene_3_txt'] ."<br/>";
    print "<td><a href='show_ocr_files_mainitem.php?param1=" . $user['ebene_1_nr'] . "&param2=" . $user['ebene_2_nr'] . "&param3=" .$user['ebene_3_nr']. "&param4=" .$_POST['id']."'>ID: " . $_POST['id'] . " Ebene 1: " . $user['ebene_1_txt']. " Ebene 2: " . $user['ebene_2_txt'] . " Ebene 3: ". $user['ebene_3_txt'] . "<br/></a></td>"; 
    }
  } else {
    echo "<p style='color:red'>Nichts gefunden..</p>";
  }

When I type in a Search Term into field "Hauptgegenstand" I generate an hyperlink which should pass the Variables to the search page to update the table OCR:

print "<td><a href='show_ocr_files_mainitem.php?param1=" . $user['ebene_1_nr'] . "&param2=" . $user['ebene_2_nr'] . "&param3=" .$user['ebene_3_nr']. "&param4=" .$_POST['id']."'>ID: " . $_POST['id'] . " Ebene 1: " . $user['ebene_1_txt']. " Ebene 2: " . $user['ebene_2_txt'] . " Ebene 3: ". $user['ebene_3_txt'] . "<br/></a></td>";

When I use ID of the search result for the update, then it is wrong, because I need the ID of the OCR table

I tried with

data: {query:query, id: <?php echo $r->id; ?>},

But the shown ID is 470 instead of 111. I do not get the ID of the OCR Field where I am doing the Search, I only get the ID of the last entry in the search of the OCR table. I have limited the search to 50 and so the last ID is 470

So I am getting the correct search results, but I am not aware how to pass the ID of the Table OCR to the Ajax file and from there back to the search page to update the table OCR.

Thank you for your help and advice how to solve this.

All the best,

Stefan

this is vulnerable to **sql injection** please use **only** **prepared statements with parameters** see https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — nbk, Nov 27 '20 at 21:56
Hi nbk - I know its not proper, but its on localhost and not online... — Bosstone, Nov 27 '20 at 21:58
when the questions is not deleted someone could find it and probably copy it, so we urge people to do it always right as it costs nothind and is also a good training. Second as we don't have your data we can't help ypu wat we need is a [mre] or run your code directly on the server and s ee what it gets you and change the select till it fits — nbk, Nov 27 '20 at 22:04
Please implement prepared statements immediately and then see if it works. If you are still facing a problem after that then you can come back and update the question with the new code. See https://stackoverflow.com/questions/28385145/correct-way-to-use-like-var-with-prepared-statements-mysqli — Dharman, Nov 27 '20 at 23:28
I have add bind_param but still the wrong ID is passed. I do not get the ID of the OCR Field, I only get the ID of the last entry in the search of the OCR table. I have limited the search to 50 and so the last ID is 470 — Bosstone, Nov 28 '20 at 09:33

score 1 · Answer 1 · answered Nov 28 '20 at 13:50

You cannot use same id for mutiple elements instead use class . So , change id="search" to class="search" and id="output" to class="output" . Then , you can use $(this).closest("tr").find("input[name=id]").val() to get the id of row where you are performing search currently.

Demo Code :

$(document).ready(function() {
  $(".search").keyup(function() {
    var $this = $(this); //for refering `this` inisde success fn
    var selector = $(this).closest("tr"); //get closest tr
    var query = $(this).val();
    var id = $(this).closest("tr").find("input[name=id]").val(); //get id
    console.log("Id -- " + id)
    if (query != "") {
      /*$.ajax({
        url: 'ajax-db-search.php',
        method: 'POST',
        data: {
          query: query,
          id:id
        },
        success: function(data) {*/
      //selector.find(".output").html(data);
      selector.find(".output").html("somethinfsfsfsf"); //just for demo..
      selector.find(".output").css('display', 'block');

      $this.focusout(function() {
        selector.find(".output").css('display', 'none');
      });
      $this.focusin(function() {
        selector.find(".output").css('display', 'block');
      });
      /* }
      });*/
    } else {
      selector.find(".output").css('display', 'none');
    }
  });
});

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<div class="card-body">
  <table width="95%" class="table table-bordered tablehover">
    <tr>
      <th width="40">ID</th>
      <th>Image</th>
      <th width="200">Hauptgegenstand</th>
      <th width="130">Save</th>
      <th width="137">Löschen</th>
      <th width="137">Update</th>
    </tr>


    <form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post" enctype="application/x-www-form-urlencoded">
      <tr>

        <td><input readonly name="id" type="text" class="form-control input-sm" value="1" id="id" /></td>
        <td width="232"><img style="max-width: 400px" src="uploads/<?php echo($r->file_name);?>"></td>
        <td>
          <input type="text" name="search" class="search" autocomplete="off" placeholder="Gegenstand suchen">
          <div class="output"></div>
        </td>

        <td><input type="submit" class="btn btn-primary" name="submit"></td>
        <td><input type="submit" class="btn btn-primary" name="delete"></td>
        <td width="5%"><a href="index.php?ocr_id=<?php echo($r->id);?>">Update</a> </td>

      </tr>
    </form>

    <form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post" enctype="application/x-www-form-urlencoded">
      <tr>

        <td><input readonly name="id" type="text" class="form-control input-sm" value="2" id="id" /></td>
        <td width="232"><img style="max-width: 400px" src="uploads/<?php echo($r->file_name);?>"></td>
        <td>
          <input type="text" name="search" class="search" autocomplete="off" placeholder="Gegenstand suchen">
          <div class="output"></div>
        </td>

        <td><input type="submit" class="btn btn-primary" name="submit"></td>
        <td><input type="submit" class="btn btn-primary" name="delete"></td>
        <td width="5%"><a href="index.php?ocr_id=<?php echo($r->id);?>">Update</a> </td>

      </tr>
    </form>


  </table>
</div>

List Table A, Search Table B and update Table A with result

1 Answers1