PHP/MYSQL: A removed row is still displayed in my page after a reload

Question

I work on a simple webserver on a Raspberry Pi, displaying a MySQL table with two buttons : One (Switch) to inverse a boolean value, and one (Delete) to remove the entire row from the table.

What I do, basically, is a while loop on the MySQL table in which I : 1 : display the row and the buttons in a form, and 2 : check if the buttons have been clicked, and if so I send the query to the database and reload the page.

My problem is very simple : When I click on a "switch" button, the query is sent and the page is refreshed, and the value is correctly modified (the page display the value as it is in the database). But when I click on the other button, the "delete" one, after the page has been reloaded, the row still appears in the table. Only when I manually reload the page, the row disappear.

What I already tried : 1 : Check that the row is removed from the database BEFORE the manual refresh : DONE, and the row is correctly removed. 2 : Wait 500ms after the MySQL query, and then reload the page : DONE, and even when I wait 500ms, when the page reloads there is still the removed row.

Here is the whole code, the MySQL query is at the end :

<form action="logout.php" method="post">
<input type="submit" name="logout" value="logout">
</form>
<b>Alarm Monitoring</b>

<p>Sensors state table. Clicking on "Switch state" will switch the field "state" of the associated mote. </p>
<p>State 1 means that the mote is active, the alarm will be activated if the mote is moved or if someone is detected.</p>
<p>State 0 means that the mote is inactive, the alarm will NOT be activated if the mote is moved or if someone is detected.</p>


<?php
$servername="localhost";
$username="root";
$password="patate";
$dbname="testbase";
$table="matable";

$conn = new mysqli($servername,$username,$password,$dbname);
if ($conn->connect_error) {
        echo "kaput";
        die("Connection failed: " . $conn->connect_error);
}

$sql="SELECT * FROM $table;";
$result=$conn->query($sql); ?>


<table border=0>
    <tr>
        <td>

                <table border=1>
                    <tr>
                        <td width=150>ID</td>
                        <td width=150>Date</td>                        
                        <td width=300>PIR</td>  
                        <td width=150>Accel</td>
                        <td width=100>State</td>
                        <td width=150>Switch</td>
                        <td width=150>Delete</td>
                    </tr>
         <?php
   while($row = $result->fetch_assoc()) : ?>

                    <tr>
                        <td><?php echo $row['id']; ?></td>
                        <td><?php echo $row['date']; ?></td>
                        <td><?php echo $row['pir']; ?></td>
                        <td><?php echo $row['accel']; ?></td>
                        <td><?php echo $row['state']; ?></td>
                        <form name="form1" method="post" action=''>
                        <td><?php echo "<input name='Switch' type='submit' id='Switch' value='Switch state ".$row['id']."'>"; ?></td>
                        <td><?php echo "<input name='Delete' type='submit' id='Delete' value='Delete mote ".$row['id']."'>"; ?></td>
                        </form>
                    </tr>    
        <?php
if($_REQUEST['Delete']=='Delete mote ' . $row['id']) {
   {
        $sql3="DELETE FROM $table WHERE id=".$row['id'].";";
        $result3=$conn->query($sql3);
        header('Location: table.php');
    }
}

if($_REQUEST['Switch']=='Switch state ' . $row['id']) {
    {
        if($row['state']=='1')
        {
                $sql2="UPDATE $table SET state='0' WHERE id=".$row['id'].";";
        }
        else
        {      
                $sql2="UPDATE $table SET state='1' WHERE id=".$row['id'].";";
        }

        $result2=$conn->query($sql2);
        header('Location: table.php');
    }
}

?>

 <?php endwhile; ?>    
</table>            

</table>
</td>
</tr>
</table>

Maybe someone saw this problem before ?

Add a query error handling: `if (!$mysqli->query($sql)) { printf("Errormessage: %s\n", $mysqli->error); }` — ZeroBased_IX, Apr 17 '15 at 11:32
I added it and tested but no error has been displayed. Anyway, as the row is correctly removed from the table when I click, I suppose there's no error on the MySQL part. — Tikim, Apr 17 '15 at 11:37
Just throwing another idea in the air, could it potentially be browser caching? — stwalkerster, Apr 17 '15 at 11:41
Maybe the browser caching, I thought about it but what can I do ? Can I clear it in the code, or force a full reload ? — Tikim, Apr 17 '15 at 11:54

