/    Sign up×
Community /Pin to ProfileBookmark

PHP/MySQL Query Totaling/Formatting Time?

Copy linkTweet thisAlerts:
Sep 23.2008

[B]2 Questions[/B]

  • 1. I’m stumped. I can’t find this anywhere. I’ve got a time field in my MySQL database (MySQL time format) that I need to total with a query. The database records are songs — so I’m using this field to indicate song length. So using a basic query below, how would I format the times so I could total them up from the query?

    • [code=php]$query = “SELECT musicTime FROM music WHERE …”;
    $result = @mysql_query($query);
    if ($result && @mysql_num_rows($result) > 0) {
    $totalTime = 0;
    while($row = mysql_fetch_array($result)) {
    $totalTime = $totalTime + $row[‘musicTime’];
    }
    print $totalTime;
    }[/code]

    2. In general — when I display times (lengths) anywhere on the site, I never need hours to show. How do I format a time string to remove the hours?

    I currently have 00:03:45 and I want to display 3:45.

    Thank you for your help!

    to post a comment
    PHP

    5 Comments(s) ↴

    Copy linkTweet thisAlerts:
    @lorkanSep 23.2008 — Hi!

    I'm very much for the simple way when it is simple so this is what I would suggest...

    [code=php]substr($string, $start, $length)[/code]

    $start and $length are of course integers.

    See example:

    [code=php]<?php
    $rest = substr("abcdef", -1); // returns "f"
    $rest = substr("abcdef", -2); // returns "ef"
    $rest = substr("abcdef", -3, 1); // returns "d"
    ?>[/code]

    Apply substr to your output and you will get your nice 3:45
    Copy linkTweet thisAlerts:
    @DARTHTAMPONSep 23.2008 — you could also use date("i:s", $yourdate); I believe. Untested but heres some documentation

    http://us3.php.net/date
    Copy linkTweet thisAlerts:
    @msmith29063authorSep 23.2008 — I appreciate the replies, guys. And I'm familiar with substr(). Is there not a better/simpler way to convert the times and add them together?

    BTW, the values in the time field look like 00:03:45, 00:04:08, 00:02:46, etc.
    Copy linkTweet thisAlerts:
    @msmith29063authorSep 23.2008 — Nevermind. I've been advised to convert to seconds and store that number in the database. Now, I'm just converting in and out. Thanks!
    Copy linkTweet thisAlerts:
    @SyCoSep 23.2008 — Nevermind. I've been advised to convert to seconds and store that number in the database. Now, I'm just converting in and out. Thanks![/QUOTE]

    I'll second that. I find PHP's timestamps are easier to work with than SQL's. Lots of PHP functions like Unix time and you can still manipulate them using SQL functions too just treated as integers.
    ×

    Success!

    Help @msmith29063 spread the word by sharing this article on Twitter...

    Tweet This
    Sign in
    Forgot password?
    Sign in with TwitchSign in with GithubCreate Account
    about: ({
    version: 0.1.9 — BETA 5.26,
    whats_new: community page,
    up_next: more Davinci•003 tasks,
    coming_soon: events calendar,
    social: @webDeveloperHQ
    });

    legal: ({
    terms: of use,
    privacy: policy
    });
    changelog: (
    version: 0.1.9,
    notes: added community page

    version: 0.1.8,
    notes: added Davinci•003

    version: 0.1.7,
    notes: upvote answers to bounties

    version: 0.1.6,
    notes: article editor refresh
    )...
    recent_tips: (
    tipper: @AriseFacilitySolutions09,
    tipped: article
    amount: 1000 SATS,

    tipper: @Yussuf4331,
    tipped: article
    amount: 1000 SATS,

    tipper: @darkwebsites540,
    tipped: article
    amount: 10 SATS,
    )...