CODEWITHSUNDEEP
phppostmysqlirequest
Mateusz
Mateusz

Reputation: 11

PHP/Mysqli: Why does this code doubles rows insert?

I need a help with my code; somehow my code creates two rooms (it inserts two rows into a table at once), I don't know why.

(I need to require an id for every insert to know in which house we create a new room. My database contains table 'house' and table 'room'. Table 'room' has a field 'house_id' which is a foreign key with a field 'id' in table 'house'.)

That is my php page:

<?php 
            // turn autocommit off
            mysqli_autocommit($con, FALSE);

            // fetch the houses so that we have access to their names and id
            $query = "SELECT name, id
                    FROM house";
            $result = mysqli_query($con, $query);
            // check query returned a result
            if ($result === false) {
                echo mysqli_error($con);
            } else {
                $options = "";
               // create an option
                while ($row = mysqli_fetch_assoc($result)) {
                    // $options .= "".$row['name']."";
                     $options .= "<option value='".$row['id']."'>";
                     $options .= $row['name'];
                     $options .= "</option>";
                }
            }

            include('templates/add_room.html');

            if ($_SERVER["REQUEST_METHOD"] == "POST") {
                $price = mysqli_real_escape_string($con, $_POST["price"]);
                $house = mysqli_real_escape_string($con, $_POST["house_id"]);

                $query = "INSERT INTO room (price, house_id)
                VALUES ('$price', '$house')";

                // run the query to insert the data
                $result = mysqli_query($con, $query);

                // check if the query went ok
                if ( $con->query($query) ) {
                    echo "<script type= 'text/javascript'>alert('New room created successfully with the id of {$con->insert_id}');</script>";
                    mysqli_commit($con);

                } else {
                    echo "There was a problem:<br />$query<br />{$con->error}";
                    mysqli_rollback($con);
                }
            }

            //free result set
                mysqli_free_result($result);

?>

and that is my html template with form:

<h2>Add new room</h2>
<form action='' method='POST'>
            <fieldset>
                <label for='price'>Price:</label>
                <input type='number' name='price'>
            </fieldset>
            <fieldset>
            <label for='house_id'>House:</label>
                <select name='house_id' required>
                    <option value='' disabled selected>Select house</options>
                    <?php echo $options; ?>
                </select>
            </fieldset>
            <button type='submit'>Add</button>
</form>

Upvotes: 1

Views: 77

Answers (3)

dstudeba
dstudeba

Reputation: 9038

You are inserting it twice

first here:

// run the query to insert the data
$result = mysqli_query($con, $query);

then here:

// check if the query went ok
if ( $con->query($query) ) {

Remove the first one and you should be fine, or check on the result of the first one and remove the second one.

Upvotes: 1

Funk Forty Niner
Funk Forty Niner

Reputation: 74220

It inserts 2 rows because of your using the query function twice:

$result = mysqli_query($con, $query);

                // check if the query went ok
                if ( $con->query($query) ) {

So you'll need to change that conditional statement to:

if ($result)  {

By the way, use a prepared statement, it's safer than real_escape_string():

Upvotes: 3

S. Imp
S. Imp

Reputation: 2895

Not 100% certain, but it looks like you run INSERT query twice. Once here:

$result = mysqli_query($con, $query);

and once a moment later when you try to check for something. you inadvertently use the OOP style when you are apparently trying to check for something

if ( $con->query($query) ) {

Upvotes: 0

Related Questions