PHP Query the problem of MSSQL.

Source: Internet
Author: User
MSSQL, field name name, field content is Chinese, field type Vachar (50), collation: SQL_Latin1_General_CP1_CI_AS (this can not be changed)

PHP Connection SQL through other related content query name can display Chinese correctly, but I by query name, can not query, is not to convert the encoding it?

Example: SELECT * from username where id=123
You can display the name Zhang San

But in turn: SELECT * from username where name= Zhang San
I can't find it.

I know I can change the collation, but it's garbled in other places. So the sorting cannot be changed.


Reply to discussion (solution)

SELECT * from username where name= ' Zhang San '

The same, not the single quotation mark problem.

First you need to make sure that your SQL instructions do not appear to be syntactically incorrect
Then we can detect the effective wording.

You can change the character set of the condition one at a time, and there may be a success

SQL_Latin1_General_CP1_CI_AS
Is the first set of European notation character sets (single byte), which can theoretically overwrite all characters
But in fact there is an internal character set conversion, whether the normal support multibyte character set is a problem

Remember that there was a time to read a book with MySQL built database is also set the Latin character set, write the project is also available, but do search function can not search, the back of their own want to turn data into UTF8, but a turn, the database is all Latin. At that time just casually built the practice project, there is no struggle to go on, it is best to save and project consistent coding bar, or later more trouble.

Strange is, if I through the table of other content query out, the name of the Chinese, can display the output, directly query the Chinese name can not be searched.
What's in the Name Field Öðîä (Chinese) ëñë÷ (search) I'll get this garbled. SQL queries can be queried to

You put the name that you queried through the other content and Base64 encoded it.
For example, the Name field of the production of the $name in the presence of
echo Base64_encode ($name);
Post the results

The GBK Environment has

Header (' Content-type:text/html;charset=utf-8 '); Echo utf8_encode (' Chinese '); Öðîäecho utf8_encode (' Search '); Ëñë÷

You put the name that you queried through the other content and Base64 encoded it.
For example, the Name field of the production of the $name in the presence of
echo Base64_encode ($name);
Post the results

The GBK Environment has

Header (' Content-type:text/html;charset=utf-8 '); Echo utf8_encode (' Chinese '); Öðîäecho utf8_encode (' Search '); Ëñë÷



Thank you for your big reply.

$str =utf8_encode (' Chinese '), echo $str, Echo Base64_encode ($STR), $r =mssql_query ("select * from  users  where Name= '" . $str. "'");


Display the Chinese word conversion to copy into the SQL tool can query, why in PHP still can't find it?

Give your SQL statement a try in phpMyAdmin first.

You're not a SELECT * from username where id=123
You can display the name Zhang San
I asked you to post the Base64 code for the value of name

You're not a SELECT * from username where id=123
You can display the name Zhang San
I asked you to post the Base64 code for the value of name



Yes, this will display Chinese correctly.

How did you get the Base64 code string?
Will not be echo base64_encode (' Chinese '); Is that right?

No, it's not. I was the result of the query,
Query statements in PHP:

$r =mssql_query ("select * from  users where denglu=1");

Query results are directly BASE64:
Base64_encode ($row [1])

How did you get the Base64 code string?
Will not be echo base64_encode (' Chinese '); Is that right?



No, it's not. I was the result of the query,
Query statements in PHP:
$r =mssql_query ("select * from  users where denglu=1");


Query results are directly BASE64:
Base64_encode ($row [1])


If the moderator you convenient, I QQ remote to you to see

Coding problem, your PHP default code is inconsistent, the value passed to MySQL is not ' Zhang San ', of course, can not query.
Can try to put PHP header ("content-type:text/html; Charset=latin1 ");

Coding problem, your PHP default code is inconsistent, the value passed to MySQL is not ' Zhang San ', of course, can not query.
Can try to put PHP header ("content-type:text/html; Charset=latin1 ");



I've changed the PHP code to UTF8. The statement output is also:
SELECT * from server01.dbo.cabal_character_table where name= ' Öðîä '//in the database the same as this, the SQL tool directly executes this command can be searched, this is the UTF8 encoded character, The original is: Chinese

where Name= ' Öðîä '

You this garbled value, in the data is garbled?

where Name= ' Öðîä '

You this garbled value, in the data is garbled?



Yes, that's how it's queried in the database.



I was wondering if MSSQL has any conversion statements that can be converted to the data type of the column name at query time. For example:

Select here to convert the name to 16 binary or char from name where name= here converts the character to 16 binary or Char

I am more curious about why, through the other column names such as Name= Zhang San id=1, through the query id=1,php query can normally be converted into Chinese, but not in turn,

PHP default Settings GB2312, MSSQL is gbk,name this column is varchar (50) storage method is Iso-8859-1

First you have to solve the problem of garbled database content, garbled solved, the next step is to read the data encoding problem.

First you have to solve the problem of garbled database content, garbled solved, the next step is to read the data encoding problem.



Database garbled cannot be solved. It's not in my control. Only to find a way to read, really can not forget.

When you put it in, it was stored in the UTF8 or GBK encoded format in Chinese.
Save with Latin1 encoding
When you take it out in the form of UTF8 or GBK, of course you get the right Chinese
If you take the Latin1 code, take is the database stored garbled, should be exactly the same.
If you want to query the Chinese through the database operation, you should query the GBK to latin1 of that code
More boring ... iconv can't seem to turn utf8 Chinese to Iso-8859-1//ignore
Personally feel no meaning ... Look at yourself ... There's no way to use fuzzy search.
The data is stored so that there is very little direction to operate.

You try to run the SELECT * from username where name= ' Zhang San ' in the database.

Change the connection library code, UTF8.

I have already solved it. Knot has been affixed. Thank you for your enthusiastic answer and help! Thank you!!

  • Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.