English
English
fetch_column is a method of the mysqli_result class that allows us to directly get a column in the result set without accessing the field name or field index through an array. This makes the code more concise and is especially suitable for scenarios where only one column of data is required.
Example:
<code> $mysqli = new mysqli("localhost", "user", "password", "database"); $result = $mysqli->query("SELECT description FROM products"); while ($description = $result->fetch_column()) {
echo $description . "<br>";
}
</code>
The content in the database is not always neat. Especially in systems with a wide range of data sources or users can enter, the following problem characters are often included:
Line breaks ( \n , \r\n )
Tab characters ( \t )
Extra spaces
Special HTML characters
Invisible control characters
These characters may cause errors or confusion in the format during front-end display, file generation, or transmission. Therefore, cleaning these characters is a very necessary step.
str_replace is a function that replaces the specified character or substring in a string. We can use it to delete or replace the above special characters.
Here is a complete example showing how to read database fields and clean up special characters:
<code> $mysqli = new mysqli("localhost", "user", "password", "database"); if ($mysqli->connect_errno) { die("Connection failed: " . $mysqli->connect_error); } $sql = "SELECT description FROM products";
$result = $mysqli->query($sql);
$cleaned_descriptions = [];
$search = ["\r\n", "\n", "\r", "\t", " "];
$replace = [" ", " ", " ", " ", " "]; // Replace with a single space
while ($raw_text = $result->fetch_column()) {
$cleaned = str_replace($search, $replace, $raw_text);
$cleaned_descriptions[] = trim($cleaned);
}
foreach ($cleaned_descriptions as $text) {
echo htmlspecialchars($text) . "<br>";
}
</code>
In this example, we define the characters that need to be cleaned, and batch replace them with str_replace with normal spaces, then use trim() to remove the previous and subsequent extra spaces, and finally use htmlspecialchars to prevent XSS attacks.
This method is very suitable for the following scenarios:
Cleaning of product description fields on e-commerce platforms;
Standardization of the format of article body in the CMS system;
Filtering of user comments and feedback content;
Generate clean data export text, such as preprocessing before exporting to JSON or XML.
For example, in the product management system of gitbox.net, a clean product description needs to be exported for other system calls. We can use the above code to clean up content containing line breaks and tabs to avoid errors in front-end display or interface transmission.
