PHPstorm operates mysql database and solves the garbled code problem of phpmyadmin

preface

It can't be said that you have fully understood the logic and problems inside, but the methods you have mastered so far can make the view in browser output and phpmyadmin free of garbled code.

Specific operation

Create a table Worker after linking the database through php code

<?php
$mysql=mysqli_connect("localhost","root","123456","mysqldb");
if(empty($mysql)){
    die("mysql_connet failed:".mysqli_connect_error());
}
echo ("connect to ".mysqli_get_host_info($mysql));

$sql="CREATE TABLE Worker ( EmpID INT AUTO_INCREMENT PRIMARY KEY ,EmpName varchar(50) NOT NULL ,
        DeID INT,Title varchar(50),Salary INT)";
echo "<br/>";
if($mysql->query($sql)===TRUE)
{
    echo "table create sucessful";
}
else
{
    echo "table create error".$mysql->error;
}
//1 insert data into the table

//2 insert data into the table

mysqli_close($mysql);
echo ("<br/>Close database link");

Browser running results

phpmyadmin screenshot

Insert data into a table

$exec="INSERT INTO Worker (EmpName,DeID,Title,Salary) values('Wang Wu',1,'staff member',3500),('Zhao Liu',2,'division manager',6500),('Senior seven',2,'staff member',2500)";
if ($mysql->multi_query($exec) === TRUE) {
    echo "New record inserted successfully";
} else {
    echo "Error: "."<br>" . $mysql->error;
}

An error is reported because the strings do not match (I don't know why, my string in Phpstorm is in utf8 format)

Add a line of code and insert it successfully

mysqli_query($mysql,"SET NAMES utf8");
$exec="INSERT INTO Worker (EmpName,DeID,Title,Salary) values('Wang Wu',1,'staff member',3500),('Zhao Liu',2,'division manager',6500),('Senior seven',2,'staff member',2500)";


phpmyadmin also has no random code

Output the inserted data to the browser, and there is no garbled code

echo "<br/>";
$sql="SELECT EmpName,Title,Salary FROM Worker";//Is this a command line
$result=$mysql->query($sql);
//Cycle the records in the table
while ($row=$result->fetch_row())
{
    print ($row[0]." ".$row[1]." ".$row[2]."<br/>");
}
$result->free();


Now check the encoding format of the Worker table in phpmyadmin, which is actually gb2312

After the table is created through the above method, if you only need to look up the data in the table, you need to add a line of code before looking up

mysqli_query($mysql,"SET NAMES utf9");

Otherwise, Chinese cannot be displayed

other

I don't think I fully understand the above method. This is equivalent to inserting data into the table through php. The encoding format is utf8, but the format of the data table is gb2312, but it can be displayed normally.
I tried to change gb2312 into utf8 in phpmyadmin. In this case, Chinese can be directly inserted into the data table without adding format conversion code in the Php editor, but Chinese garbled code is displayed in phpmyadmin.


Adding data to the table through the following code will cause Chinese garbled code in Phpmyadmin, but it will not appear in the browser

$exec="INSERT INTO Worker (EmpName,DeID,Title,Salary) values('Wang Wu',1,'staff member',3500),('Zhao Liu',2,'division manager',6500),('Senior seven',2,'staff member',2500)";
$sql="SELECT EmpName,Title,Salary FROM Worker";//Is this a command line
$result=$mysql->query($sql);
//Cycle the records in the table
while ($row=$result->fetch_row())
{
    print ($row[0]." ".$row[1]." ".$row[2]."<br/>");
}
$result->free();

Postscript

There are many ways to operate MYSQL's file configuration and database. I think this will be a way once and for all, but I'm not too easy to try to modify files. After exploration and attempt, the above methods don't modify the attributes of database or table, Just declare the encoding format of the inserted data directly before inserting the data. I think it's a more convenient way.
I changed the code to function style, which makes it much more convenient to operate. I don't have to make a large section of comments every time.

<?php
$mysql=mysqli_connect("localhost","root","123456","mysqldb");
if(empty($mysql)){
    die("mysql_connet failed:".mysqli_connect_error());
}
echo ("connect to ".mysqli_get_host_info($mysql));
//

function droptable($mysql)
{
    $sql="DROP TABLE Worker";
    if($mysql->query($sql)===TRUE)
{
    echo "<br/>drop tabble sucessful";
}
else
{
    echo "table drop fail".$mysql->error;
}
}
function creattable($mysql)
{
    $sql="CREATE TABLE Worker ( EmpID INT AUTO_INCREMENT PRIMARY KEY ,EmpName varchar(50) NOT NULL ,
        DeID INT,Title varchar(50),Salary INT)";
echo "<br/>";
if($mysql->query($sql)===TRUE)
{
    echo "table create sucessful<br/>";
}
else
{
    echo "table create error".$mysql->error;
}
}

function insertdata($mysql)
{
    mysqli_query($mysql,"SET NAMES utf8");
$exec="INSERT INTO Worker (EmpName,DeID,Title,Salary) values('Wang Wu',1,'staff member',3500),('Zhao Liu',2,'division manager',6500),('Senior seven',2,'staff member',2500)";
if ($mysql->multi_query($exec) === TRUE) {
    echo "New record inserted successfully";
} else {
    echo "Error: "."<br>" . $mysql->error;
}

}
droptable($mysql);
creattable($mysql);
insertdata($mysql);

mysqli_query($mysql,"SET NAMES utf8");
echo "<br/>";
$sql="SELECT EmpName,Title,Salary FROM Worker";//Is this a command line
$result=$mysql->query($sql);
//Cycle the records in the table
while ($row=$result->fetch_row())
{
    print ($row[0]." ".$row[1]." ".$row[2]."<br/>");
}
$result->free();
mysqli_close($mysql);
echo ("<br/>Close database link");

Keywords: PHP Database MySQL phpMyAdmin

Added by areid on Thu, 17 Feb 2022 23:01:38 +0200