Jason Jason - 24 days ago 8
MySQL Question

Simple search function return always 0 results

I have this php code

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

$likeString = '%' . $_POST['search'] . '%';
$query = $conn->prepare("SELECT * FROM images WHERE image_caption LIKE ?");
$query->bind_param('s', $likeString);
$query->execute();

var_dump($likeString);
if (!$query) {
printf("Query failed: %s\n", $mysqli->error);
exit;
}
if($res->num_rows > 0) {
while ($row = $res->fetch_assoc()) {
echo "<br>Title: " . $row['image_caption'];
}
} else {
echo " <br> 0 results";
}

}


var_dump($likeString)
shows the word which I've posted via search form correctly. Also I've tried in phpmyadmin directly to run this query


SELECT *
FROM images
WHERE image_caption LIKE "%Volvo%"


And I've received 1 result which is correct. On page I see
0 results
. Tried to play with fetch:

$res->fetch_assoc()
$res->fetchAll()
$res->fetch()


none of them show any result. I'm sure is something very silly and simple mistake but can't see it. Please help on this.

I don't have
Call to a member function bind_param() on a non-object
It was my mistake while I've made proposed changes from one of the answer. Problem still remains - 0 Results

UPDATE: Current code

$likeString = "%{$_POST['search']}%";
$query = $conn->prepare("SELECT * FROM images WHERE image_caption LIKE ? ");
$query->bind_param('s', $likeString);
$query->execute();

if($query->num_rows > 0) {
while ($row = $query->fetch()) {
echo "<br>Title: " . $row['image_caption'];
}
} else {
echo " <br> 0 results";
}

}


UPDATE 2: DB connection checked-> result is
Connected successfully


$servername = "localhost";
$username = "mydbUsername"; // it's changed for the question
$password = "myPass"; // it's changed for the question
$dbname = "myDbName"; // it's changed for the question

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";

Answer

try this : (update your code)

$likeString= "%{$_POST['search']}%";
$stmt = $db->prepare("SELECT * FROM images WHERE image_caption LIKE ?");
$stmt->bind_param('s', $likeString);
$stmt->execute();

$result = $stmt->get_result();
    while ($row = $result->fetch_array(MYSQLI_NUM))
    {
        foreach ($row as $r)
        {
           echo "<br>Title: " . $r['image_caption'];                
        }
        print "\n";
    }

OR

<?php
  $conn = new mysqli("localhost","mydbUsername","myPass","myDbName");
  /* check connection */
  if ($conn->connect_errno) { 
    printf("Connect failed: %s\n", $conn->connect_error); 
    exit(); 
  }

  $query = "SELECT * FROM images WHERE image_caption LIKE %".$_POST['search']."%";

  if ($result = $conn->query($query)) {

    /* fetch associative array */
    while ($row = $result->fetch_assoc()) {
        echo "<br> Title: ". $row["image_caption"]);
    }print "\n";

    /* free result set */
    $result->free();
  }

  /* close connection */
  $conn->close();
?>