Insert data to MySql DB and display if insertion is success or failure

Question

I am inserting data to a MySQL DB, In case if the insertion fails i need to give an appropriate error message indicating so. According to my code below i will be Echo-ing the message Inserted if the insertion was success or failure.

What i want to do is to echo the Inserted message only when the data insertion is a success, and return fail if it fails. How can i modify my code to do so ?

<?php

mysql_connect("localhost", "root", "123") or die(mysql_error());
mysql_select_db("swm_database") or die(mysql_error());

mysql_query("INSERT INTO PEOPLE (NAME ) VALUES ('COLE')")or die(mysql_error()); 

echo "Inserted";
?>

the answers below already state what you should do, but think about using mysqli or PDO rather than the mysql_* functions. — Jon Taylor
– Jon Taylor, Commented Jul 25, 2012 at 12:56

Kode Plus · Accepted Answer · 2012-07-25 12:54:31Z

40

$result = mysql_query("INSERT INTO PEOPLE (NAME ) VALUES ('COLE')"));
if($result)
{
echo "Success";

}
else
{
echo "Error";

}

answered Jul 25, 2012 at 12:54

Kode Plus

7061 gold badge8 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Timeless · Accepted Answer · 2015-01-21 08:38:43Z

According to the book PHP and MySQL for Dynamic Web Sites (4th edition)

Example:

$r = mysqli_query($dbc, $q);

For simple queries like INSERT, UPDATE, DELETE, etc. (which do not return records), the $r variable—short for result—will be either TRUE or FALSE, depending upon whether the query executed successfully.

Keep in mind that “executed successfully” means that it ran without error; it doesn’t mean that the query’s execution necessarily had the desired result; you’ll need to test for that.

Then how to test?

While the mysqli_num_rows() function will return the number of rows generated by a SELECT query, mysqli_affected_rows() returns the number of rows affected by an INSERT, UPDATE, or DELETE query. It’s used like so:

$num = mysqli_affected_rows($dbc);

Unlike mysqli_num_rows(), the one argument the function takes is the database connection ($dbc), not the results of the previous query ($r).

Omesh · Accepted Answer · 2012-07-25 12:55:00Z

3

After INSERT query you can use ROW_COUNT() to check for successful insert operation as:

SELECT IF(ROW_COUNT() = 1,  "Insert Success", "Insert Failed") As status;

answered Jul 25, 2012 at 12:55

Omesh

29.4k6 gold badges44 silver badges51 bronze badges

2 Comments

Timeless Over a year ago

Return true does not guarantee the data has been inserted into database, can I say so ?

Omesh Over a year ago

yes, this is safest way as return true will not work in case of INSERT IGNORE query.

mlishn · Accepted Answer · 2012-07-25 12:55:04Z

2

if (mysql_query("INSERT INTO PEOPLE (NAME ) VALUES ('COLE')")or die(mysql_error())) {
  echo 'Success';
} else {
  echo 'Fail';
}

Although since you have or die(mysql_error()) it will show the mysql_error() on the screen when it fails. You should probably remove that if it isnt the desired result

answered Jul 25, 2012 at 12:55

mlishn

1,66714 silver badges19 bronze badges

Comments

Viskovic · Accepted Answer · 2012-07-25 12:57:17Z

2

Check: http://php.net/manual/en/function.mysql-affected-rows.php

answered Jul 25, 2012 at 12:57

Viskovic

394 bronze badges

1 Comment

Chris Happy Over a year ago

Helpful, but please do not add a link only answer.

Collectives™ on Stack Overflow

Insert data to MySql DB and display if insertion is success or failure

5 Answers 5

Comments

Comments

2 Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

2 Comments

Comments

1 Comment

Linked

Related