inserting into 3 tables 1

[DotNetGnat]

Guys,

I wrote the following function that is either expected to return true or false:

   function addItems($id, $listid,$itemname, $itemcomments){
   	$uldate = date('m/d/Y');
	mysql_query("LOCK TABLES ".TBL1.",".TBL2.",".TBL2." WRITE");
	mysql_query("SET AUTOCOMMIT = 0");
	$q = "INSERT INTO ".TBL1."(id, listid, activeind, uldate)"
   	     ."VALUES ($id, $listid, 1, '$uldate')";
   	mysql_query($q, $this->connection);    
	$itemid = mysql_query("SELECT LAST_INSERT_ID()", $this->connection);
	$q =  "INSERT INTO ".TBL2."(itemid, itemname)"
   	     ."VALUES ($itemid, '$itemname')" 
	     . "; INSERT INTO ".TBL3."(itemid, id, comments, commentdate) VALUES ($itemid, $id, '$itemcomments', '$uldate')";     
	$result = mysql_query("COMMIT");
	mysql_query("UNLOCK TABLES");
	return $result;
	}

I get the result as true but record is not gettting inserted into the tables.

What am i doing wrong.

Thanks

-DNG

 

[jpadie]

you cannot run multiple queries in one call to mysql_query.

so this is not going to work

$q =  "INSERT INTO ".TBL2."(itemid, itemname)"
            ."VALUES ($itemid, '$itemname')"
         . "; INSERT INTO ".TBL3."(itemid, id, comments, commentdate) VALUES ($itemid, $id, '$itemcomments', '$uldate')";

even if you did, in fact, send the string to the database - which you don't.

break it out into two strings and send each sequentially to mysql_query.

 

[DotNetGnat]

thanks jpadie,

I separated the query into two queries. It still did not work. I echo'ed the queries to see what was going on.

first query string looked ok. But insert did not go through. I did not see the record inserted into TBL1.

So i think the Last_insert_ID() failed. Then the next two queries had Resource Id #16 in the $itemid place.

Any suggestions.

-DNG

 

[jpadie]

a call to mysql_query returns a result set, not a value
so this

$itemid = mysql_query("SELECT LAST_INSERT_ID()", $this->connection);

won't return what you expect.

try this instead

$itemid = mysql_result(mysql_query("SELECT LAST_INSERT_ID()", $this->connection), 0,0);

 

[DotNetGnat]

jpadie,

Thanks that works great.

I changed the whole function to look like below. I think locking only one table makes sense since last_insert_id is dependent on that. Also i want my function return either true(on success) or false(on failure).

Also I want to these 3 queries to executed when all of them go well. If not rollback and return false. How can I do that.

   function addItems($id, $listid,$itemname, $itemcomments){
       $uldate = date("Y-m-d G.i:s",time());
    mysql_query("LOCK TABLES ".TBL1." WRITE");
    mysql_query("SET AUTOCOMMIT = 0");
    $q1 = "INSERT INTO ".TBL1."(id, listid, activeind, uldate)"
            ."VALUES ($id, $listid, 1, '$uldate')";
$result1  = mysql_query($q1, $this->connection);    
    $itemid = mysql_result(mysql_query("SELECT LAST_INSERT_ID()", $this->connection),0,0);
mysql_query("COMMIT");
    mysql_query("UNLOCK TABLES");
    
$q2 =  "INSERT INTO ".TBL2."(itemid, itemname)"
            ."VALUES ($itemid, '$itemname')" 
$q3 = "INSERT INTO ".TBL3."(itemid, id, comments, commentdate) VALUES ($itemid, $id, '$itemcomments', '$uldate')";     
$result2 = mysql_query($q2, $this->connection);
	$result3 = mysql_query($q3, $this->connection);
 
if($result1 && $result2 && $result3) {
		return true;
		} else
		{
			return false;
			}
}

-DNG

 

[jpadie]

the logic is as you had it except that you should be testing each query result to ensure it is as you suspected.

start transaction
lock tables
run each query 
test whether each query successful.
if yes -> go to next query
if no -> force rollback, return false
if yes -> force commit after last query
return true.

 

[DotNetGnat]

thank you.

have a star.

-DNG