Php Mysql Insert Return Value With One Query Execution

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 t

Solution 1:

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.

Solution 2:

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.

Solution 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.

Solution 4:

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)

Solution 5:

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