How to check if record has been inserted into database table?

Question

How do I check insert success in my code?

I tried to test my code below, but it doesn't work. It just returns insert every time.

<?PHP
   include("connect.php");

    $sql = "INSERT INTO details (name , month , description) 
            SELECT name , month , description 
            FROM details_temporary WHERE id = 'xxxxxx'";

   $dbQuery = mysql_query($sql);

   if($dbQuery) {
      echo "insert";
   }
   else {
      echo "not insert";
   }
?>

[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](http://j.mp/XqV7Lp). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. — Kermit, Jan 13 '14 at 15:18
This code is correct. If you are doing an `INSERT` query (which you are), then `mysql_query` will return a boolean. So, how do you figure it "doesn't work"? Why wouldn't it return `"insert"` every time? Is this query supposed to be failing? What do you expect this code to do? Did you check to see if rows were inserted? — gen_Eric, Jan 13 '14 at 15:20
to - Rocket Hazmat # it's echo insert but not have data in row. — user3190706, Jan 13 '14 at 15:24
One method I use is `if(mysqli_num_rows($query) > 0){ echo "Exists"; }` — Funk Forty Niner, Jan 13 '14 at 15:30
that's because you're using `mysql_` instead of `mysqli_` which you should be using — Funk Forty Niner, Jan 13 '14 at 15:53
I'm sorry, but you're really going to have to make an effort with this. I'm not going to comment for hours, and/or post an answer, in "HOPING" it's going to work for you. Just Google what I gave you, that will get you started. Good luck with that. — Funk Forty Niner, Jan 13 '14 at 17:27
Plus, there's already been an [**answer given below**](http://stackoverflow.com/a/21094908/1415724) that should theoretically work. Work with that. — Funk Forty Niner, Jan 13 '14 at 17:28
You can use PDO statements, check documentation: http://php.net/manual/en/pdostatement.rowcount.php — Hemant Kumar, Oct 12 '18 at 04:18

score 1 · Answer 1 · edited May 23 '17 at 11:48

1

Please, don't use mysql_* functions in new code. They are no longer maintained and the deprecation process has begun on it. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which.

An example using the MySQLi functionality is below:

$mysqli = new mysqli('localhost', 'root', 'password', 'database_name');

$sql = "INSERT INTO details (name, month, description) VALUES ('Alex', 'October', 'My Birthday')";
$result= $mysqli -> query($sql);
$affected = $mysqli -> affected_rows;

if($affected == 1){
   echo 'Inserted';
}else{
   echo 'Did not insert';
}

edited May 23 '17 at 11:48

Community

1
1

answered Jan 13 '14 at 15:22

ajtrichards

29,723
13
94
101

Prepared statements are where it's at! You should be using them :-) – gen_Eric Jan 13 '14 at 15:25
Yeah totally agree. Just wanted to get something down. Will update the example. – ajtrichards Jan 13 '14 at 15:26

score 0 · Answer 2 · answered Jan 13 '14 at 15:22

0

Firstly, don't use MySQL functions, they're deprecated and insecure, look in to using MySQLi or PDO.

Secondly, MySQL will only return an error if it failed, so you can just simply run:

if (!$dbQuery) {
    die('Invalid query: ' . mysql_error());
}

This will die and print the error if it was unsuccessful.

If you want to check further, you could check mysqli_insert_id() as this will only be set if the query was successful (will only work for inserts, not updates, etc).

answered Jan 13 '14 at 15:22

Karl

5,435
11
44
70

Using MySQL is fine, using the mysql_* functions is however a no no. You're using MySQL even when using MySQLi or PDO if that's the chosen database structure (I know you're talking about mysql_*, I'm just making it clear to OP so he doesn't thing he has to switch to PostgreSql or something like that). – Jonast92 Jan 13 '14 at 15:23
I know but someone could get confused :) (Updated) – Jonast92 Jan 13 '14 at 15:24

score 0 · Answer 3 · answered Jan 13 '14 at 15:30

You are using INSERT FROM SELECT and if the select has no result then it will insert nothing and still have true in $dbQuery.

Use mysql_affected_rows. : Get number of affected rows in previous MySQL operation

mysql_* functions are deprecated as of php 5.5

If you are working on a new project and NOT changing an old one, it's best to use pdo/mysqli etc.

How to check if record has been inserted into database table?

3 Answers3