CODEWITHSUNDEEP
phpmysql
Disk01
Disk01

Reputation: 357

MySQL returning 0 rows where the rows have & symbol

Using PHP I'm trying to fetch records from MySQL using following syntax

select * from groups where groupname="Soya & Group";

Even after it exist in the table but still it is returning 0 rows. Table have a lot of data which cannot me modified manually, rows where have & operator it is not returning any records. Please help me with it. 

$sql = "SELECT * from groups where groupname='Soya & Group';";
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {    
    $result = $conn->query($sql);
    echo "<td>ID ".$row["id"]."</td>";
    echo "<td>ID ".$row["groupname"]."</td>";
        }

    } else {
        echo "0 results";
    }

Upvotes: 1

Views: 77

Answers (2)

Disk01
Disk01

Reputation: 357

Thank you everyone for the support. Well, I replaced & with and in the MySQL table using following query 

UPDATE groups set groupname=REPLACE(groupname,'&','and');

This query solved my problem.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

This is too long for a comment.

One doubts very much that the ampersand is the problem. But, you can easily test for it using like:

select *
from groups
where groupname like 'Soya _ Group';

The _ character will match anything.

More likely, there is a subtle problem with the data in your database. See if one of these return anything:

where groupname like '%Soya _ Group%';
where groupname like '%Soya&Group%';
where lower(groupname) like '%soya%group%';

A lot of different things could be happening. For instance, your default collation could be case sensitive and the values could really be Soya & group. There are other possibilities as well. Once you get some match, you can investigate the actual values to determine what is happening.

Upvotes: 1

Related Questions