www.webdeveloper.com
Results 1 to 4 of 4

Thread: mysql time activity table

  1. #1
    Join Date
    Apr 2005
    Location
    Oregon
    Posts
    144

    mysql time activity table

    Hello,
    This is what I have. One MySQL database/table with epoch time stored in one column and another logic number in another column. Looks kinda like this:

    Code:
    epoch	d1
    1391883000	1
    1391883026	0
    1391883476	1
    1391883502	0
    1391883695	1
    1391883700	0
    1391884202	0
    1391884437	1
    What I'd really like to do is be able to display the data in a table, with the left most column displaying the Hour and the rest of that row displaying only the first occurence of the minute. So, if in the hour of 10:00am there is an entry for 01,05,05,05,06,22,23,36,36,36,36 I only want it to look like this:
    HTML Code:
    <table>
    <tr>
    <td>10</td><td>01</td><td>05</td><td>06</td><td>22</td><td>23</td><td>36</td>
    </tr>
    </table>
    But, after that first row, it then goes on to the 11:00am hour and so on. This is the code that I am trying to work with, but I am having very little success. I know there has to be a better way to do this and that this is not the most efficient way to retrieve data, but it's the best I can come up with, and it does not work right... Here it is:

    PHP Code:
    <?php
    $minute 
    '0';
    $hour '0';
    // Make a MySQL Connection
    mysql_connect("localhost""****""****") or die(mysql_error());
    mysql_select_db("****") or die(mysql_error());

    // Get all the data from the "example" table
    $resultHour mysql_query("SELECT * FROM `1`") or die(mysql_error());  

    echo 
    "<table border='1'>";
    // keeps getting the next row until there are no more to get
    while($rowH mysql_fetch_array$resultHour )) { // this is my loop for the hour numbers


    $ehour date('H'$rowH['epoch']);

        
    // Print out the contents of each row into a table
        
            
    if($hour != $ehour){  // if the hour number is a new number, start new row
                
    echo "<tr><td>"
                echo 
    $ehour;
                echo 
    "</td>"

            }
    $hour $ehour;    

    $resultMinute mysql_query("SELECT * FROM `1`") or die(mysql_error()); 
    //echo "<tr>";
        
    while($rowM mysql_fetch_array$resultMinute )) { //This is my loop for the minute numbers


        
    $eminute date('i'$rowM['epoch']);
        
    $emhour date('H'$rowM['epoch']);

            
    // Print out the contents of each row into a table
                
    if($hour == $emhour && $minute != $eminute){ // This should match the minutes to the hour
                    
    echo "<td>"
                    echo 
    $eminute;
                    echo 
    "</td>"
                }
            
            
    $minute eminute;
        
                if(
    $minute == '59'){ // If we get to 59, break for new hour
                    
    break;
                }

         
    ob_flush();
            
    flush();
        }
    echo 
    "</tr>";




    echo 
    "</table>";
    ?>
    Is there a better way to do this?
    Jeremy

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    22,284
    I might use this query:
    Code:
    SELECT
    	`epoch`,
    	HOUR(FROM_UNIXTIME(`epoch`)) AS `hour`,
    	MINUTE(FROM_UNIXTIME(`epoch`)) AS `minute`
    FROM `1`
    ORDER BY epoch
    Then for the display (untested):
    PHP Code:
    echo "<table>\n";
    $hour null;
    $started false;
    while(
    $row mysql_fetch_assoc($result)) {
        if(
    $row['hour'] !== $hour) {
            if(
    $started) {
                echo 
    "</tr>\n";
            }
            else {
                
    $started true;
            }
            echo 
    "<td>{$row['hour']}</td>";
            
    $hour $row['hour'];
        }
        echo 
    "<td>{$row['minute']}</td>";
    }
    echo 
    "</tr>\n</table>\n"
    "Well done....Consciousness to sarcasm in five seconds!" ~ Terry Pratchett, Night Watch

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

    My Blog
    cwrBlog: simple, no-database PHP blogging framework

  3. #3
    Join Date
    Apr 2005
    Location
    Oregon
    Posts
    144
    Wow, that looks so much better and load time is drastically better. I made a change in the php code so that only the first minute was added to the table and duplicates were skipped, like this:
    PHP Code:
    echo "<table border=1>\n";
    $hour null;
    $minute null;
    $started false;
    while(
    $row mysql_fetch_assoc($result)) {
            if(
    $row['hour'] !== $hour) {
                    if(
    $started) {
                            echo 
    "</tr>\n";
                    }
                    else {
                            
    $started true;
                    }
                    echo 
    "<tr><td><b>{$row['hour']}</b></td>";
                    
    $hour $row['hour'];
            }
            
        if(
    $row['minute'] != $minute){
            echo 
    "<td>{$row['minute']}</td>";
            
    $minute $row['minute'];
        }
    }
    echo 
    "</tr>\n</table>\n"
    I had one other question, and it is only cosmetics. This is what the table looks like now:
    Code:
    10	10	17	18	21	30	33	34	39	40	41	42	43	44	45	48	49	50	51	52	53	54	57
    11	0	1	2	3	4	5	6	13	14	15	16	17	19	20	21	22	23	24	25	26	28	29	30	31	35	36	37	38	39	42	45	46	47	53	54	55	56
    12	0	2	3	4	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	25	26	27	28	29	30	31	33	34	35	36	37	38	39	40	41	42	43	44	45	46	47	48	49	50	51	52	53	54	55	56	57	58	59
    13	0	1	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36	37	38	39	40	41	42	43	44	45	46	47	48	49	50	51	52	53	54	55	56	57	58	59
    14	0	1	2	3	5	6	7	8	9	10	11	12	13	14	15	18	23	24	26	27	29	30	33	34	39	40	41	42	43	44	45	47	48	49	50	52	53	54	55	56	57	58	59
    15	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	33	34	35	36	37	38	39	40	41	42	43	45	52	53
    16	0	15	16	17	18	20	21	22	23	24	25	26	28	29	30	31	32	33	34	35	36	37	43	44	45	46	47	48	49	50	51	52	53	54	55	56	57	58	59
    17	0	3	4	7	8	9	11	12	13	15	16	17	20	21	24	25	26	27	28	30	31	32	33	36	38	40	42	43	44	45	46	47	48	49	50	51	52	53	54	55	56	57	58	59
    18	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	28	30	31	32	34	35	42	43	44	45	47	48	49	50	51	52	53	54	55	59
    19	0	1	2	3	4	6	7	10	12	15	19	20	21	24	25	26	27	29	30	31	32	33	35	36	45	48	50	51	52	56	57	58	59
    20	0	3	10	12	13	15	19	20	21	23	24	25	27	28	30	33	35	45	47	48	52	55
    21	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	18	23	24	25	26	27	30	34	36	37	38	40	41	42	43	44	45	49
    22	0	1	2	4	11	12	13	14	15	16	17	18	21	22	23	24	25	26	27	28	29	30	31	33	34	37	38	41	43	44	45	46	47	48	50	52	53	54	55	56	57	58	59
    23	0	1	2	4	5	6	10	11	12	13	15	17	18	20	21	22	27	28	29	30	36	37	38	43	45	46	48	53	56	58	59
    0	0	1	2	3	4	5	11	12	14	15	18	19	22	24	30	33	34	45	47	49
    1	0	1	2	3	12	15	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	45
    2	0	15	30	45
    3	0	15	30	45
    4	0	15	30	45
    5	0	15	30	41	42	43	45	47	49	52	59
    6	0	1	13	15	18
    My question is this, is it possible to add buffer cells? So it looks like this:
    Code:
    10	*	*	*	*	*	*	*	*	*	*	10	*	*	*	*	*	*	17	18
    11	0	1	2	3	4	5	6	*	*	*	*	*	*	13	14	15	16	17	*
    12	0	*	2	3	4	*	*	*	8	9	10	11	12	13	14	15	16	17	18
    13	0	1	*	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18
    14	0	1	2	3	*	5	6	7	8	9	10	11	12	13	14	15	*	*	18
    15	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18
    16	0	*	*	*	*	*	*	*	*	*	*	*	*	*	*	15	16	17	18
    17	0	*	*	3	4	*	*	7	8	9	*	11	12	13	*	15	16	17	*
    18	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18
    19	0	1	2	3	4	*	6	7	*	*	10	*	12	*	*	15	*	*	*
    20	0	*	*	3	*	*	*	*	*	*	10	*	12	13	*	15	*	*	*
    21	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	*	*	18
    22	0	1	2	*	4	*	*	*	*	*	*	11	12	13	14	15	16	17	18
    23	0	1	2	*	4	5	6	*	*	*	10	11	12	13	*	15	*	17	18
    0	0	1	2	3	4	5	*	*	*	*	*	11	12	*	14	15	*	*	18
    1	0	1	2	3	*	*	*	*	*	*	*	*	12	*	*	15	*	17	18
    2	0	*	*	*	*	*	*	*	*	*	*	*	*	*	*	15	*	*	*
    3	0	*	*	*	*	*	*	*	*	*	*	*	*	*	*	15	*	*	*
    4	0	*	*	*	*	*	*	*	*	*	*	*	*	*	*	15	*	*	*
    5	0	*	*	*	*	*	*	*	*	*	*	*	*	*	*	15	*	*	*
    6	0	1	*	*	*	*	*	*	*	*	*	*	*	13	*	15	*	*	18
    I understand if it's not able to do that, but thanks for your help!
    Jeremy

  4. #4
    Join Date
    Apr 2005
    Location
    Oregon
    Posts
    144
    OK, this is what I have now.
    PHP Code:
    while($row mysql_fetch_assoc($result)) {
            if(
    $row['hour'] !== $hour) {
                    if(
    $started) {
                            echo 
    "</tr>\n";
                    }
                    else {
                            
    $started true;
                    }
                    echo 
    "<tr><td><b>{$row['hour']}</b></td>";
                    
    $hour $row['hour'];
            }
            
        while(
    $nextMinute $row['minute']){
            echo 
    "<td>*</td>";
            
    $nextMinute++;

        }

        if(
    $row['minute'] != $minute){
            echo 
    "<td>{$row['minute']}</td>";
            
    $minute $row['minute'];
            
    $nextMinute $row['minute'] + 1;
        }

    Which outputs this:

    Code:
    10	*	*	*	*	*	*	*	*	*	*	10	*	*	*	*	*	*	17	18	*	*	21	*	*	*	*	*	*	*	*	30	*	*	33	34	*	*	*	*	39	40	41	42	43	44	45	*	*	48	49	50	51	52	53	54	*	*	57
    11	0	1	2	3	4	5	6	*	*	*	*	*	*	13	14	15	16	17	*	19	20	21	22	23	24	25	26	*	28	29	30	31	*	*	*	35	36	37	38	39	*	*	42	*	*	45	46	47	*	*	*	*	*	53	54	55	56
    12	0	*	2	3	4	*	*	*	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	*	*	25	26	27	28	29	30	31	*	33	34	35	36	37	38	39	40	41	42	43	44	45	46	47	48	49	50	51	52	53	54	55	56	57	58	59
    13	0	1	*	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36	37	38	39	40	41	42	43	44	45	46	47	48	49	50	51	52	53	54	55	56	57	58	59
    14	0	1	2	3	*	5	6	7	8	9	10	11	12	13	14	15	*	*	18	*	*	*	*	23	24	*	26	27	*	29	30	*	*	33	34	*	*	*	*	39	40	41	42	43	44	45	*	47	48	49	50	*	52	53	54	55	56	57	58	59
    15	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	*	*	33	34	35	36	37	38	39	40	41	42	43	*	45	*	*	*	*	*	*	52	53
    16	0	*	*	*	*	*	*	*	*	*	*	*	*	*	*	15	16	17	18	*	20	21	22	23	24	25	26	*	28	29	30	31	32	33	34	35	36	37	*	*	*	*	*	43	44	45	46	47	48	49	50	51	52	53	54	55	56	57	58	59
    17	0	*	*	3	4	*	*	7	8	9	*	11	12	13	*	15	16	17	*	*	20	21	*	*	24	25	26	27	28	*	30	31	32	33	*	*	36	*	38	*	40	*	42	43	44	45	46	47	48	49	50	51	52	53	54	55	56	57	58	59
    18	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	*	*	*	28	*	30	31	32	*	34	35	*	*	*	*	*	*	42	43	44	45	*	47	48	49	50	51	52	53	54	55	*	*	*	59
    19	0	1	2	3	4	*	6	7	*	*	10	*	12	*	*	15	*	*	*	19	20	21	*	*	24	25	26	27	*	29	30	31	32	33	*	35	36	*	*	*	*	*	*	*	*	45	*	*	48	*	50	51	52	*	*	*	56	57	58	59
    20	0	*	*	3	*	*	*	*	*	*	10	*	12	13	*	15	*	*	*	19	20	21	*	23	24	25	*	27	28	*	30	*	*	33	*	35	*	*	*	*	*	*	*	*	*	45	*	47	48	*	*	*	52	*	*	55
    21	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	*	*	18	*	*	*	*	23	24	25	26	27	*	*	30	*	*	*	34	*	36	37	38	*	40	41	42	43	44	45	*	*	*	49
    22	0	1	2	*	4	*	*	*	*	*	*	11	12	13	14	15	16	17	18	*	*	21	22	23	24	25	26	27	28	29	30	31	*	33	34	*	*	37	38	*	*	41	*	43	44	45	46	47	48	*	50	*	52	53	54	55	56	57	58	59
    23	0	1	2	*	4	5	6	*	*	*	10	11	12	13	*	15	*	17	18	*	20	21	22	*	*	*	*	27	28	29	30	*	*	*	*	*	36	37	38	*	*	*	*	43	*	45	46	*	48	*	*	*	*	53	*	*	56	*	58	59
    0	0	1	2	3	4	5	*	*	*	*	*	11	12	*	14	15	*	*	18	19	*	*	22	*	24	*	*	*	*	*	30	*	*	33	34	*	*	*	*	*	*	*	*	*	*	45	*	47	*	49
    1	0	1	2	3	*	*	*	*	*	*	*	*	12	*	*	15	*	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	*	*	*	*	*	*	*	*	*	*	*	*	*	45
    2	0	*	*	*	*	*	*	*	*	*	*	*	*	*	*	15	*	*	*	*	*	*	*	*	*	*	*	*	*	*	30	*	*	*	*	*	*	*	*	*	*	*	*	*	*	45
    3	0	*	*	*	*	*	*	*	*	*	*	*	*	*	*	15	*	*	*	*	*	*	*	*	*	*	*	*	*	*	30	*	*	*	*	*	*	*	*	*	*	*	*	*	*	45
    4	0	*	*	*	*	*	*	*	*	*	*	*	*	*	*	15	*	*	*	*	*	*	*	*	*	*	*	*	*	*	30	*	*	*	*	*	*	*	*	*	*	*	*	*	*	45
    5	0	*	*	*	*	*	*	*	*	*	*	*	*	*	*	15	*	*	*	*	*	*	*	*	*	*	*	*	*	*	30	*	*	*	*	*	*	*	*	*	*	41	42	43	*	45	*	47	*	49	*	*	52	*	*	*	*	*	*	59
    6	0	1	*	*	*	*	*	*	*	*	*	*	*	13	*	15	*	*	18	*	*	*	*	*	*	*	*	*	*	*	30	*	32	*	*	*	*	*	*	*	40	*	*	43	*	45	*	47	48	49	*	51	52	53	54	*	*	57	*	59
    7	0	1	*	3	4	5	6	*	*	9	10	11	*	*	14	15	16	*	*	*	*	*	*	*	*	*	*	*	*	*	30	*	*	*	*	*	*	*	38	39	40	41	42	*	*	45	*	*	48	49	50	51	52
    8	0	*	*	*	*	5	6	*	*	*	10	11	12	13	14	15	*	*	*	*	*	21	*	*	*	25	*	27	28	*	30	*	*	33	34	*	*	*	38	39	40	*	42	43	*	45	*	47	*	*	*	51	*	53	54	55	56	57	58	59
    9	0	*	2	3	4	*	*	7	*	*	*	*	*	*	*	15	*	17	*	*	*	*	*	*	24	25	26	27	*	*	30
    I'm not as concerned with the trailing minutes being empty as I was with the minute columns not lining up vertically. But, if there is a way, I'd be interested... Thank you for your help.
    Jeremy

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center

"

"

X vBulletin 4.2.2 Debug Information

  • Page Generation 0.35551 seconds
  • Memory Usage 2,940KB
  • Queries Executed 15 (?)
More Information
Template Usage (35):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (5)bbcode_code
  • (1)bbcode_html
  • (4)bbcode_php
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (4)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (1)navbar_moderation
  • (1)navbar_noticebit
  • (1)navbar_tabs
  • (2)option
  • (4)postbit
  • (4)postbit_onlinestatus
  • (4)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (26):
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/functions_navigation.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./includes/functions_facebook.php
  • ./includes/functions_calendar.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_notice.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php 

Hooks Called (70):
  • init_startup
  • friendlyurl_resolve_class
  • init_startup_session_setup_start
  • database_pre_fetch_array
  • database_post_fetch_array
  • init_startup_session_setup_complete
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • load_show_variables
  • load_forum_show_variables
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • strip_bbcode
  • friendlyurl_clean_fragment
  • friendlyurl_geturl
  • forumjump
  • cache_templates
  • cache_templates_process
  • template_register_var
  • template_render_output
  • fetch_template_start
  • fetch_template_complete
  • parse_templates
  • fetch_musername
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • bbcode_parse_start
  • postbit_imicons
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • build_navigation_data
  • build_navigation_array
  • check_navigation_permission
  • process_navigation_links_start
  • process_navigation_links_complete
  • set_navigation_menu_element
  • build_navigation_menudata
  • build_navigation_listdata
  • build_navigation_list
  • set_navigation_tab_main
  • set_navigation_tab_fallback
  • navigation_tab_complete
  • fb_like_button
  • showthread_complete
  • page_templates