I'm searching for a field in a SQL server 2005 table. I'm using something like
$query = "select * from TableName where replace ( replace ( client , 'é' ,'e' ) , 'ç' ,'c' ) COLLATE French_CI_AI = 'Agence française du texte agréable'";
I can ask the table to get the name without the 'where' clause, but I can not ask it if I use this specific name 'Agence française du texte agréable' in a where clause
Every other names are working so I suppose that the problem is related to the 'ç' or 'é' character
thank you for your help !
------------- EDIT --------------
thank you for your anwser. I have made another test by removing the ç and é, like this 'Agence francaise du texte agreable'
And I still can't get the infos ! So finally where is my problem ? ... I also tested $query = "select * from Table where client = 'Agence francaise du texte agreable'";
and the var_dump returns resource(4, SQL Server Statement)
and the var_dump of the fetch_array, returns null
I don't understand ... it works with other names :(
---------- EDIT 2 --------------
Found that my problem comes from the fact that I had only one result, and my code did not work then. Then changed my code with
$query = "select * from Table where client COLLATE French_CI_AI = 'Agence française du texte agréable'";
// SQLSrv_num_rows requires a static or keyset cursor.
if (strncmp(ltrim(strtoupper($query)), 'SELECT', strlen('SELECT')) == 0)
{
$array = array('Scrollable' => SQLSRV_CURSOR_KEYSET);
}
else
{
$array = array();
}
$cursor = sqlsrv_query($db, $query, array(), $array);
$row = sqlsrv_fetch_array($cursor, SQLSRV_FETCH_NUMERIC);
thank you all for your help!
I don't think you even need to remove the special characters at all (ie: the replace() ) why not just just do:
$query = "select * from TableName COLLATE French_CI_AI = 'Agence française du texte agréable'";
then if you really want to remove the french characters for whatever reason:
$result = str_replace("é", "e", str_replace("ç", "c"$result);
This way you can still replace the French characters and not mess up your query.