Table content keeps adding when refreshing PHP page

Question

I have a problem with PHP adding content to the database. Every time I refresh the page it adds the exact same values over again. How can I make it stop doing that? I have no idea why it does that.

I want it to add the values to the database once and store them there. When the table is created and values added, they are used on the page. User will have the ability to add more values.

It also adds whatever the user has entered when refreshing.

The problematic part is

$db_query = 'INSERT INTO `cars`' .
    ' (`id`, `make`, `model`, `year`, `color`, `engine`)' .
    ' VALUES' .
    ' (NULL, \'Citroen\', \'Saxo\', \'1997\', \'White\', 1.1),' .
    ' (NULL, \'Citroen\', \'C3\', \'2012\', \'Blue\', 1.4),' .
    ' (NULL, \'Volkswagen\', \'Golf\', \'2010\', \'Blue\', 1.8),' .
    ' (NULL, \'Ford\', \'Mondeo\', \'2009\', \'Black\', 1.6),' .
    ' (NULL, \'Renault\', \'Clio\', \'2010\', \'Silver\', 1.2);';
$result = mysqli_query($conn, $db_query);

Full code:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <title>Task 7</title>
</head>
<body>
<?php


# Connection to the database start
$host = 'localhost';
$user = 'root'; # Your username
$password = 'vertrigo'; # Your password
$databaseName = 'db2'; # Your database

$conn = mysqli_connect($host, $user, $password, $databaseName);

if (mysqli_connect_errno($conn)) {
    echo 'Failed to connect to MySQL: ' . mysqli_connect_error();
} else {
    echo 'have connection<br>';
}
# Connection to the database end (output messages created)


# Creating the table with rows start
$db_query = 'CREATE TABLE IF NOT EXISTS `cars` (' .
    '`id` int(11) NOT NULL AUTO_INCREMENT,' .
    '`make` varchar(50) NOT NULL,' .
    '`model` varchar(50) NOT NULL,' .
    '`year` varchar(4) NOT NULL,' .
    '`color` varchar(50) NOT NULL,' .
    '`engine` decimal(2,1) NOT NULL,' .
    'PRIMARY KEY (`id`));';
$result = mysqli_query($conn, $db_query);
# Creating table with rows end


# Putting information in each column start
$db_query = 'INSERT INTO `cars`' .
    ' (`id`, `make`, `model`, `year`, `color`, `engine`)' .
    ' VALUES' .
    ' (NULL, \'Citroen\', \'Saxo\', \'1997\', \'White\', 1.1),' .
    ' (NULL, \'Citroen\', \'C3\', \'2012\', \'Blue\', 1.4),' .
    ' (NULL, \'Volkswagen\', \'Golf\', \'2010\', \'Blue\', 1.8),' .
    ' (NULL, \'Ford\', \'Mondeo\', \'2009\', \'Black\', 1.6),' .
    ' (NULL, \'Renault\', \'Clio\', \'2010\', \'Silver\', 1.2);';
$result = mysqli_query($conn, $db_query);
# Putting information at each end


# Add cars START
@$cars = $_POST['cars'];
if (isset($cars)) {
    $db_query = "INSERT INTO cars (make, model, year, color, engine) VALUES ('" . $cars['0'] . "','" . $cars['1'] . "','" . $cars['2'] . "','" . $cars['3'] . "','" . $cars['4'] . "')";
    mysqli_query($conn, $db_query);
} else {
    echo 'Cars array does not exists and obtained from FORM.';
}
# Add cars END


# Select the cars table if the right output the headers start
$db_query = "SELECT * FROM cars WHERE id <> 0 ORDER BY year ASC";

if ($result = mysqli_query($conn, $db_query)) {
    echo  "    <table border='1'>";
    echo  "        <tr><td>Make</td><td>Year</td><td>model</td><td>color</td><td>engine</td></tr>";
    # Select cars table if the right output the headers end

    # while start, output the content of the column.
    while ($row = mysqli_fetch_assoc($result)) {
    ?>
        <tr>
            <td><?php echo $row['make']; ?></td>
            <td><?php echo $row['year']; ?></td>
            <td><?php echo $row['model']; ?></td>
            <td><?php echo $row['color']; ?></td>
            <td><?php echo $row['engine']; ?></td>
        </tr>
    <?php
    }
    # End while

    echo "</table>";
}
?>
    <form action="index.php" method="post" name="zatupok">
        <fieldset>
            <legend>MY CARS</legend>
            <label>Make</label>
            <input type="text" name="cars[]">
            <label>Model</label>
            <input type="text" name="cars[]">
            <label>Year</label>
            <input type="text" name="cars[]">
            <label>Color</label>
            <input type="text" name="cars[]">
            <label>Engine</label>
            <input type="text" name="cars[]">
            <input type="submit">
        </fieldset>
    </form>
