0

Solution found: I had to set my #ID column to auto_increment. See also: Auto increment in phpmyadmin

I have made a web formula and I want to fetch the data and insert it in a MySQL database. I am new to PHP and MySQL and I am having some trouble understanding why I can't insert more than 1 row.

I have basically just made the following in order to learn to fetch form data: https://www.youtube.com/watch?v=2HVKizgcfjo The guy from the video succeeds at inserting more records from the formula.

NB: I am not running on localhost but on a web hotel (with Linux).

Part of my troubleshooting: I have differed from procedural- and object-oriented programming and searched StackOverflow to find a way to handle indexes - but i guess that is not my cue?

There shouldn't be any trouble with unique values or sets.

I have been in doubt whether I should include #id column as part of the statement but have not, since that the video I have been watching haven't done it and does not have any issues.

My code: My file.php is essentially as follows:

<?php

    // Getting form data. Note that some are not shown here for overview
    $firstName = $_POST['firstName'];
    $phoneNumber = $_POST['phoneNumber'];

    // Database connection
    $conn = new mysqli('some_host','hostname','password','database');

    // Checks connection
    if ($conn->connect_error){
        die('Connection Failed : '.$conn->connect_error);
    } else {

                // Prepare statement
        $stmt = $conn->prepare("insert into DB_test(firstName,phoneNumber) values(?,?)");
        $stmt->bind_param('si',$firstName,$phoneNumber);

            // Tells if insertion was possible to execute.
            // It echoes "Unable to create record" when there is 1 record in db already
        if ($stmt->execute()) {
          echo "New record created successfully";
        } else {
          echo "Unable to create record";
        }

        $stmt->close();
        $conn->close();

    }
?>

Following returns -1 when unable to insert new record. It returns 1 when able to.

printf("%d Row inserted.\n", mysqli_stmt_affected_rows($stmt));

Is there any other way to insert records in MySQL database with statements or have I missed an important note on PHP and MySQL?

All the best!

DisabledWhale
  • 771
  • 1
  • 7
  • 15
  • Not related to the question, but you shouldn't use an integer for phone numbers, use a string. – Barmar Sep 07 '19 at 00:00
  • Which message is it echoing? Success or unable to create? If it's unable to create, you should print `$stmt->error` to see the reason. – Barmar Sep 07 '19 at 00:02
  • It is echoing "Unable to create record". – DisabledWhale Sep 07 '19 at 00:05
  • OK, so what does `echo $stmt->error;` show? – Barmar Sep 07 '19 at 00:06
  • Aha! That is the almighty debugging command! It is echoing: Duplicate entry '0' for key 'PRIMARY'. So, should I consider to count rows in database and feed statement with id = length of database? Or is there a neat MySQL setting to handle this? – DisabledWhale Sep 07 '19 at 00:07
  • 1
    What is the primary key of the table? If it's an ID column, it should use `AUTO_INCREMENT` to give it a unique value each time. – Barmar Sep 07 '19 at 00:09
  • That was it! That was the solution! Thank you very much!! – DisabledWhale Sep 07 '19 at 00:17

0 Answers0