Calculating total character count from URLs in SQL database

Data and Databases
1

I’m stuck on a problem. I need to sum up the lengths of URLs stored in my database. I’ve got the URLs in an array, but I’m having trouble with the loop to add them up. Here’s what I’ve tried:

function sumUrlLengths($userEmail) {
    $query = "SELECT url FROM website_links WHERE email = '$userEmail'";
    $result = db_query($query);
    $urlList = db_fetch_array($result);

    $totalChars = 0;
    foreach ($urlList as $url) {
        $totalChars += strlen($url);
    }

    return $totalChars;
}

It’s not working as expected. Am I missing something obvious? Any help would be great!

2

Your approach is on the right track, but there’s a small issue with how you’re fetching the data. The db_fetch_array() function typically returns one row at a time, not all rows at once. Try this modification:

function sumUrlLengths($userEmail) {
    $query = "SELECT url FROM website_links WHERE email = '$userEmail'";
    $result = db_query($query);
    $totalChars = 0;
    while ($row = db_fetch_array($result)) {
        $totalChars += strlen($row['url']);
    }
    return $totalChars;
}

This should correctly iterate over all URLs for the given email. However, GrowingTree’s suggestion of using SQL for the calculation is indeed more efficient, especially for larger datasets. It’s worth considering that approach for better performance.

3

hey sam, have you considered using SQL to do the counting directly? like: SELECT SUM(LENGTH(url)) AS total_chars FROM website_links WHERE email = ? this way you’re not pulling all the URLs into PHP. might be faster for big datasets. what do you think? have you tried any other approaches?

4

yo sam, have u tried using array_sum() with array_map()? might simplify ur code. like this:

$totalChars = array_sum(array_map(‘strlen’, $urlList));

could make it cleaner. but yeah, the SQL approach others mentioned is probly better for big data. just another option to consider!