MySQL v 5.0
php v 5.3
When I query the database I get an incorrect result if the value I use in the where clause contains an underscore "_".
1. If i use the "like" option with the % percent wildcard all rows are returned that contain the same character up to the underscore.
Result from sample code below
IPPI0001
IPPL0001
IPP_0001
2. If I use the "=" option with the % percent wildcard, I get no rows returned.
3. The only way it works is if I use the "=" option with no %
Result from sample code below
IPP_0001
I would most like to know why paragraph 1 above returns too many rows?
Here is some stand alone code that will demonstrate the problem. Simply plug in your database connection and run code. Thanks in advance for any help.
Expand|Select|Wrap|Line Numbers
- <?php
- try // Error trap entire applet.;
- {
- /*open connection to YOUR database*/
- //**********SET UP YOUR DATABASE CONNECTION**************************
- $mysqli = new mysqli("localhost", "my_user", "my_password", "myDbName");
- $mysqli->query('DROP TABLE IF EXISTS `myDbName`.`myTest1`');
- $mysqli->query("CREATE TABLE myTest1 (`myKey` VARCHAR(8) not null default' ', PRIMARY KEY(`myKey`) ) ENGINE=MyISAM");
- $mysqli->query("INSERT INTO myTest1 set myKey='IPPI0001'");
- $mysqli->query("INSERT INTO myTest1 set myKey='IPPL0001'");
- $mysqli->query("INSERT INTO myTest1 set myKey='IPP_0001'");
- /* QUERY 1 using IPP_0001*/
- $result = $mysqli->query("select myKey from myTest1 where myKey like'IPP_0001%'");
- echo "query 1 using 'like IPP_0001' INCORRECT RESPONSE<br>";
- while ($record = $result->fetch_assoc())
- {
- echo ' '.$record['myKey']."<br>";
- }
- $result->free();
- $result = $mysqli->query("select myKey from myTest1 where myKey like'IPPL0001%'");
- echo "query 1 using 'like IPPL0001'<br>";
- while ($record = $result->fetch_assoc())
- {
- echo ' '.$record['myKey']."<br>";
- }
- $result->free();
- /* QUERY 2 */
- $result = $mysqli->query("select myKey from myTest1 where myKey ='IPP_0001%'");
- echo "query 2 using '= IPP_0001%'<br>";
- while ($record = $result->fetch_assoc())
- {
- echo ' '.$record['myKey']."<br>";
- }
- $result->free();
- $result = $mysqli->query("select myKey from myTest1 where myKey ='IPPL0001%'");
- echo "query 2 using '= IPPL0001%'<br>";
- while ($record = $result->fetch_assoc())
- {
- echo ' '.$record['myKey']."<br>";
- }
- $result->free();
- /* QUERY 3 */
- $result = $mysqli->query("select myKey from myTest1 where myKey ='IPP_0001'");
- echo "query 3 using '= IPP_0001'<br>";
- while ($record = $result->fetch_assoc())
- {
- echo ' '.$record['myKey']."<br>";
- }
- $result->free();
- $result = $mysqli->query("select myKey from myTest1 where myKey ='IPPL0001'");
- echo "query 3 using '= IPPL0001'<br>";
- while ($record = $result->fetch_assoc())
- {
- echo ' '.$record['myKey']."<br>";
- }
- $result->free();
- }
- ?>