PHP MySQL INSERT return value with one query execution

Question

Is there anything returned from MySQL/PHP on a INSERT query being executed? Here is my function which I have in a CLASS.

function mysqlQuery($query) {
   // Gets the results from the query
   $results = mysql_query($query, $this->connection);

   // Loops through the queried results as an multi-dimensional array
   while($rows = mysql_fetch_array($results, MYSQL_ASSOC)) {
      // Push results to single array
      $rs[] = $rows;
   }

   // Return results as array
   return $rs;
}

This is how I call the function

$rs = $dbh->mysqlQuery($query);

But executing a INSERT query the $rs returns nothing. Does my function need help or is this the default behavior? Any tips would be helpful as well.

score 40 · Accepted Answer · answered Sep 28 '09 at 17:16

40

INSERT just returns true or false. to actually return something useful, you will need a SELECT or similar query. there is no result to fetch with INSERT.

answered Sep 28 '09 at 17:16

helloandre

10,541
8
47
64

score 8 · Answer 2 · edited Apr 09 '19 at 18:32

From the php documentation:

Return Values For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.

The returned result resource should be passed to mysql_fetch_array(), and other functions for dealing with result tables, to access the returned data.

Use mysql_num_rows() to find out how many rows were returned for a SELECT statement or mysql_affected_rows() to find out how many rows were affected by a DELETE, INSERT, REPLACE, or UPDATE statement.

mysql_query() will also fail and return FALSE if the user does not have permission to access the table(s) referenced by the query.

Chiwda · Answer 3 · 2020-04-18T05:48:42.153

3

Although this is a very old thread, it is still relevant. And to those who stumble upon this (like I did), don't give up! There are options. In fact, see this answer on SO

Also, for sqli and pdo, see this

Essentially, an insert statement followed by one of the functions listed in those answers will give you the ID of the last record. The function is used like this:

$LastID = mysql_insert_id();

after the INSERT statement.

edited Apr 18 '20 at 05:48

answered Jul 27 '18 at 04:27

Chiwda

1,233
7
30
52

Thanks for posting this. `mysql_insert_id` is what I was looking for. – crazymatt Oct 09 '19 at 17:31

score 0 · Answer 4 · answered Sep 28 '09 at 17:18

From php.net http://us2.php.net/mysql_query

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.

swordofpain · Answer 5 · 2009-09-28T18:24:57.610

0

Generally, when devs are building home functions to manipulates queries, they use two methods. One, "query", used for SELECTs and co, and one, "exec", other INSERTs, UPDATEs and others (like in PDO extension).

If you want to keep your function, add a IF statement around the loop checking the type of $results (with a is_resource() for example)

edited Sep 28 '09 at 18:24

answered Sep 28 '09 at 17:49

swordofpain

149
4

score 0 · Answer 6 · answered Sep 25 '17 at 16:38

In the example below, I add to my insert clause the "returning" along with the primary key of my table, then after the execute, I do a fetch getting an array with the value of the last inserted id.

<?php 
    public function insert($employee){ 

        $sqlQuery = "INSERT INTO employee(user_id,name,address,city) VALUES(:user_id,:name,:address,:city) RETURNING employee_id"; 

        $statement = $this->prepare($sqlQuery); 

        $a ="2002-03-11 12:01AM" ; 

        $statement->bindParam(":user_id", $employee->getUserId(), PDO::PARAM_INT); 
        $statement->bindParam(":name", $employee->getName(), PDO::PARAM_STR); 
        $statement->bindParam(":address", $employee->getAddress(), PDO::PARAM_STR); 
        $statement->bindParam(":city", $employee->getCity(), PDO::PARAM_STR); 
        $statement->execute(); 

        $result = $statement->fetch(PDO::FETCH_ASSOC); 
        return $result["employee_id"]; 

    } 
?>

Source

Just going to point out for future viewers: The query in this answer uses the `RETURNING` clauses which is a SQL Server feature, not available in MySQL which is specified in the question above. — Issung, Nov 24 '22 at 03:48

PHP MySQL INSERT return value with one query execution

6 Answers6

Linked