Why does the DataBase return old information?

user_13248287 0
Viewed 123

(Edit: I added examples below. I also added a public_ip to my tables as suggested in the comments)

(Just a disclaimer, I am still very new to web development. I especially know very little about MySQL in conjuction with PhP.)

I have a database table that contains a number records. For this example it contains records of DVDs. One of my php pages receives the ID of the DVD to be removed, and then proceeds to do so. ($chosenTable is a variable as I also have different tables for CDs, Vinyls and others - they all have the same columns)

$sql = "DELETE FROM $chosenTable WHERE id=$removeID";
$conn->exec($sql);

Yet I've noticed that recently it would delete non-existing records. For example, I would have this in my database:

ID - Title - Comment - File location
1 - Mr Bones - Very new - Folder/Bones.jpg
2 - Avengers - Secondhand - Folder/Avengers.jpg

Using the delete query works the first time. If I send "2", it removes record two. So as expected I have one record left:

ID - Title - Comment - File location
1 - Mr Bones - Very new - Folder/Bones.jpg

I would then add a new record. From the database on phpMyAdmin I am absolutely sure it has been added:

ID - Title - Comment - File location
1 - Mr Bones - Very new - Folder/Bones.jpg
2 - Intersteller - Broken - Folder/Intersteller.jpg

But here is where I am left without any explanation. If I remove the second record again (this time Intersteller), then nothing happens. When I checked the Title given of the record it is supposed to delete, it returns the title of the old second record.

Getting the info of the record I want to delete:

