0

I have some UTF-8 characters that were stored incorrectly in my MySQL database, e.g. £ is stored as £. Since there are too many to manually correct I want to run a query via PHP to get the affected rows and use regular expressions to replace them.

The basic query I am using to test this is:

SELECT * 
  FROM `notes` 
 WHERE `notes` LIKE '%£%';

If I run this in phpMyAdmin it returns 754 rows, but in my PHP script it only returns 5 rows:

$query = "SELECT * FROM `notes` WHERE `notes` LIKE '%£%'";
$result = mysqli_query($this->connection, $query);
echo "rows... " . mysqli_num_rows($result));

I also tried setting the character set:

mysqli_set_charset($connection, 'utf-8');

Why does the same query to the same database produce different results?

AmigoJack
  • 5,234
  • 1
  • 15
  • 31
Alan A
  • 2,557
  • 6
  • 32
  • 54
  • You probably need to use `SET CHARSET` after opening the connection. – Barmar Sep 17 '21 at 19:50
  • `'utf-8'` is not a valid charset. Please use `utf8mb4` and make sure your database columns are also set to it. – Dharman Sep 17 '21 at 20:10
  • @Dharman thank you that fixes the issue, do you want to answer the question for the full credit – Alan A Sep 18 '21 at 08:35
  • I already gave you the answer. It's linked at the top of the page. It's called canonical and it should help you solve the problem – Dharman Sep 18 '21 at 08:41
  • "myPHPAdmin" does not exist. Pay attention to details and you'll notice _phpMyAdmin_ will also tell you at one place that it uses `utf8m4` or `utf8`, and not "utf-8". – AmigoJack Sep 18 '21 at 10:55

0 Answers0