MySQL/PHP re-query after update

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
mwdaley
Forum Newbie
Posts: 1
Joined: Tue May 06, 2008 4:16 pm

MySQL/PHP re-query after update

Post 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.
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: MySQL/PHP re-query after update

Post 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.
Post Reply