PHP/live Update Question...

Discussion in 'OT Technology' started by venger240, Jul 11, 2006.

  1. venger240

    venger240 Hoemoe

    Joined:
    Oct 20, 2003
    Messages:
    2,938
    Likes Received:
    0
    Location:
    Nashville, TN
    Ok this is weird I know...

    At work I have setup toolbars across everybodies computers. These toolbars link to a php page that refreshes every 60 seconds. This php page pulls from a mysql database. If a manager has an update to announce, he/she will enter it into the database. The php page is setup on a meta timer to refresh every 60 seconds...

    My question is, if I want to change the color of the text from gray to red, whenver a new update comes in and then back to gray after the next refresh, how would I do that? I will post the current code for you to see how it works..

    Code:
    <html>
    <head>
    <LINK REL="StyleSheet" HREF="style.css" TYPE="text/css">
    <title></title>
    <meta http-equiv="refresh" content="60">
    </head>
    <body bgcolor="#000000" style="overflow-x:hidden;overflow-y:hidden;" topmargin="3">
    <?php
    
    $db_user = '*****'; 
    $db_pass = '*****'; 
    $db_host = '*****'; 
    $db_name = '*****'; 
    mysql_connect($db_host,$db_user,$db_pass) or die(mysql_error()); 
    mysql_select_db($db_name);
    
    $sql = "select * from news";
    $ret = mysql_query($sql) or die(mysql_error());
    
    ?>
    <table border="0" width="100%" cellpadding="0" cellspacing="0">
    	<tr valign="top">
    		<td width="100%">
    			<font class="headlines2">
    			<?php while($row = mysql_fetch_assoc($ret)) { ?>
    				* <?php echo $row['headline1']; ?><br>
    				* <?php echo $row['headline2']; ?><br>
    				* <?php echo $row['headline3']; ?><br>
    				* <?php echo $row['headline4']; ?></font>
    		</td>
    <?php } ?> 
    So basically if a new item is entered into $row['headline1'], I want it to be red for 60 seconds until the page refreshes again, and then it will be gray...

    Any ideas?
     
  2. piratepenguin

    piratepenguin New Member

    Joined:
    Jun 18, 2006
    Messages:
    1,067
    Likes Received:
    0
    Location:
    Ireland
    you could store the 'age' of the row in the database and if it's older than 60s it's grey, otherwise it's red.
     
  3. venger240

    venger240 Hoemoe

    Joined:
    Oct 20, 2003
    Messages:
    2,938
    Likes Received:
    0
    Location:
    Nashville, TN

    Thats brilliant! So would I have the database store a CURTIME()?

    I guess I will get confused with the calculations, but I could write an if else statement, and if the time stored is less than 60 seconds away from the current time, then its red, else its gray... Would htat work? Any better suggestions?
     
  4. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    why base this on 60-second refreshes? Why not display the most-recent update in red, and subsequent in gray... I assume red is to "catch attention" and that cannot be guaranteed in only a 60-second window.


    you don't need to use the time, and you don't have to adjust the schema. Simply use a SQL query that calls the records in order of last-updated (primary keys in high-to-low order) then display the first results in red, then the rest in grey. done.
     
  5. piratepenguin

    piratepenguin New Member

    Joined:
    Jun 18, 2006
    Messages:
    1,067
    Likes Received:
    0
    Location:
    Ireland
    yea that'd work, that's what I'd do, I can't think of an alternative.

    btw, I didn't mean to say 'age', I should've said 'a timestamp'. Then you can calculate the age by comparing it to the current time, which is what you're saying anyhow.

    might wanna consider doing it jolly's way too.
     
  6. piratepenguin

    piratepenguin New Member

    Joined:
    Jun 18, 2006
    Messages:
    1,067
    Likes Received:
    0
    Location:
    Ireland
    and if you do store the current time in the database it'd be handier to use a UNIX_TIMESTAMP() (if you use MySQL) - then you can just subtract from PHP's time() and compare the result to 60
     
  7. venger240

    venger240 Hoemoe

    Joined:
    Oct 20, 2003
    Messages:
    2,938
    Likes Received:
    0
    Location:
    Nashville, TN

    Sometimes multiple lines are updated at once... This is going to be used to catch attention, but what we have now is nobody noticing it. I want something to change the color and then to change it one more time when a update is posted. Sometimes updates come every 10 minutes, sometimes every 5 hours. I dont want something staying red for 5 hours...

    Im a little confused on how to write the code to subtract current time from a stored time. Im looking for help on the php website, but its not htat useful. Ugh.
     
  8. piratepenguin

    piratepenguin New Member

    Joined:
    Jun 18, 2006
    Messages:
    1,067
    Likes Received:
    0
    Location:
    Ireland
    on the table in the database, create a field of type DATETIME with the default value set to CURRENT_TIMESTAMP, call it 'timestamp'. Then, change the SELECT query to:
    Code:
    select headline1, headline2, headline3, headline4, UNIX_TIMESTAMP(timestamp) as timestamp from news
    then...
    Code:
    if (time () - $row['timestamp'] > 60) {
    // use black
    } else {
    // use red
    }
    http://simon.incutio.com/archive/2003/07/11/storingDatesInMySQL
     
  9. venger240

    venger240 Hoemoe

    Joined:
    Oct 20, 2003
    Messages:
    2,938
    Likes Received:
    0
    Location:
    Nashville, TN
    Will this automatically update whenever the field is updated? I am only updating the field whenever something new is entered...
     
  10. piratepenguin

    piratepenguin New Member

    Joined:
    Jun 18, 2006
    Messages:
    1,067
    Likes Received:
    0
    Location:
    Ireland
    http://www.databasejournal.com/features/mysql/article.php/3577131

    search that page for 'attribute' and read that first paragraph and the code above it.

    Using "on update", at least in MySQL 5, you can set it up so the timestamp field is updated with the CURRENT_TIMESTAMP everytime another field is updated.
     
  11. venger240

    venger240 Hoemoe

    Joined:
    Oct 20, 2003
    Messages:
    2,938
    Likes Received:
    0
    Location:
    Nashville, TN
    Im a newb at this, so when I go to add my timestamp field in, would it look like this..

    Code:
    
    $sql = "alter table news add (timestamp DATETIME(CURRENT_TIMESTAMP, on update CURRENT_TIMESTAMP));"; 
    
    mysql_query($sql) or die(mysql_error()); 
    ??
     
  12. piratepenguin

    piratepenguin New Member

    Joined:
    Jun 18, 2006
    Messages:
    1,067
    Likes Received:
    0
    Location:
    Ireland
    you only need to add the timestamp field once......................
    surely you can use your mysql client to do it?
     
  13. venger240

    venger240 Hoemoe

    Joined:
    Oct 20, 2003
    Messages:
    2,938
    Likes Received:
    0
    Location:
    Nashville, TN

    I dont have access to it... Only one guy does and he isnt letting anyone have access to it...
     
  14. piratepenguin

    piratepenguin New Member

    Joined:
    Jun 18, 2006
    Messages:
    1,067
    Likes Received:
    0
    Location:
    Ireland
    I think it should be something like:

    $sql = "alter table news add timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";

    but I am no pro at this stuff
     
  15. CyberBullets

    CyberBullets I reach to the sky, and call out your name. If I c

    Joined:
    Nov 13, 2001
    Messages:
    11,865
    Likes Received:
    0
    Location:
    BC, Canada/Stockholm, Sweden
    if you are able to query the database, write your alter script in php, and execute it. hopefully the username's permissions are not locked down
     
  16. venger240

    venger240 Hoemoe

    Joined:
    Oct 20, 2003
    Messages:
    2,938
    Likes Received:
    0
    Location:
    Nashville, TN

    I ran into another problem using your method... If I do it like that, the table will not know which record was updated, only that a record was updated. So what I have to do is make 4 time fields, and update each time field individually, whenver that field is updated. I dont think that will work with my current setup. Argh.
     
  17. piratepenguin

    piratepenguin New Member

    Joined:
    Jun 18, 2006
    Messages:
    1,067
    Likes Received:
    0
    Location:
    Ireland
    no, if you add a 'timestamp' field to the table then each record - all of them - will have a timestamp that you can calculate the age of and make it black/red depending on whether it has been updated/created within the last 60 seconds or not...
     
  18. venger240

    venger240 Hoemoe

    Joined:
    Oct 20, 2003
    Messages:
    2,938
    Likes Received:
    0
    Location:
    Nashville, TN

    Ok I didnt know that. I just found out why that wouldnt work though... The page that I use to update the headlines, is a simple page with text boxes. Each text box pulls from the database what is currently on the headline bar. If the manager wants to make a change, they clear out whats in the headline bar and replace it with whatever they want it to say. They hit submit. When they submit it, the database updates every field...
     
  19. piratepenguin

    piratepenguin New Member

    Joined:
    Jun 18, 2006
    Messages:
    1,067
    Likes Received:
    0
    Location:
    Ireland
    Oh... why don't you have a seperate table in the database for the headlines? One row for each headline...

    unless different people get different headlines or something, it'd be much handier when it comes to adding more info e.g. a timestamp ;)
     
  20. venger240

    venger240 Hoemoe

    Joined:
    Oct 20, 2003
    Messages:
    2,938
    Likes Received:
    0
    Location:
    Nashville, TN

    Well I do, but what im saying is everytime there is an update, every row is updated. Imagine 4 Text boxes on a page and a submit button. Each text box already has the headline that was up previously in it. If you want to change text box 3, you clear it out and type what you want. When you hit submit, a php script is setup to update headline1, 2, 3, 4, with whatever information is in the text boxes. Even though you only put new stuff into 3, the other 3 still contain the last information so they are basically updating themselves.
     
  21. venger240

    venger240 Hoemoe

    Joined:
    Oct 20, 2003
    Messages:
    2,938
    Likes Received:
    0
    Location:
    Nashville, TN
    Ok, I think I have a way of updating each one individually. Are we sure that this timestamp thing will work?
     
  22. venger240

    venger240 Hoemoe

    Joined:
    Oct 20, 2003
    Messages:
    2,938
    Likes Received:
    0
    Location:
    Nashville, TN

    Ok that doesnt work. I think I can get this to work but I need to figure out how to add this to the table from a query...
     
  23. venger240

    venger240 Hoemoe

    Joined:
    Oct 20, 2003
    Messages:
    2,938
    Likes Received:
    0
    Location:
    Nashville, TN
    Ok, so just like I thought, when I use CURRENT_TIMESTAMP it updates every field. The color change script you gave me worked! Thats the good news. It just changed EVERY headline to red, instead of just the one updated... I guess I will have to do timestamp1 2 3 4...
     
  24. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    do not use seperate tables... everything can be in one record.

    Why not use the time() function and store that? It is essentially a counter of seconds since the epoch...
     
  25. venger240

    venger240 Hoemoe

    Joined:
    Oct 20, 2003
    Messages:
    2,938
    Likes Received:
    0
    Location:
    Nashville, TN
    Yeah, i wasnt going to use seperate tables... That would be to much work for this simple thing.... I got it to work!!! So excited. I had no idea how to do this coming into it, now in a few hours (while doing my other duties at my job) I got this thing done. :)

    I had to create 4 seperate DATETIME fields... Each field is only updated whenever the corresponding headline option is updated. When its updated, the current date/time is put into that field. Then on the display end. I just have it subtract the minutes from that time. May not be accurate to the second, but its close enough for me.
     

Share This Page