PHP live search not getting the existing row in database

Question

So, I'm making PHP live search with php,jquery and mysql. the data that being search isn't showing in my page despite the data is exist in the database.

rowCount() is showing the correct total of rows, but the page isn't showing all the rows.

here's my php code

<?php
if(isset($_POST['keyword'])){
    $key = $_POST['keyword'];

    $findPerusahaan = "SELECT * FROM perusahaan_instansi WHERE badan_hukum LIKE '%$key%' OR merek LIKE '%$key%' ";
    $stmt = $conn->prepare($findPerusahaan);
    $stmt->execute();
    $data =$stmt->fetch(); 

    foreach($stmt as $row){
        echo $row['badan_hukum']."<br>";
    }
    //print_r($stmt);
    echo $stmt->rowCount();
}

?>

and here it is the page

<input onkeyup="myFunction()" type="text" id="myInput" class="form-control" 
id="perusahaan" name="perusahaan" placeholder="PT....." required>
<div id="perusahaan"></div>


<script>
function myFunction() {
console.log($('#myInput').val());
var keyword = $('#myInput').val();

$.ajax({
        type:'POST',
        url: 'index.php?modul=form_submit&action=perusahaan',
        data: {'keyword': keyword },
        success:function(data){
                console.log(data);
                $('#perusahaan').empty();
                $('#perusahaan').append(data);
        }
    });

   }
   </script>

Here is the result, thanks for any help

**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should really use parameterized prepared statements instead of manually building your queries. They are provided by [PDO](https://php.net/manual/en/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/en/mysqli.quickstart.prepared-statements.php). — CodyKL, Jul 01 '19 at 05:15

CodyKL · Accepted Answer · 2019-07-01T05:26:13.833

1

$data contains your array of rows, not $stmt!

Change this:

$data = $stmt->fetch(); 
foreach($stmt as $row){
    echo $row['badan_hukum']."<br>";
}

to this:

$data = $stmt->fetchAll(); 
foreach($data as $row){
    echo $row['badan_hukum']."<br>";
}

edited Jul 01 '19 at 05:26

answered Jul 01 '19 at 05:14

CodyKL

1,024
6
14

tried it and i got this Warning: Illegal string offset 'badan_hukum' in C:\laragon\www\uas\modul\form_submit\perusahaan.php on line 11 – Idris Akbar Adyusman Jul 01 '19 at 05:23
Whats the output of `var_dump($data)`? – CodyKL Jul 01 '19 at 05:25
I've edited my code, try again. If it's not work, whow the output of `var_dump($data)` – CodyKL Jul 01 '19 at 05:26
But you still have a problem with SQL injection! So change your code and pass (via e.g. `bindValue`) the value of your variable `$key` to the prepared statement instead of using this variable directly in the query! – CodyKL Jul 01 '19 at 05:36

score -1 · Answer 2 · answered Jul 01 '19 at 05:14

-1

Try using '%".$key."%' instead of '%$key%':

<?php
if(isset($_POST['keyword'])){
$key = $_POST['keyword'];

$findPerusahaan = "SELECT * FROM perusahaan_instansi WHERE badan_hukum LIKE '%".$key."%' OR merek LIKE '%".$key."%' ";
$stmt = $conn->prepare($findPerusahaan);
$stmt->execute();
$data =$stmt->fetch(); 

foreach($stmt as $row){
    echo $row['badan_hukum']."<br>";
}
//print_r($stmt);
echo $stmt->rowCount();
}

answered Jul 01 '19 at 05:14

Sumit Advani

69
3

1

Welcome to Stack Overflow! **Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should really use parameterized prepared statements instead of manually building your queries. They are provided by [PDO](https://php.net/manual/en/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/en/mysqli.quickstart.prepared-statements.php). – CodyKL Jul 01 '19 at 05:15
1

Makes no difference! [Read this posting](https://stackoverflow.com/a/3446286/11692015) to get more info about variables within single/double quotes. – CodyKL Jul 01 '19 at 05:18
Doing `'%$key%'` is valid though, since the OP has the query inside double quotes. You're just recommending a different way of doing the same thing. – M. Eriksson Jul 01 '19 at 05:18

PHP live search not getting the existing row in database

2 Answers2