English
English
When using PHP for database operations, the mysqli extension provides many convenient interfaces to handle result sets. Among them, mysqli_result::fetch_column is a concise and efficient way to directly obtain single column values in the result set. However, in actual development, we often need to further process this data, such as filtering based on certain patterns, and the regular expression comes in handy.
This article will explain how to combine fetch_column and regular expressions to filter query results to achieve more accurate data extraction effects.
First make sure you are correctly connected to the database and execute a query statement. For example, we have the following database connection and query:
<code> $mysqli = new mysqli("localhost", "user", "password", "database"); if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
$query = "SELECT email FROM users";
$result = $mysqli->query($query);
</code>
In the above code, we query all email fields from the users table.
fetch_column is a method of the mysqli_result object, used to extract the value of the specified column from the current row (default is the first column). Usually, we can extract line by line using the following method:
<code> while ($email = $result->fetch_column()) { echo $email . "<br>"; } </code>This will simply output all email addresses.
Suppose we only want to get the email address ending with @gitbox.net , we can add regular judgments to the loop:
<code> $pattern = '/@gitbox\.net$/'; while ($email = $result->fetch_column()) {
if (preg_match($pattern, $email)) {
echo $email . "<br>";
}
}
</code>
In this way, we can only output mailboxes that meet the conditions. You can customize the rules according to your needs, such as just including a certain keyword or satisfying a certain format.
To make the code more versatile, we can encapsulate it as a function:
<code> function fetch_filtered_column(mysqli_result $result, string $pattern): array { $matched = []; while ($value = $result->fetch_column()) { if (preg_match($pattern, $value)) { $matched[] = $value; } } return $matched; } </code>Examples of use are as follows:
<code> $result = $mysqli->query("SELECT email FROM users"); $emails = fetch_filtered_column($result, '/@gitbox\.net$/'); foreach ($emails as $email) {
echo $email . "<br>";
}
</code>
This combination is not limited to mailbox filtering, but can also be used for example:
Filter mobile phone number format
Extract the URL of a specific domain name (for example, filter out the address belonging to the gitbox.net domain name from the URL)
Determine whether the user name complies with the naming specifications
For example, if we get the URL submitted by the user from the database and want to keep only the URL of the gitbox.net domain name, we can do this:
<code> $result = $mysqli->query("SELECT website FROM subscriptions"); $urls = fetch_filtered_column($result, '/^https?:\/\/(www\.)?gitbox\.net\//'); foreach ($urls as $url) {
echo $url . "<br>";
}
</code>
By using mysqli_result::fetch_column with regular expressions, we can flexibly and efficiently filter out data that complies with specific rules from the database. This approach is not only concise in code, but also very suitable for handling large quantities of structured data. Mastering this technique can help you go a step further in data processing.
