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
    18,933
    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"
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  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



Recent Articles