How can we check insert query was successful in php mysqli?

I have this big function that gets a lot of different data and insert it into multiple tables.. Not all the data is always available so not all the SQL INSERT queries are successful. I need to check which SQL INSERT query was fully successful and which wasn't to the do something with this data (like inserting into a log table or similar).

Just to give you an example of how I think it can be done:

$sql = 'INSERT INTO data_table (ID, column1, column2) VALUES(?, ?, ?)';

if ($stmt->prepare($sql)) {
    $stmt->bind_param('iss', $varID, $var1, $var2);

    if ($stmt->execute()) {
        $success == TRUE;   //or something like that
    }
}

I'm not completely sure this is the best way and if its always really show if the data was inserted into the table... Any suggestions??



To insert data into a MySQL table, you would need to use the SQL INSERT INTO command. You can insert data into the MySQL table by using the mysql> prompt or by using any script like PHP.

Syntax

Here is a generic SQL syntax of INSERT INTO command to insert data into the MySQL table −

INSERT INTO table_name ( field1, field2,...fieldN )
   VALUES
   ( value1, value2,...valueN );

To insert string data types, it is required to keep all the values into double or single quotes. For example "value".

Inserting Data from the Command Prompt

To insert data from the command prompt, we will use SQL INSERT INTO command to insert data into MySQL table tutorials_tbl.

Example

The following example will create 3 records into tutorials_tbl table −

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed

mysql> INSERT INTO tutorials_tbl 
   →(tutorial_title, tutorial_author, submission_date)
   →VALUES
   →("Learn PHP", "John Poul", NOW());
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO tutorials_tbl
   →(tutorial_title, tutorial_author, submission_date)
   →VALUES
   →("Learn MySQL", "Abdul S", NOW());
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO tutorials_tbl
   →(tutorial_title, tutorial_author, submission_date)
   →VALUES
   →("JAVA Tutorial", "Sanjay", '2007-05-06');
Query OK, 1 row affected (0.01 sec)
mysql>

NOTE − Please note that all the arrow signs (→) are not a part of the SQL command. They are indicating a new line and they are created automatically by the MySQL prompt while pressing the enter key without giving a semicolon at the end of each line of the command.

In the above example, we have not provided a tutorial_id because at the time of table creation, we had given AUTO_INCREMENT option for this field. So MySQL takes care of inserting these IDs automatically. Here, NOW() is a MySQL function, which returns the current date and time.

Inserting Data Using a PHP Script

PHP uses mysqli query() or mysql_query() function to insert a record into a MySQL table. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax

$mysqli→query($sql,$resultmode)
Sr.No.Parameter & Description
1

$sql

Required - SQL query to insert record into a table.

2

$resultmode

Optional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Example

This example will take three parameters from the user and will insert them into the MySQL table − −

Copy and paste the following example as mysql_example.php −


   
      Add New Record in MySQL Database
   
   
      ", $mysqli→connect_error);
               exit();
            }
            printf('Connected successfully.
'); if(! get_magic_quotes_gpc() ) { $tutorial_title = addslashes ($_POST['tutorial_title']); $tutorial_author = addslashes ($_POST['tutorial_author']); } else { $tutorial_title = $_POST['tutorial_title']; $tutorial_author = $_POST['tutorial_author']; } $submission_date = $_POST['submission_date']; $sql = "INSERT INTO tutorials_tbl ". "(tutorial_title,tutorial_author, submission_date) "."VALUES ". "('$tutorial_title','$tutorial_author','$submission_date')"; if ($mysqli→query($sql)) { printf("Record inserted successfully.
"); } if ($mysqli→errno) { printf("Could not insert record into table: %s
", $mysqli→error); } $mysqli→close(); } else { ?>
Tutorial Title
Tutorial Author
Submission Date [ yyyy-mm-dd ]

Output

Access the mysql_example.php deployed on apache web server, enter details and verify the output on submitting the form.

Record inserted successfully.

While doing a data insert, it is best to use the function get_magic_quotes_gpc() to check if the current configuration for magic quote is set or not. If this function returns false, then use the function addslashes() to add slashes before the quotes.

You can put many validations around to check if the entered data is correct or not and can take the appropriate action.

How can we check insert is successful in SQL PHP?

To check if your INSERT was successful, you can use mysqli_affected_rows() . Returns the number of rows affected by the last INSERT, UPDATE, REPLACE or DELETE query. And check for errors against your query and for PHP.

How do I know if SQL insert query was successful?

You can check the @@ROWCOUNT right after insert. If it's more than 0, then the insert succeeded. Also, if @@ERROR = 0 after insert, it was successful. No, check it in T-SQL although if the insert will result in error, most likely the error will be propagated into the client.

What is the result of Mysqli_query?

For successful queries which produce a result set, such as SELECT, SHOW, DESCRIBE or EXPLAIN , mysqli_query() will return a mysqli_result object. For other successful queries, mysqli_query() will return true .

How can I see Mysqli errors?

Just simply add or die(mysqli_error($db)); at the end of your query, this will print the mysqli error.