$temp = $conn->query("SELECT Title from $chosenTable WHERE ID='$removeID')->fetch()["Title"];
echo $temp;

When I did this it returns Avengers. With the above example in mind, the title being returned is supposed to be Intersteller, NOT Avengers. Avengers no longer exists in the table. I am absolutely sure about this. So now I know that it doesn't delete the second record (Intersteller), because it is deleting the OLD second record, Avengers. The same applies when I delete the third or fourth records (it instead deletes the non-existing old third and fourth records).

It seems for some reason Chrome or the server has its own pseudo-table stored somewhere that it uses instead of my actual table.

I have looked all day and I cannot find a solution this. I apologize again for any stupid mistakes. I just really wish I could understand how on earth I could get past this.

I noticed when I use another browser, like Firefox or even my smartphone's browser, it again allows me to remove a record once. I then have the exact same problem if I upload and remove another item. That it happens on different browsers makes me think the problem lies with me.

I am using Database MySQL client version: 5.6.43

Any help at all?

Edit: I made two php pages to simulate the problem. The same thing happens. I do not know how to connect to DB fiddle so I hope the code itself will suffice. upload.php submits information and adds it to the database table. remove.php removes record one of the table:

upload.php

<?php
if ($_SERVER["REQUEST_METHOD"] == "POST"){ 
     
     //Get info from POST
    $uTitle = $_POST["iTitle"]; 
    $uComment = $_POST["tComment"];
    $uPrice = $_POST["iPrice"];
    
    echo "<script>console.log('$uTitel - $uComment - $uPrice);</script>";
    
    //Set a file location for the uploaded picture
    $fileName = basename($_FILES["iFile"]["name"]);
    echo "<script>console.log('filenaam: $fileName');</script>";
    $saveLocation = "uploads/".$fileName;
    
    echo "<script>console.log('Saved locationk: $saveLocation');</script>";
    
    //Upload file
    if (move_uploaded_file($_FILES["iFile"]["tmp_name"], $saveLocation))
    { 
        
        //If file is successfully uploaded
        echo "<script>console.log('File uploaded');</script>";
        
        //For connecting to database table - these four variables obviously just for illustration:
        $servername = "localhost"; 
        $connectusername = "alyosha";
        $connectpassword = "alyoshaPassword";
        $dbname = "alyoshaDB";
    
     try { //If connected to database
        $conn = new PDO("mysql:host=$servername;dbname=$dbname", $connectusername, $connectpassword);
        
        echo "<script>console.log('Connected to Database');</script>";
        
        //I first see how many records are already in the table so that I can add the appropriate value for my 'public_ip' column
        $m = $conn->prepare("SELECT * from testTable");
        $m->execute();
        $amountRows = $m->rowCount();
        
        echo "<script>console.log('Amount in table before adding record: $amountRows');</script>";
        
        //newIP is the current amount+1 so that it is added below my previous records (if there are 3 records, the new one should obviously be record public_ip 4)
        $newIP = $amountRows +1;
        
        //Now for adding a row to the table with the submitted informaton:
            
            $sql = "INSERT INTO testTable (public_ip, Title, Comment, Price, File_Location) VALUES ($newIP, '$uTitle', '$uComment', $uPrice, '$saveLocation')";

            $conn->exec($sql);
            echo "<script>console.log('New row added');</script>";
            
            //Count again to make sure there is one more record than before
            $m = $conn->prepare("SELECT * from testTable");
            $m->execute();
            $amountRows = $m->rowCount();
            echo "<script>console.log('Amount in table after adding record: $amountRows');</script>";
        
     }
     catch (PDOException $e){ //if database did not connect
         echo "<script>console.log('Failed to connect to database');</script>";
     }
        
    }//if move
    
}//if post

?>

<!DOCTYPE html>
<html>
<head>
    <title>Add item</title>
    <meta name="viewport" content="width=device-width, initial-scale=1"/>
</head>
<body>
    <header>
        <h1>Upload</h1>
    </header>
          
        <form action='upload.php'  method='post' enctype='multipart/form-data'>
            <input type='file' name='iFile' required>
            <br/>
            <br/>
            
            
            <label for='iTitle'>Title:</label>
            <input type='text' name='iTitle' required>
            <br/>
            <br/>
            
            <label for='iPrice'>Price:</label>
            <input id='iPriceUpload' type='number' name='iPrice' min='1' required>
            <br/>
            <br/>
            
            <label for='tComment'>Comments (optional):</label>
            <br/>
            <textarea type='text' name='tComment'></textarea>
            <br/>
            <br/>
            
            <input type='reset' name='iReset'>
            <input type='submit' name='iSubmit'>
            <br/>
        
        <a href='remove.php?num=1'>Remove record 1</a>
             <!--In practise the user would not be able to choose this by altering the link, but for my example this is easier to change this here -->
        

</body>
</html>

remove.php:

<?php
    if ($_SERVER["REQUEST_METHOD"] == "GET"){ 
        
        $removeIP = $_GET["num"]; //The public_ip record to be deleted
        
        //Connect to table
        servername = "localhost"; 
        $connectusername = "alyosha";
        $connectpassword = "alyoshaPassword";
        $dbname = "alyoshaDB";
    
     try { //If connected to database
        $conn = new PDO("mysql:host=$servername;dbname=$dbname", $connectusername, $connectpassword);
        
        echo "<script>console.log('Connected to Database in Remove.php');</script>";
        
         //All the remaining records should have their public_ips shifted upwards (5->4, 4->3, 3->2, 2->1)
        //It is necessary to again count the amount of rows left to achieve this
        $m = $conn->prepare("SELECT * from testTable");
        $m->execute();
        $amountRows = $m->rowCount();
        
        echo "<script>console.log('Amount of rows before deleting: $amountRows');</script>";
        
        //For debugging purposes get the title of removeIP:
        $gotTitle = $conn->query("SELECT Title from testTable WHERE public_ip='$removeIP'")->fetch()["Title"];
     
         echo "<script>console.log('Title of record $removeIP to be deleted: $gotTitle');</script>";
     
        
        //
        
        $sql = "DELETE FROM testTable WHERE public_ip=$removeIP"; //public_ip=1
        $conn->exec($sql);
        
        echo "<script>console.log('Record $removeIP deleted');</script>";
        
        //count again to make sure one record is removed:
        $m = $conn->prepare("SELECT * from testTable");
        $m->execute();
        $amountRows = $m->rowCount();
        
        echo "<script>console.log('Amount of rows affter deleting: $amountRows');</script>";
        
        if ($removeIP < ($amountRows+1)){ //in case the removed record is the only one in the table it won't be necessary to change the public_ips of the rest
            
            for ($x = 1; $x < ($amountRows+2); $x++)
                {
                    echo "<script>console.log('Changing record: $x');</script>";
                    $lowerIP = $x-1; //The record with public_ip 2 should become public_ip 1, and 3->2, etc
                    
                    $sql = "UPDATE testTable SET public_ip=$lowerIP WHERE public_ip=$x";
                         
                    $stmt = $conn->prepare($sql);
                    $stmt->execute();
                }
            echo "<script>console.log('Loop finished');</script>";
        }
     }
     catch (PDOException $e){
         echo "<script>console.log('Failed to connect to database in Remove.php');</script>";
     }
        
        
    }//if GET
    
    //After everything go back to upload page
    echo "<script>window.location = 'upload.php';</script>";

?>

Here's a screenshot of the type of table I'm using: My table, 'testTable'

UPDATE: From extensive comments it seems something somewhere between the PHP and and browser is caching the pages. @Martin proposed a workaround which seems to be very helpful until I solve this issue. This is adding a date string to the page urls, forcing the browser to read a new a page rather than a cached page (if I understand this correctly).

But if someone could still explain why this is a problem in the first place I would really appreciate it. I can understand the browser or whatever using an old version of the page, but I cannot understand whatever this is keeping an outdated table somewhere.

mysql sql php
1 Answers

With respect, this business of renumbering your rows when you delete one is a really bad idea.

Why?

For one thing, the cost of doing this goes up the more rows you have. And the whole point of SQL is to allow you to handle vast tables efficiently.

For another thing, when you have multiple users doing this you get confusion. If one user is looking things up while another is deleting them, the first user will have the row numbers change under her. You can manage this with judicious use of database transactions, but they'll just block the second user until the first is done. This situation is often called a "race condition."

You mentioned you're new to MySql. Again with respect, I suggest you stick to the tried-and-true way of handling your ID numbers: auto-incrementing. When you delete a row from an auto incrementing table, the next row inserted does not re-use the deleted ID.

Everybody uses autoincremented ID values--from students to credit-card companies with unimaginably vast tables. I suggest you do the same, at least until you get a little more proficient with the technology.

Edit If you want to display your rows in the order you inserted them, simply do SELECT whatever FROM tbl ORDER BY id where id is the autoincrementing ID column. Gaps in the sequence of IDs due to deleted rows are not a problem.

If you have a particular ID value and you want the next row, in the order you inserted them, you can do

  SELECT whatever 
    FROM tbl
   WHERE id > <<<the value you have>>
   ORDER BY id
   LIMIT 1;

The id > <<<the value you have>> filter works even with gaps in the ID values.

You say there's only one user. But if that user happens to have two web pages open working on the database at the same time, your renumbering setup will fail. Spectacularly. When you have a web interface, it's always safest to assume you will have concurrent access.

user_2056080
Related