How can MySQL string functions be utilized in PHP to extract and group hosts for analysis?

To extract and group hosts for analysis using MySQL string functions in PHP, you can use the SUBSTRING_INDEX function to extract the host from a URL and then group the results using the GROUP BY clause in a SQL query. This allows you to analyze data based on the hosts of URLs stored in a database. 

// Connect to MySQL database
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

$conn = new mysqli($servername, $username, $password, $dbname);

// Query to extract and group hosts
$sql = "SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(url, '/', 3), '//', -1) AS host, COUNT(*) AS count FROM table_name GROUP BY host";

$result = $conn->query($sql);

// Output results
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "Host: " . $row["host"]. " - Count: " . $row["count"]. "<br>";
    }
} else {
    echo "0 results";
}

$conn->close();

Keywords

MySQL string functions PHP extract group hosts

Related Questions