Shashikumar Misal · Answer 1 · 2015-04-17T12:40:39.620

Just try cleaning the result array after the deletion query but before redirecting by using: unset($row); & also clean $result by initiating it: $result='';

Modified the code as below & added four lines into it on top two & at the bottom two with comments:

    <form action="logout.php" method="post">
<input type="submit" name="logout" value="logout">
</form>
<b>Alarm Monitoring</b>

<p>Sensors state table. Clicking on "Switch state" will switch the field "state" of the associated mote. </p>
<p>State 1 means that the mote is active, the alarm will be activated if the mote is moved or if someone is detected.</p>
<p>State 0 means that the mote is inactive, the alarm will NOT be activated if the mote is moved or if someone is detected.</p>


<?php
$servername="localhost";
$username="root";
$password="patate";
$dbname="testbase";
$table="matable";

$conn = new mysqli($servername,$username,$password,$dbname);
if ($conn->connect_error) {
        echo "kaput";
        die("Connection failed: " . $conn->connect_error);
}
$row=array();                     //Initialize the empty array (By shashi).
$result='';                       //Initialize the empty result var (By shashi).

$sql="SELECT * FROM $table;";
$result=$conn->query($sql); ?>


<table border=0>
    <tr>
        <td>

                <table border=1>
                    <tr>
                        <td width=150>ID</td>
                        <td width=150>Date</td>                        
                        <td width=300>PIR</td>  
                        <td width=150>Accel</td>
                        <td width=100>State</td>
                        <td width=150>Switch</td>
                        <td width=150>Delete</td>
                    </tr>
         <?php
   while($row = $result->fetch_assoc()) : ?>

                    <tr>
                        <td><?php echo $row['id']; ?></td>
                        <td><?php echo $row['date']; ?></td>
                        <td><?php echo $row['pir']; ?></td>
                        <td><?php echo $row['accel']; ?></td>
                        <td><?php echo $row['state']; ?></td>
                        <form name="form1" method="post" action=''>
                        <td><?php echo "<input name='Switch' type='submit' id='Switch' value='Switch state ".$row['id']."'>"; ?></td>
                        <td><?php echo "<input name='Delete' type='submit' id='Delete' value='Delete mote ".$row['id']."'>"; ?></td>
                        </form>
                    </tr>    
        <?php
if($_REQUEST['Delete']=='Delete mote ' . $row['id']) {
   {
        $sql3="DELETE FROM $table WHERE id=".$row['id'].";";
        $result3=$conn->query($sql3);
        unset($row);                  //Clear the array (By shashi).
        result='';                    //Clear result Var (By shashi).
        header('Location: table.php');
    }
}

if($_REQUEST['Switch']=='Switch state ' . $row['id']) {
    {
        if($row['state']=='1')
        {
                $sql2="UPDATE $table SET state='0' WHERE id=".$row['id'].";";
        }
        else
        {      
                $sql2="UPDATE $table SET state='1' WHERE id=".$row['id'].";";
        }

        $result2=$conn->query($sql2);
        header('Location: table.php');
    }
}

?>

 <?php endwhile; ?>    
</table>            

</table>
</td>
</tr>
</table>

I tried that but it didn't change the result. By the way, aren't all the variables cleared when I reload the page ? — Tikim, Apr 17 '15 at 11:49
I changed that part with your advice : if (!$conn->query($sql)) { printf("Errormessage: %s\n", $conn->error); } unset($row); $result=''; mysql_close($conn); Now, when I click on the "delete" button the row isn't removed, but all the rows that come after aren't displayed. This has probably a lot of meaning but I can't understand it... — Tikim, Apr 17 '15 at 12:14
Check the modified code posted in my answer & check whether it works or not, I have added my lines with comments. — Shashikumar Misal, Apr 17 '15 at 12:42
I tried it but nothing is displayed on the page, which let me think there's a PHP error somehwere (synthax I guess), I'll look for it. Thanks anyway — Tikim, Apr 17 '15 at 12:48

