Correct way to use LIKE '%{$var}%' with prepared statements?
Try this
$likeVar = "%" . $yourParam . "%";
$stmt = $mysqli->prepare("SELECT * FROM REGISTRY where name LIKE ?");
$stmt->bind_param("s", $likeVar);
$stmt->execute();
you need to prepare the query using simply ?
then you bind the param using bind_param
.
How to use prepared statement for LIKE and % in WHERE?
You need to supply query parameter when you bind the parameters like below:
$order = "SELECT name, name_id FROM requests WHERE name LIKE ?" ;
$var = "%" . $_POST['name'] . "%";
$statement->bindParam(1, $var);
MySQLi prepared statements with IN operator
I've recently found the solution for my question. Maybe it's not the best way to do it, but it works nice! Prove me wrong:)
<?php
$lastnames = array('braun', 'piorkowski', 'mason', 'nash');
$arParams = array();
foreach($lastnames as $key => $value) //recreate an array with parameters explicitly passing every parameter by reference
$arParams[] = &$lastnames[$key];
$count_params = count($arParams);
$int = str_repeat('i',$count_params); //add type for each variable (i,d,s,b); you can also determine type of the variable automatically (is_int, is_float, is_string) in loop, but i don't need it
array_unshift($arParams,$int);
$q = array_fill(0,$count_params,'?'); //form string of question marks for statement
$params = implode(',',$q);
$data_res = $_DB->prepare('SELECT `id`, `name`, `age` FROM `users` WHERE `lastname` IN ('.$params.')');
call_user_func_array(array($data_res, 'bind_param'), $arParams);
$data_res->execute();
$result = $data_res->get_result();
while ($data = $result->fetch_array(MYSQLI_ASSOC)) {
...
}
$result->free();
$data_res->close();
?>
Using like wildcard in prepared statement
You need to set it in the value itself, not in the prepared statement SQL string.
So, this should do for a prefix-match:
notes = notes
.replace("!", "!!")
.replace("%", "!%")
.replace("_", "!_")
.replace("[", "![");
PreparedStatement pstmt = con.prepareStatement(
"SELECT * FROM analysis WHERE notes LIKE ? ESCAPE '!'");
pstmt.setString(1, notes + "%");
or a suffix-match:
pstmt.setString(1, "%" + notes);
or a global match:
pstmt.setString(1, "%" + notes + "%");
Doctrine - How to print out the real sql, not just the prepared statement?
Doctrine is not sending a "real SQL query" to the database server : it is actually using prepared statements, which means :
- Sending the statement, for it to be prepared (this is what is returned by
$query->getSql()
) - And, then, sending the parameters (returned by
$query->getParameters()
) - and executing the prepared statements
This means there is never a "real" SQL query on the PHP side — so, Doctrine cannot display it.
How to using LIKE operator which data saved in variable?
Try this
$sqlGetFreq = "SELECT keyword, frequency FROM termfrequency WHERE keyword LIKE '%".$inFilter."%' ";
Also please set the following to track any possible error.
ini_set("display_errors", "1");
error_reporting(E_ALL);
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 can I assign a variable with a prepared statement in a stored procedure?
First, to use dynamic table/column names, you'll need to use a string/Prepared Statement like your first query for @s
. Next, to get the return-value from COUNT()
inside of the query you'll need to use SELECT .. INTO @vTotalFT
.
The following should be all you need:
SET @vTotalFTquery = CONCAT('(select sum(Count_of_Records) INTO @vTotalFT from
(select substring(cast(', vField, ' as char(50)),1,2) as FT_Digits, count(*) as Count_of_Records
from ', vTable, '
where ', vField, ' >= 10
group by 1) a);');
PREPARE stmt FROM @vTotalFTquery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Please note: the variable name has changed from vTotalFT
to @vTotalFT
. It doesn't seem to work without the @
. And also, the variable @vTotalFT
won't work when declared outside of/before the query, so if you encounter an error or empty results that could be a cause.
Related Topics
What's the Difference Between Isset() and Array_Key_Exists()
Using $_Post to Get Select Option Value from Html
PHP Echo VS PHP Short Echo Tags
Nginx Serves .PHP Files as Downloads, Instead of Executing Them
PHP Multidimensional Array Searching (Find Key by Specific Value)
Create Array Tree from Array List
PHPmailer - Smtp Error: Password Command Failed When Send Mail from My Server
Are Arrays in PHP Copied as Value or as Reference to New Variables, and When Passed to Functions
Get the Last Inserted Id Using Laravel Eloquent
What Does $$ (Dollar Dollar or Double Dollar) Mean in PHP
Base_Url() Function Not Working in Codeigniter
Create Word Document Using PHP in Linux
How to Declare a Global Variable in PHP