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:
- Since you are using a parameterised query, you don't need to use
escape_string
on your inputs. - It's more efficient to add the
%
to either side of the parameter once in PHP (i.e.$keywords = "%$keywords%";
) and just useLIKE ?
rather than 5 instances ofLIKE CONCAT('%',?,'%')
in your query.
Related Topics
How to Post Data in PHP Using File_Get_Contents
Reference: What Is a Perfect Code Sample Using the MySQL Extension
How to Insert Multiple Rows from Array Using Codeigniter Framework
Sort an Array by Keys Based on Another Array
Allow PHP Sessions to Carry Over to Subdomains
How to Encrypt and Decrypt a PHP String
PHP - Concatenate or Directly Insert Variables in String
How to Get a Variable Name as a String in PHP
How to Prevent Duplicate Usernames When People Register
Can PHP Curl Retrieve Response Headers and Body in a Single Request
PHP - Floating Number Precision
How to Group Subarrays by a Column Value
Why Does PHP Consider 0 to Be Equal to a String
Can't Use Method Return Value in Write Context