php - 使用不同的字符集搜索数据库

Question

我有一个带有小型搜索脚本的网站，我使用 utf-8 字符集。现在在我的 mysql 数据库上我使用 latin1_swedish_ci 字符集。当我想要搜索包含字母 å、ä 或 ö 的内容时，它不会返回任何结果，因为在数据库中字母 å=à¥、ä=¤ 和 ö=ö¶。

此问题的解决方案是告诉搜索脚本将这些字母替换为数据库可以理解的字母。

因此，当我的用户搜索其中包含字母 å 的内容时，它应该将其转换为 à 并返回正确的结果。

知道如何实现这一目标吗？

提前致谢

编辑:
添加了我尝试修改的脚本，但没有成功。
关于如何修改它以使其“转换”字符集的任何想法:

<?php  


// file for database connection
include('inc/db.inc.php');

// configuration file
include('inc/config.inc.php');

if(isset($_GET['p'])) {
  $page_number = $_GET['p'];
  $arraySearch = $_GET['terms'];
  $show_count = $_GET['count'];
  settype($page_number, 'integer');
}
$nospaces = substr($_GET['terms'],0,4);
  $offset = ($page_number - 1) * $records_number;
// check for an empty string and display a message.
if ($_GET['terms'] == "") {
  echo  '<div id="counter">ex. write &acute;here and&acute; or &acute;search&acute; without quotes.</div>';
// minim 3 characters condition
  } else if(strlen($_GET['terms']) < $limitchar) {
 echo '<div id="counter">'. $limitchar .' characters minimum</div>';
// no spaces in first 4 letters
  } else if(preg_replace('/[a-zA-Z0-9]/', '', $nospaces))  {
 echo '<div id="counter">Please use letters or numbers in first 4 characters</div>';
  } else  {

// explode search words into an array
  $arraySearch = explode(" ", $_GET['terms']);
// table fields to search
  $arrayFields = array(0 => $first_field, 1 => $second_field);
  $countSearch = count($arraySearch);
  $a = 0;
  $b = 0;
  $query = "SELECT * FROM $table_name WHERE (";
  $countFields = count($arrayFields);
  while ($a < $countFields)
  {
    while ($b < $countSearch)
    {
      $query = $query."$arrayFields[$a] LIKE '%$arraySearch[$b]%'";
      $b++;
      if ($b < $countSearch)
      {
        $query = $query." AND ";
      }
    }
    $b = 0;
    $a++;
    if ($a < $countFields)
    {
      $query = $query.") OR (";
    }
  }
  $query = $query.") LIMIT $offset, $records_number;";
  $search = mysql_query($query);


// get number of search results
  $arrayFields = array(0 => $first_field, 1 => $second_field);
  $countSearch = count($arraySearch);
  $a = 0;
  $b = 0;
  $query = "SELECT * FROM $table_name WHERE (";
  $countFields = count($arrayFields);
  while ($a < $countFields)
  {
    while ($b < $countSearch)
    {
      $query = $query."$arrayFields[$a] LIKE '%$arraySearch[$b]%'";
      $b++;
      if ($b < $countSearch)
      {
        $query = $query." AND ";
      }
    }
    $b = 0;
    $a++;
    if ($a < $countFields)
    {
      $query = $query.") OR (";
    }
  }
  $query = $query.")";
  $count_results = mysql_query($query);
  $numrows = mysql_num_rows($count_results);

// no results
if($numrows == 0) {
        echo '<div id="counter">No results found</div>';

// show results
} else {

echo '<div id="results">
<div id="results_top"><p><b>'. $_GET['terms'] .'</b> - '. $numrows .' results found</p></div>
';

while($row = mysql_fetch_assoc($search)) {

$urltitle = str_replace(" ","_", $row['title']);

echo '<div class="item">
<div class="details"><a href="http://www.onlinegamez.net/'.$urltitle.'-'.$row['id'].'.html" style="float:left;"><img src="http://www.onlinegamez.net/files/image/'.$row['icon'].'" width="90" height="65" alt="'.$row['title'].'"/></a><a href="http://www.onlinegamez.net/'.$urltitle.'-'.$row['id'].'.html" class="title">'.$row['title'].'</a><br />
'.$row['description'].'</div>
<div class="played"><span>'.$row['timesplayed'].'</span>
<p>played</p></div>
<div style="clear:both;"></div></div>'; 
}
// pagination
  $maxPage = ceil($numrows/$records_number);

  $nav = '';
for($page = 1; $page <= $maxPage; $page++) {
  if ($page == $page_number)     {
     $nav .= "$page";
  }
  else
  {
      $nav .= "<a href=\"javascript:htmlData('search.php','terms=".$_GET['terms']."&amp;p=$page')\">$page</a>";
  }
}

if ($page_number > 1) {

  $page = $page_number - 1;
  $prev = "<a href=\"javascript:htmlData('search.php','terms=".$_GET['terms']."&amp;p=$page')\">&laquo;</a>";

  $first = "<a href=\"javascript:htmlData('search.php','terms=".$_GET['terms']."&amp;p=1')\">First</a>";
}
else {
  $prev = '';
  $first = '';
}

if ($page_number < $maxPage) {
  $page = $page_number + 1;
  $next = "<a href=\"javascript:htmlData('search.php','terms=".$_GET['terms']."&amp;p=$page')\">&raquo;</a>";

  $last = "<a href=\"javascript:htmlData('search.php','terms=".$_GET['terms']."&amp;p=$maxPage')\">Last</a>";
}
else {
  $next = '';
  $last = '';
}
  echo $data;
echo "<div id=\"results_bottom\"><p>$first $prev $nav $next $last</p></div>
</div>";
    }
 }
?>

Answer 1

首先——为什么要使用不同的字符集？

有多种方法可以解决您的问题，您可以更改连接上的字符集；

set names 'utf8';
set character set 'utf8' collate 'utf8_swedish_ci';

您可以进行基于排序规则的匹配；

select
    `field`
from
    `table`
where
    cast(`field` as char character set 'utf8') collate 'utf8_swedish_ci' = '$PHP_VARIABLE'

或者您可以更改 PHP 中变量的字符集；

$var_in_iso88591 = utf8_decode($var_in_utf8);

查看utf8_encode()/utf8_decode()和 iconv()