Page 1 of 1

MySQL/PHP re-query after update

Posted: Tue May 06, 2008 4:26 pm
by mwdaley
I have a page that provides a dynamically created checklist. I have the code written such that when an item is checked, the table is updated and the list is supposed to be recreated without the items that were checked. Unfortunately, the only way I can get the items to leave the list is to manually refresh the page. I presume I'm missing something in the timing of how the page is refreshed, or the fact that I'm using a POST method on my form. I need a little direction. Here is the code I've written:

Code: Select all

<html>
 <head>
  <title>SMTNW Maintenance Tracker</title>
 </head>
 <body>
 <?php
    $currdate = getdate();
    $datestring = date($currdate['year']."-".$currdate['mon']."-".$currdate['mday']);
    $qtr_array = array('January','April','July','October');
    $username="user";
    $password="password";
    $database="maintenance";
    $frequency="('daily'";
    mysql_connect('localhost',$username,$password);
    @mysql_select_db($database) or die("Unable to connect to $database");
 
    if ($currdate['wday'] == 1)
        $frequency = $frequency . ",'weekly'";
    
 
    if ($currdate['mday'] > 29 || $currdate['mday'] < 4){
        if (in_array($currdate['month'],$qtr_array)){
            $frequency = $frequency . ",'quarterly'";
        }
        $frequency = $frequency . ",'monthly'";
    }
 
    if ($currdate['month'] = "May"){
        if ($currdate['mday'] == 5){
            $frequency = $frequency . ",'annually'";
        }
    }
 
    $frequency = $frequency . ")";
 
    $query = "SELECT m.name, i.desc, i.code FROM schedule s, items i, machines m where s.item = i.code and s.machine = m.ser_no and i.freq in $frequency and 0 < (select count(*) from history h where date = '$datestring' and h.machine = m.name and h.code = i.code and h.complete is null)";
    $result = mysql_query($query);
    $numrows = mysql_numrows($result);
 
    echo "<form action=\"show_schedule.php\" method=\"post\">";
    $i = 0;
    while ($i < $numrows){
        $NAME = mysql_result($result,$i,"NAME");
        $DESC = mysql_result($result,$i,"DESC");
        $CODE = mysql_result($result,$i,"CODE");
        echo "$NAME<br>$DESC<br><input type=\"checkbox\" name=$CODE value=\"Y\">Completed<hr>";
        $query = "INSERT INTO history VALUES ('$datestring','$NAME','$CODE')";
        mysql_query($query);
        $i++;
    }
 
    mysql_close();
 ?>
  <input type="Submit" value="Clear checked items">
 </form><br>
 <a href='index.php'>Home</a>
 
 <?php
 
    $username="user";
    $password="password";
    $database="maintenance";
    mysql_connect('localhost',$username,$password);
    @mysql_select_db($database) or die("Unable to connect to $database");
 
    $query = "SELECT m.name, i.desc, i.code FROM schedule s, items i, machines m where s.item = i.code and s.machine = m.ser_no and i.freq in $frequency and 0 < (select count(*) from history h where date = '$datestring' and h.machine = m.name and h.code = i.code and h.complete is null)";
    $result = mysql_query($query);
    $numrows = mysql_numrows($result);
 
    $i = 0;
    while ($i < $numrows){
        $CODE = mysql_result($result,$i,"CODE");
        $CHECK = $_POST[$CODE];
        $NAME = mysql_result($result,$i,"NAME");
        if ($CHECK == "Y"){
            $query = "UPDATE history SET complete = 'Y' WHERE date = '$datestring' AND code = '$CODE' AND machine = '$NAME'";
            mysql_query($query);
        }
        $i++;
    }
    mysql_close();
 ?>
 
 </body>
</html>
I apologize if I'm violating any code conventions. I haven't exactly cleaned this code up or anything. I don't know if I've provided enough information, but this strikes me as being somewhere in the PHP/HTML category, since my tables are updating correctly. I appreciate any insight.

Re: MySQL/PHP re-query after update

Posted: Thu May 08, 2008 5:17 pm
by Jade
If you put each item in a div layer with an identical item ID then you could always use javascript to hide the div layers as they're being checked. Then you won't have to refresh the page to remove them.