How to With MySQLi Make a Query With Like and Get All Results

How can I with mysqli make a query with LIKE and get all results?

Here's how you properly fetch the result

$param = "%{$_POST['user']}%";
$stmt = $db->prepare("SELECT id,username FROM users WHERE username LIKE ?");
$stmt->bind_param("s", $param);
$stmt->execute();
$stmt->bind_result($id,$username);

while ($stmt->fetch()) {
echo "Id: {$id}, Username: {$username}";
}

or you can also do:

$param = "%{$_POST['user']}%";
$stmt = $db->prepare("SELECT id, username FROM users WHERE username LIKE ?");
$stmt->bind_param("s", $param);
$stmt->execute();

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
echo "Id: {$row['id']}, Username: {$row['username']}";
}

I hope you realise I got the answer directly from the manual here and here, which is where you should've gone first.

How to get mysqli results from database where the WHERE clause searches for records that have part of it, not necessarily whole of it

$query = "SELECT * FROM user_records WHERE Mtr LIKE '%$reference%';

$result = mysqli_query($conn,$query);

How to use SQL LIKE clause with mysqli prepared statements

Try this:

$link = \mysqli_connect("127.0.0.1", "user", "password", "dbname");

if (!$link) {
$error = \mysqli_connect_error();
$errno = \mysqli_connect_errno();
print "$errno: $error\n";
exit();
}

$query = "SELECT * FROM tblSchools WHERE systemName LIKE ?";

$stmt = \mysqli_stmt_init($link);
if (!\mysqli_stmt_prepare($stmt, $query)) {
print "Failed to prepare statement\n";
exit;
} else {
$sysName = 'sys%';
\mysqli_stmt_bind_param($stmt, "s", $sysName);

\mysqli_stmt_execute($stmt);
$result = \mysqli_stmt_get_result($stmt);
$row = mysqli_fetch_array($result);
var_dump($row);
}

it works for me

How to get query result with mysqli?

If I understand correctly, you want to query all to an array without doing a loop?

The query might take a while if it's too much data, but you can try mysqli_fetch_all directly from database, like this (example):

$sql = $mysqli->query("SELECT * FROM table WHERE status = '1'"); 
$results = mysqli_fetch_all($sql, MYSQLI_ASSOC); # all rows to array

Also: you need PHP >= 5.3.0
mysqli_result::fetch_all -- mysqli_fetch_all — Fetches all result rows as an associative array, a numeric array, or both

====

EDIT:
The method above avoids the loop, but you need to escape the values on your own, like this:

$email = $mysqli->real_escape_string($email);
$GUID = $mysqli->real_escape_string($GUID);

To get all to an array using a prepared query, you will have to use a loop: This is a limitation of mysqli and that's why many people prefer pdo.
So for your case:

if (!$stmt->execute()) {
// handle error
}

// Extract result set and rows
$getresult = $stmt->get_result();
while ($data = $getresult->fetch_assoc()) {
$result[] = $data;
}

$stmt->close();
return $result;

// debug or testing
echo "<pre>";
var_dump($result);
echo "</pre>";

How to perform a LIKE query using multiple keywords from search field using mysqli prepared statement

As user3783243 states in the comments above, my placeholders and parameters where not matching. So in order to solve that, I did the following (this will be sloppy as I'm new to PHP but if someone can clean it up for me I'll award the answer to that).

First you have to create a string for the type parameter (mine are all strings so this was easy, you could run a conditional statement if you have different types). Since I use two placeholders for each entry in my SQL, each iteration will include two s's.

$typeparam='';
foreach($word as $key => $value){
$typeparam.='ss';
}

Then create a new array to put the types and the values all together (again, since there are two placeholders for each parameter, I just add the $word twice to the array):

$bindpars=array();

$bindpars[]=&$typeparam;
foreach($word as $key => $value){
$bindpars[]=&$word[$key];
$bindpars[]=&$word[$key];
}

Finally, bind the parameters using call_user_func_array:

call_user_func_array(array($stmt,'bind_param'),$bindpars);

So the code in my question now looks like this:

$word=preg_split('/[\s]+/',$terms);
$totalwords=count($word);

$sql="SELECT title,content FROM articles WHERE (title LIKE CONCAT('%',?,'%') OR (content LIKE CONCAT('%',?,'%'))";

for(i=1;$i<$totalwords;$i++){
$sql.=" AND (title LIKE CONCAT('%',?,'%') OR (content LIKE CONCAT('%',?,'%'))";
}

$stmt=$conn->prepare($sql);

$typeparam='';
foreach($word as $key => $value){
$typeparam.='ss';
}

$bindpars=array();

$bindpars[]=&$typeparam;
foreach($word as $key => $value){
$bindpars[]=&$word[$key];
$bindpars[]=&$word[$key];
}

call_user_func_array(array($stmt,'bind_param'),$bindpars);

$stmt->execute;
$stmt->store_result;

Implement LIKE in PHP prepared Statements with % wildcards

I want to thank everyone for their help with this. ArtisticPhoenix got me headed in the right direction.

This post hit the mark of what I was looking for to bring it all together:

Adding a wildcard character to a string in PHP

Here's the "slightly" updated code:

    $search = $_POST['search'].'%';

//echo($search);

$stmt = $link->prepare("SELECT lname, fname FROM planner WHERE lname LIKE ?");
$stmt->bind_param('s', $search);
$stmt->execute();
$result = $stmt->get_result();

if ($result->num_rows > 0) {
echo "<table><tr><th>Last Name</th><th>First Name</th></tr>";
while($row = $result->fetch_assoc()) {
echo "<tr><td>".$row["lname"]."</td><td>".$row["fname"]."</td></tr>";
}
echo "</table>";
} else {
echo "0 results";
}

How To Run Mysqli query and get results from table where table Like %

Connect to your database:

<?php
$mysqli = new mysqli("example.com", "user", "password", "database");

Then execute your query:

// Get the actor name
$actor = $_GET['name'];

// Define te SQL query and execute it
$query = "SELECT moviename FROM moviedetails WHERE cast LIKE '%" . $actor . "%'";
$res = $mysqli->query($query);

// Store the results in array
$movies = array();
while ($row = $res->fetch_assoc()) {
$movies[] = $row;
}

Now you can do anything you want with the $movies array.

But like JRsz said everything is in the documentation.

How to display results from mysqli prepared statement?

You have a couple of issues. The first is that you are binding 6 results, but your query only returns 4. The second is that mysqli_stmt::fetch simply returns a boolean indicating success/fail since all result data is fetched into the bound result parameters. For your query as is, you need to do something like:

$query->bind_result($first_name, $last_name, $phone, $email);
while ($query->fetch()) {
echo $first_name, $last_name, $phone, $email;
}

Notes:

  1. Since you are using a parameterised query, you don't need to use escape_string on your inputs.
  2. It's more efficient to add the % to either side of the parameter once in PHP (i.e. $keywords = "%$keywords%";) and just use LIKE ? rather than 5 instances of LIKE CONCAT('%',?,'%') in your query.


Related Topics



Leave a reply



Submit