How to manage data with "+" in mysql where clause

Question

I have a blood group data in a member table [A-,A+,B+,B-,AB+, AB-,O+,O-] I have a search feature which allows me to select above blood groups and search for the member who has the selected blood group. But my query is not fetching me the members who has the blood group ending with "+". Same query is working fine for the members having the blood group "-".

Then i am passing the query result to a javascript url which fetches the final query results. So I feel javascript variable is putting an end when it get's +. So how to manage this?

This is my query:

$bldgroup       = mysql_real_escape_string($_POST["bldgroup"]);
$sqlsearch  =   mysql_query("SELECT * from member
                            WHERE blood_group='$bldgroup' 
                            ORDER BY memid ASC");

<script>

var url = "../include/query.php?flag=search&&query="+sqlsearch;

</script>
 <div><label>Blood Group</label></div>  
            <div>
            <select id="bldgroup" name="bldgroup" >  
               <option value="">Select</option>  
               <option value="A+">A+</option>  
               <option value="A-">A-</option> 
               <option value="B+">B+</option>
               <option value="B-">B-</option>
               <option value="AB+">AB+</option>
               <option value="AB-">AB-</option>
               <option value="O+">O+</option>
               <option value="O-">O-</option>  
            </select> 
         </div>
       </div>


//This the content inside ./include/query.php file
if(isset($_REQUEST["flag"]))
{
   $list  = $_REQUEST["flag"];
   if($list == "search")
   {
      $query = $_REQUEST["query"];  
      $sql = $query;
      $sql.= mysql_query" ORDER BY t1.MemberID DESC";    
   }
}

Add error reporting to the top of your file(s) right after your opening PHP tag for example ` — Funk Forty Niner, Apr 17 '15 at 12:57
Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Apr 17 '15 at 12:58
The `+` sign cause the string to end .. You should use **`prepared statement`** instead.. You can find help [here](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — Abhishek Ghosh, Apr 17 '15 at 13:02
Please elaborate @AbhishekGhosh, how does the `+` cause the string to end? — Jay Blanchard, Apr 17 '15 at 13:13
I am guessing that the `+` *may* cause the string to end while parsing the query.. I may be wrong! — Abhishek Ghosh, Apr 17 '15 at 13:15
Check your table directly if there is any data with A+ ? if not, you have to convert + sign to \+ before inserting into mysql db. — Ali Sheikhpour, Apr 17 '15 at 13:27
Nathan; you need to provide DB schema. This is guesswork at best and nobody will be able to provide you with a solution at this point. Error handling will tell you if you're getting errors; do that and then share that with us also. — Funk Forty Niner, Apr 17 '15 at 13:33

Rein Baarsma · Answer 1 · 2015-04-17T13:45:08.317

The code works for me.. I created a table member with the a record with blood_group A+ and a one with blood_group A-. Selecting A+ and sending the form results for me results in the correct record.

Of course I don't know your table definition, so there might be something wrong with your MySQL query.

Try using

$sql = "SELECT * from member where       blood_group='$bldgroup' order by memid asc";
$search_result = mysql_query($sql) or die ($sql."<BR>".mysql_error());

Also note the comments you already received. Your code uses deprecated functions that are generally insecure and you should at least use the mysqli functions.

My PHP code:

<?php
mysql_connect("localhost", "root", "root");
mysql_select_db("test");

$bldgroup       = mysql_real_escape_string($_POST["bldgroup"]);
$sqlsearch  =   mysql_query("SELECT * from member where       blood_group='$bldgroup' order by memid asc");

while ($row = mysql_fetch_row($sqlsearch)) {
    var_dump($row);
}

?>
<form method="POST">
 <div><label>Blood Group</label></div>  
            <div>
            <select id="bldgroup" name="bldgroup" >  
               <option value="">Select</option>  
               <option value="A+">A+</option>  
               <option value="A-">A-</option> 
               <option value="B+">B+</option>
               <option value="B-">B-</option>
               <option value="AB+">AB+</option>
               <option value="AB-">AB-</option>
               <option value="O+">O+</option>
               <option value="O-">O-</option>  
            </select> 
         </div>
       </div>
       <input type="submit">
</form>

My table definition:

CREATE TABLE `member` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `blood_group` varchar(20) NOT NULL,
  `memid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

INSERT INTO `member` (`id`, `blood_group`, `memid`) VALUES
(1, 'A+', 123),
(2, 'A-', 231);

I don't see anything different here, than what's already been said in comments. What works for you, doesn't mean it will work for them. — Funk Forty Niner, Apr 17 '15 at 13:21
Well from the code provided the problem does not seem to be there, so it is probably in the table definition.. so hopefully he can use the or die() construction to understand what is going wrong. — Rein Baarsma, Apr 17 '15 at 13:26
What you should have done was; included the codes from the table and column creation that you used to "successfully" create and query the table. What column types used etc. That way, it will give the OP an indication as to where they might have gone wrong. — Funk Forty Niner, Apr 17 '15 at 13:30
Ok point taken. I've added the code that I used, which is working. — Rein Baarsma, Apr 17 '15 at 13:45

score 0 · Answer 2 · answered Apr 17 '15 at 13:44

0

try escaping last character of search term using \ to make sure that PLUS sign is acting as string:

$bldgroup= substr($bldgroup, 0, -1).'\'.substr($bldgroup, -1);

also check your table if is there A+ in your table at all?! if not, also escape + by \+ while inserting data into database.

answered Apr 17 '15 at 13:44

Ali Sheikhpour

10,475
5
41
82

Actually my query is returning the right value. But then i am sending the query result to a javascript variable which will refer to other URL. Here it is going wrong – Nathan Apr 20 '15 at 05:10
var url = "../include/query.php?flag=search&&query="+sqlsearch ; – Nathan Apr 20 '15 at 05:12
I have modified the code above. Please go throught it and let me know if you have any solution – Nathan Apr 20 '15 at 05:21
Do not envelope entire SQL query in your url. Just send necessary parameters and recreate sql query in php page – Ali Sheikhpour Apr 20 '15 at 06:48
I need to send entire query in the url. Because in the php page this query is used like a paramater to get the other query result – Nathan Apr 20 '15 at 08:00

How to manage data with "+" in mysql where clause

2 Answers2