</body>
</html>

I think your answer is here http://stackoverflow.com/questions/5166178/php-form-refresh-send-form-again-prevent — electroid, Oct 27 '13 at 13:03
Seems like it. I do struggle to find where exactly to place it. I am all new to PHP, doing my own studies. Can you give me a hand show me where to place it. I upload my whole code. — user2919681, Oct 27 '13 at 13:16
Well. The story behind is is really long and creepy. I am working on self studies and at point where request to create PHP which creates a table in SQL and then is possible to do simple statements like EDIT UPDATE DELETE etc. It also must be made in classes not in one single file. This is just me trying to create the connection in PHP for the first time EVER. This link will give you more idea of what is going on. Hope it can be helped. And yeah I have NO idea how to transfer this in to classes after it is working. http://stackoverflow.com/questions/19588347/sql-database-access-layer-with-php — user2919681, Oct 27 '13 at 13:55
Just continue in the same way and don't mind about refreshing page, since that's how HTTP Requests works. — electroid, Oct 27 '13 at 14:07

Palec · Accepted Answer · 2021-12-30T02:18:34.943

Why the sample values are added on each page visit

Your PHP code is executed every time you visit the page. It generates the page (writes parts of it dynamically, just copies everything outside <?php … ?>), and the page is the output of your script. Apache, or whatever server software you use, just gets an HTTP request, executes your script, takes its output and sends it to the client in an HTTP response.

If your INSERT query is executed unconditionally (as it is), it is also executed every time you visit the page. That's why the values are added over and over again.

How to stop adding the sample values

You may want to check if the cars table was created and execute the insert query only if it was:

# Creating the table with rows start
# CHANGED: Notice that IF NOT EXISTS is not used anymore!
#          Fails if the table already exists, not touching its original contents.
$db_query = 'CREATE TABLE `cars` (' .
    '`id` int(11) NOT NULL AUTO_INCREMENT,' .
    '`make` varchar(50) NOT NULL,' .
    '`model` varchar(50) NOT NULL,' .
    '`year` varchar(4) NOT NULL,' .
    '`color` varchar(50) NOT NULL,' .
    '`engine` decimal(2,1) NOT NULL,' .
    'PRIMARY KEY (`id`));';
$result = mysqli_query($conn, $db_query);
# Creating the table with rows end


# Putting information in each column start
$db_query = 'INSERT INTO `cars`' .
    ' (`id`, `make`, `model`, `year`, `color`, `engine`)' .
    ' VALUES' .
    ' (NULL, \'Citroen\', \'Saxo\', \'1997\', \'White\', 1.1),' .
    ' (NULL, \'Citroen\', \'C3\', \'2012\', \'Blue\', 1.4),' .
    ' (NULL, \'Volkswagen\', \'Golf\', \'2010\', \'Blue\', 1.8),' .
    ' (NULL, \'Ford\', \'Mondeo\', \'2009\', \'Black\', 1.6),' .
    ' (NULL, \'Renault\', \'Clio\', \'2010\', \'Silver\', 1.2);';
# CHANGED: Executing the query only if previous query (`CREATE TABLE …`) succeeded.
if ($result) {
    $result = mysqli_query($conn, $db_query);
}
# Putting information at each end

Refresh after a POST problem

Another problem we met during debugging the issue is refresh after POST form submission. Browser automatically submits the original form contents again, which is standard behavior. It results in inserting the submitted values to the database again, now storing them in two or even more copies.

This can be prevented, though. See e.g. Stop browsers asking to resend form data on refresh. The accepted answer recommends the POST-redirect-GET pattern, which is what I would use in your case. You can get much more on that topic when googling post data resent stackoverflow.

@user2919681 What you need to do is to set your form and your DB code apart from each other. Also, if you want to run your code the way it is, then I suggest you add an `if(isset($_POST['submit']))` at the beginning and under your DB credentials, then name your submit button like this `` that way it will only execute if the submit button was pressed; THAT's your **answer.** — Funk Forty Niner, Oct 27 '13 at 15:37
@Fred-ii- Propably user2919681 just wants to make sure the table is there. The script should setup the table when deleted manually and fill it with example data. `CREATE TABLE` produces an error when creating a table that already exists in MySQL, so no data is owerwritten. `IF NOT EXISTS` just suppresses the error, but then it could not be detected, whether the table has already been created or not. — Palec, Oct 27 '13 at 15:44
@Palec I doubt that the intention on adding values to a DB is a **one-time thing**. I understand what you're saying, however the OP's setup doesn't make any sense and should be using conditions instead. Using `if(isset($_POST['submit']))` is a partial solution for the original problem. — Funk Forty Niner, Oct 27 '13 at 15:52

Table content keeps adding when refreshing PHP page

1 Answers1

Why the sample values are added on each page visit

How to stop adding the sample values

Refresh after a POST problem