php mysql获取带有字符问题的名称

问题描述：

Hi i have a list of restaurants name in my db where some of the name comes with character like &, @, and ' (quote), the way the name are displayed in browser when viewing then are http://localhost/my-restaurant-new-york as i use this function to replace empty spaces with dash -

$businessDetail = strtr($businessDetail, '-', ' ');

based on business name an business id will be found and retrieve all the related infos. If in my db i have a name like My Restaurant New & york i cause an error in sql as follow

Message: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 3.

The question now is how can i save the name in the beginning and how to retrieve it back without having an issue with special characters. Thanks

UPDATE: i am using zend framework so this is how i save name into db and retrieve back

$testMapper = new Application_Model_Mapper_TestMapper();
$testModel = new Application_Model_Test();

$bzname =  str_replace("'", '', $this->_getParam('name'));
$testModel->setId($id)
          ->setName($bzname);
$business_id = $testMapper->save($testModel);

All link to the business name are translated by this function

$this->view->bzurl = preg_replace("![^a-z0-9]+!i","-", $result['business_name']);

Update2:

public function getBusinessId($business_detail)
    {
        $select = $this->getDbTable()->getAdapter()->select();
      $select->from('business',array('business_id'))

               ->where("business_name='".$business_detail."'"); 



        $result = $this->getDbTable()->getAdapter()->fetchRow($select);
        return $result['business_id'];      
    }

我的db中有一个餐馆名称列表，其中一些名称带有＆amp;等字符 ;，@和'（引用） code>，在查看时名称在浏览器中显示的方式是 http：// localhost / my-restaurant-new-york code>，因为我使用这个函数用短划线 - code> p>

  $ businessDetail = strtr（$ businessDetail，' - '，''）; 
  code  >  pre> 
 
 根据业务名称，将找到业务ID并检索所有相关信息。 如果在我的数据库中我有一个像 My Restaurant New＆amp; 我在sql中导致错误，如下所示 p> 
 
 
 消息：SQLSTATE [42000]：语法错误或访问冲突：1064 SQL语法中有错误; 检查与MySQL服务器版本对应的手册，以便在第3行的'）'附近使用正确的语法。
  code>  pre> 
 
 现在的问题是如何保存 开头的名称以及如何检索它而不会出现特殊字符问题。 谢谢 p> 
 
 
 UPDATE：
i我正在使用zend框架，所以这就是我如何将名称保存到db并检索回来 p> 
 
 
  $ testMapper = new  Application_Model_Mapper_TestMapper（）; 
 $ testModel = new Application_Model_Test（）; 
 
 $ bzname = str_replace（“'”，''，$ this-＆gt; _getParam（'name'））; 
 $ testModel-＆gt;  setId（$ id）
  - ＆gt; setName（$ bzname）; 
 $ business_id = $ testMapper-＆gt; save（$ testModel）; 
  code>  pre> 
 
 全部 该商务名称的链接由此函数翻译 p> 
 
 
  $ this-＆gt; view-＆gt; bzurl = preg_replace（“！[^ a-z0-9] +！i  “，” - “，$ result ['business_name']）; 
  code>  pre> 
 
  Update2： p> 
 
 
 公共函数 getBusinessId（$ business_detail）
 {
 $ select = $ this-＆gt; getDbTable（） - ＆gt; getAdapter（） - ＆gt; select（）; 
 $ select-＆gt; from（'business'，array（'  business_id'））
 
  - ＆gt; where（“business_name ='”。$ business_detail。“'”）;  
 
 
 
 $ result = $ this-＆gt; getDbTable（） - ＆gt; getAdapter（） - ＆gt; fetchRow（$ select）; 
返回$ result ['business_id'];  
} 
  code>  pre> 
  div>

答

->where("business_name='".$business_detail."'")

should be:

->where("business_name = ?", $business_detail)

to ensure that the data is correctly escaped. If that's the query generating the error, that should fix your issue. I'd recommend you read up a little on SQL injection and how to avoid it.

php mysql获取带有字符问题的名称

相关推荐