search

Home  >  Q&A  >  body text

When mysqli calls a stored procedure, the incoming mysql is always garbled. (Attached are the operation process, details and screenshots)

Business scenario: The php page calls the mysql stored procedure, which has 1 input parameter and 1 output parameter.
Problem: It can be executed normally, but garbled characters are always displayed after entering the parameters into the database.

PHP page code is as follows

<head>
<meta charset="utf-8">
</head>
<?php
$conn = new MySQLi("数据库地址","数据库用户","密码","数据库名");
mysqli_query($conn,"SET NAMES utf8");
$info_name_cn='测试x201';
$info_name_cn=mb_convert_encoding($info_name_cn,'UTF-8');
$result=$conn->query("CALL x2('$info_name_cn',@exeout_rows)");
$result=$conn->query("SELECT @exeout_rows");
$recordset=mysqli_fetch_assoc($result);
$exeout_rows=(int)$recordset["@exeout_rows"];
?>
<div>---|<?php echo $exeout_rows; ?>|<?php echo mb_detect_encoding($exeout_rows); ?>|---</div>


mysql stored procedure code is as follows

CREATE DEFINER=`数据库名`@`%` PROCEDURE `x2`(
IN exein_info_name_cn VARCHAR(5)
,OUT exeout_rows int
)
BEGIN
        set exeout_rows=1;
         
        insert into 测试表
        (
        info_name_cn
        )
        values
        (
        exein_info_name_cn
        );
END

In mysql database, the option of the test table is "utf8/ utf8_ganaral_ci", the character set and sorting order of the internal field info_name_cn in the test table are "utf8/utf8_ganaral_ci"

I tried different conversions to different encoding methods - the following text
but the results were all garbled - the results after execution The following picture

(id is)29: No conversion
//$info_name_cn=mb_convert_encoding($info_name_cn,'UTF-8');

(id is)30:Convert to UTF-8
$info_name_cn=mb_convert_encoding($info_name_cn,'UTF-8');

(id is)31: Convert to GB2312
$info_name_cn=mb_convert_encoding($info_name_cn,'GB2312' );

(id is)32:convert to GBK
$info_name_cn=mb_convert_encoding($info_name_cn,'GBK');

(id is)33:convert to BIG5
$info_name_cn=mb_convert_encoding($info_name_cn,'BIG5');

(id is)34: Convert to ASCII
$info_name_cn=mb_convert_encoding($info_name_cn,'ASCII');

1.png


In addition, run the following code to add Chinese to the database normally

<?php
$link = @mysql_connect("数据库地址","数据库用户名","数据库密码")
or die("连接失败" .mysql_error());
@mysql_select_db("数据表") or die("连接失败".mysql_error);
function insert(){
mysql_query("set names utf8");
$sqlinsert = "insert into 测试表(info_name_cn) values('李四')";
$resultinsert = mysql_query($sqlinsert);
if($resultinsert){
echo "insert data success";
}else{
echo "insert data fail".mysql_error();
}
}
insert();
mysql_close($link);
?>

2.png

**越狱兔**越狱兔1951 days ago1170

reply all(3)I'll reply

  • innocence

    innocence2019-07-27 11:41:55

    *.php file encoding problem

    reply
    1
  • **越狱兔

    Yes, thank you very much. It's done now. I feel that it is directly using Notepad++ to copy and paste, causing encoding problems in the .php file. I’ll leave the operation process for what’s coming later (I’m a novice, please don’t mind me if you’re a master) 1. Create a new abc.txt file, save the file in utf8 format, change the name to abc.php and open it. 2. Open the previously written code in txt notepad, copy and paste it into the abc.php file. Save and close. 3. Use the software Notepad++ to open abc.php, and select UTF-8 for the "Encoding" item (to avoid BOM headers).

    **越狱兔 · 2019-07-27 15:12:50
  • **越狱兔

    **越狱兔2019-07-22 17:03:00

    Supplement: In the same situation, I changed the file saving encoding method to ASCI, and the page code ran normally after setting it to GBK.

    reply
    0
  • Cancelreply