user3284566 · Answer 2 · 2015-04-17T13:19:09.190

$sql="SELECT * FROM $table;"

should be after

$sql3="DELETE FROM $table WHERE id=".$row['id'].";";

and ather actions like update ...

looks like its deleted but you see old data :)

<?php
    $servername="localhost";
    $username="root";
    $password="patate";
    $dbname="testbase";
    $table="matable";

    $conn = new mysqli($servername,$username,$password,$dbname);
    if ($conn->connect_error) {
            echo "kaput";
            die("Connection failed: " . $conn->connect_error);
    }

if($_REQUEST['Delete']) {
   {
        $sql3="DELETE FROM $table WHERE id=".(int)$_REQUEST['Delete'].";";
        $result3=$conn->query($sql3);

    }
}

if($_REQUEST['Switch']) {
    {

        $sql2="UPDATE $table SET state= IF (`state`, 0, 1) WHERE id=".(int)$_REQUEST['Switch'].";";


        $result2=$conn->query($sql2);

    }
}


    $sql="SELECT * FROM $table;";
    $result=$conn->query($sql); ?>

 <form action="logout.php" method="post">
<input type="submit" name="logout" value="logout">
</form>
<b>Alarm Monitoring</b>

<p>Sensors state table. Clicking on "Switch state" will switch the field "state" of the associated mote. </p>
<p>State 1 means that the mote is active, the alarm will be activated if the mote is moved or if someone is detected.</p>
<p>State 0 means that the mote is inactive, the alarm will NOT be activated if the mote is moved or if someone is detected.</p>





<table border=0>
    <tr>
        <td>

                <table border=1>
                    <tr>
                        <td width=150>ID</td>
                        <td width=150>Date</td>                        
                        <td width=300>PIR</td>  
                        <td width=150>Accel</td>
                        <td width=100>State</td>
                        <td width=150>Switch</td>
                        <td width=150>Delete</td>
                    </tr>
         <?php
   while($row = $result->fetch_assoc()) : ?>

                    <tr>
                        <td><?php echo $row['id']; ?></td>
                        <td><?php echo $row['date']; ?></td>
                        <td><?php echo $row['pir']; ?></td>
                        <td><?php echo $row['accel']; ?></td>
                        <td><?php echo $row['state']; ?></td>
                        <form name="form1" method="post" action=''>
                        <td><?php echo "<input name='Switch' type='submit' id='Switch' value='".$row['id']."'>"; ?></td>
                        <td><?php echo "<input name='Delete' type='submit' id='Delete' value='".$row['id']."'>"; ?></td>
                        </form>
                    </tr>    


 <?php endwhile; ?>    
</table>            

</table>
</td>
</tr>
</table>

And please do not use in this case some SQL queries in WHILE loop

Following that delete, there's `header('Location: table.php');`, which will instruct the browser to reload the page. The result of this is that the user shouldn't actually see the old data. — stwalkerster, Apr 17 '15 at 11:42
header('Location: table.php'); shoult be before any output like html :) — user3284566, Apr 17 '15 at 11:53
You need to replace the code to top of page and html to bottom and remove Header(Location) and everything will be ok I think — user3284566, Apr 17 '15 at 11:54
If I do so, the little phrases I put in the top will appear at the bottom on the page, won't they ? And I output the table at the same time I check if the buttons have been clicked, I don't see how I could dissociate them. The answer might be an evidence because Im a total newbie concerning web programmation. — Tikim, Apr 17 '15 at 11:57
Answer updated. You can check. In the code make connection , do some actions in Db like DELETE and UPDATE and after that SELECT all and show result — user3284566, Apr 17 '15 at 12:08
I tried your code, nothing happens when I click on both buttons and the MySQL table isn't modified (checked with the MySQL console). For the queries in the WHILE loop, are you saying that in order to avoid doing a mistake on the whole table, or is there any other reason ? — Tikim, Apr 17 '15 at 12:51
Ok I didnt see how you make delete and update action. Can you try now? Answer updated — user3284566, Apr 17 '15 at 13:20

