SQL like statement problems
mysqli_stmt::bind_param
can only bind a specific variable, not an expression. The supplied variable is passed to 'bind' by reference, and not by value, meaning that the underlying SQL gets whatever value that variable has at the time the command is executed, not when it's bound.
Use:
WHERE field LIKE CONCAT('%', ?, '%")
or do:
$brand = '%' . $brand . '%'
immediately before the command is executed.
What you can't do is:
WHERE field LIKE '%?%
because the ?
bound variable must correspond to a single string or numeric value, not to a substring (or field name).
EDIT in this case, your real problem appears to be mixing up prepared statements (as supported by mysqli::prepare
and mysqli_stmt::execute()
) with plain old queries (as done with mysqli::query()
). You should also just ask for the number of rows directly from the DB server, rather than pull the data and use num_rows:
$countQuery = "SELECT COUNT(ARTICLE_NO) FROM AUCTIONS WHERE upper(ARTICLE_NAME) LIKE ?";
if ($numRecords = $con->prepare($countQuery)) {
$numRecords->bind_param("s", $brand);
$numRecords->execute();
$numRecords->bind_result($num_rows);
$numRecords->fetch();
$numRecords->free_result();
$numRecords->close();
$last = ceil($rowcount/$page_rows);
} else {
print_r($con->error);
}
Problems with LIKE command in SQL Server
Query clauses like where colname like '%something%'
are not able to take advantage of indexes and usually result in a full scan of the possible rows to ascertain which ones should be delivered
Although, as ChrisC points out in a comment, it's somewhat surprising that the more efficient clauses aren't firt used to reduce the candidate rowset down to a manageable size before trying to use like
- perhaps the statistics for the table are not up to date enough for the query analysis to decide this - best run whatever counts for an explain query
under SQL Server.
The reason your non-like query is so fast is because it almost certainly has an index on MgmtLogHost
and/or MgmtLogTime
which can be used to quickly cull unneeded rows.
One way you can fix this is to use something like insert/update triggers to process the MgmtLogText
data only when changed, to extract the application names out and put them in a separate table which can be far better optimised.
Even just using such a trigger to keep a lowercased version of the column (in another column) would be an improvement. Using a case-insensitive collation means that selects run slower since they have to allow for XYZZY
and xyzzy
being classed as equal. If instead you maintain a lower-cased version in the table and ensure the check is done against lower case, that effort disappears as you only have one case to worry about.
And, by doing all this in the trigger, you ensure that it's only done when necessary (when the data is changed), not every time you want to select. This amortises the cost over many selects.
You can also use something like full text indexing if your DBMS supports it but I've often thought that was like trying to kill mosquitos with a thermo-nuclear warhead.
Yes, there are situations where you may need full text indexing but, in the vast majority of cases, you can gain efficiency by being a little more selective.
LIKE clause is not working in SQL server
You get an empty result because there is no row in your table where DocID
is like both %DSRB%
and %DHA%
at the same time. You don't say in your question, but I guess you are expecting to receive the two rows with DocIds DHA12
and DSRB23
.
To do this, you need to select rows where DocID
is like either %DSRB%
or %DHA%
. Try changing the AND
in your WHERE
clause to an OR
:
SELECT DocId, NSN, Qty, RequestDate, ReceiveDate
FROM Orders
WHERE (DocID LIKE '%DSRB%') OR (DocID LIKE '%DHA%')
See this introduction to SQL Logical Operators if you want more examples.
SQL Not Like Statement not working
If WPP.COMMENT
contains NULL
, the condition will not match.
This query:
SELECT 1
WHERE NULL NOT LIKE '%test%'
will return nothing.
On a NULL
column, both LIKE
and NOT LIKE
against any search string will return NULL
.
Could you please post relevant values of a row which in your opinion should be returned but it isn't?
SQL LIKE operator not showing any result when it should
Promoted to an answer as requested.
You have some space characters in the end of your Owner field in Vehicle table. '=' operator skips trailing spaces, 'like' operator doesn't.
In SQL. Why does this 'Like' statement with a wildcard not work?
This doesn't work because the brackets in the string have a special function in a LIKE statement - items between the brackets constitute a set of values that the singular character at the specified position matches. Your original pattern looks for a 1,0,4, or 2 followed by a 1 or an 0. To make this work, you should have a pattern like this:
Declare @Temp2 as nvarchar(max) = '[[]10004120][[]1100]'
SQL LIKE operator with aaa% having strange behavior
Please see this test case:
DECLARE @TestTable TABLE
(
id INT IDENTITY(1, 1)
, LastName NVARCHAR(255) COLLATE Danish_Norwegian_CI_AS
)
INSERT @TestTable (LastName)
VALUES (N'aa')
, (N'aaa')
, (N'aaaa')
SELECT *
FROM @TestTable AS TT
WHERE TT.LastName LIKE N'aaa%' COLLATE Latin1_General_CI_AS
Without the COLLATE Latin1_General_CI_AS
you get the behaviour you are seeing.
Related Topics
Connect to PHP Server Using Swift
How to Count Same Values in an Array and Store It to a Variable
Storing Database Connection in a Session Variable
Proper Repository Pattern Design in PHP
Php/Symfony/Doctrine Memory Leak
Laravel 5.4 - Validation with Regex
Count the Number of Pages in a PDF in Only PHP
Error: MySQLnd Cannot Connect to MySQL 4.1+ Using the Old Insecure Authentication
Swift Sync Data of Core Data an MySQL Database
What Does the B in Front of String Literals Do
PHP Simplexml Attributes Are Missing
Rest API Authorization & Authentication (Web + Mobile)
Simple PHP Form: Attachment to Email (Code Golf)
Sending Multiple Data Parameters with Jquery Ajax