score 0 · Answer 3 · answered Apr 17 '15 at 11:45

0

After you do the delete, you instruct the browser to reload the page with this:

header('Location: table.php');

However, you can't send HTTP headers after you've started sending the data. You'll probably see an error message below your table somewhere stating "headers already sent".

You need to move your output of page content to after the last time you modify the response headers with the header() function, or use output buffering.

answered Apr 17 '15 at 11:45

stwalkerster

1,646
1
20
30

What do you call "sending the data" ? The MySQL query ? If so, why didn't it work when I had a 500ms wait after the query was sent ? 500ms is by far long enough for the MySQL database to react... I didn't understood what I should move, and where. – Tikim Apr 17 '15 at 11:52
By "sending the data", I mean the HTML output to the browser. – stwalkerster Apr 17 '15 at 11:55

score 0 · Accepted Answer · edited May 23 '17 at 11:58

whell you are have problem with redirect. check this link to know more about redirect

Before you continue please backup your code.

To fix your problem you must move your <?php endwhile; ?> after

</form>
</tr>

then add input hidden after formname "Delete"

<td>
<?php echo "<input name='Switch' type='submit' id='Switch' value='Switch state ".$row['id']."'>"; ?></td>
<td><?php echo "<input name='Delete' type='submit' id='Delete' value='Delete mote ".$row['id']."'>"; ?></td>
<input name='id' type='hidden' value="<?php echo $row['id']; ?>">
<input name='state' type='hidden' value="<?php echo $row['state']; ?>">

and the last replace this:

<?php
if($_REQUEST['Delete']=='Delete mote ' . $row['id']) {
   {
        $sql3="DELETE FROM $table WHERE id=".$row['id'].";";
        $result3=$conn->query($sql3);
        header('Location: table.php');
    }
}

if($_REQUEST['Switch']=='Switch state ' . $row['id']) {
    {
        if($row['state']=='1')
        {
                $sql2="UPDATE $table SET state='0' WHERE id=".$row['id'].";";
        }
        else
        {      
                $sql2="UPDATE $table SET state='1' WHERE id=".$row['id'].";";
        }

        $result2=$conn->query($sql2);
        header('Location: table.php');
    }
}

?>

With this

<?php

        if (!empty($_REQUEST['Delete']) && !empty($_REQUEST['id']))
        {
            $sql3="DELETE FROM $table WHERE id=".$_REQUEST['id'].";";
            $result3=$conn->query($sql3);
            echo '<script> location.replace("index.php"); </script>';
        }

        if (!empty($_REQUEST['Switch']) && !empty($_REQUEST['id']) && isset($_REQUEST['state']))
        {
            var_dump($_REQUEST['Switch']);

            if($_REQUEST['state'] == '1')
            {
                    $sql2="UPDATE $table SET state='0' WHERE id=".$_REQUEST['id'].";";
            }
            else
            {      
                    $sql2="UPDATE $table SET state='1' WHERE id=".$_REQUEST['id'].";";
            }

            $result2=$conn->query($sql2);

            echo '<script> location.replace("index.php"); </script>';
        }
    ?>

Im not say this is are the good solution. but it will solve your problem until you found the best solution.

THIS solved the problem ! Thanks a lot ! I just had to replace "index.php" with "table.php" which is the name of the php file where all this code is. Actually, to identify the problem, I tried an even simpler solution based on yours : I just replaced : header('Location: table.php'); with : echo ''; For some reason it seems that this second line that you proposed is more efficient when refreshing the page. Thanks again :-) — Tikim, Apr 17 '15 at 14:20

score 0 · Answer 5 · answered Dec 06 '19 at 14:01

This is very simple issue because I encounter with them, problem is SQL statement, you need look at MYSQL, so I resolve with this for example :

DELETE FROM `reyler` WHERE `reyler`.`id`=".$id_post1 ."";

this symbol you need => `` it is not this " or this '

second you can delete from mysql and mysql before deleting show you window with sql statement you can copy from there right statement,this is associated with your database in your hosting or in xampp server, I think will help you.))

PHP/MYSQL: A removed row is still displayed in my page after a reload

5 Answers5