current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them, it only takes a minute:
up vote 0 down vote favorite

I have an insert command based on the following: 

$baseINS = "INSERT INTO table2 (Points_ID, StaticCode) VALUES ";
$arrayINS = explode(", ", $arraystring);
foreach ($arrayINS as &$array1INS) {
    $array1INS = "('" . $array1INS . "', '123456')";
}
$arrayvaluesINS = implode(', ', $arrayINS);
$insertSQL2 = $baseINS . $arrayvaluesINS;

The $insertSQL2 is queried from the following transaction:

$insertSQL = "BEGIN";
mysql_query($insertSQL) or die (mysql_error());
$insertSQL = $insertSQL1;
mysql_query($insertSQL) or die (mysql_error());
$insertSQL = $insertSQL2;
mysql_query($insertSQL) or die (mysql_error());
$insertSQL = "COMMIT";
mysql_query($insertSQL) or die (mysql_error());

(where $insertSQL1 is another SQL insert, to another table)

Table structure as follows:

table2 ( ID int(50) NOT NULL AUTO_INCREMENT,
StaticCode varchar(100) NOT NULL, Points_ID varchar(50) NOT NULL, PRIMARY KEY (ID), UNIQUE KEY ID (ID) ) ENGINE=InnoDB

Now, let's say for example that $insertSQL2 echos as INSERT INTO table2 (Points_ID, StaticCode) VALUES ('24859', '123456'), ('24649', '123456'), ('25166', '123456')

I would expect three rows to appear in the table2 ...but this is what happens:

  • Both the insert commands ($insertSQL1 and $insertSQL2) do, in fact, insert data.
  • It's just that, while $insertSQL1 inserts all of the data from all of the form to the required fields in table1, the $insertSQL2 (which is inserting the array) doesn't - it just inserts the static code, once, and leaves the Points_ID blank.

I have tested the INSERT statement by just copying the echoed command into PHPMySQL, and it does exactly what it is supposed to, so I know that my version of MySQL can handle it.

Any ideas why?

share|improve this question
    
You need to show your table structure. e.g. is Points_ID a foreign key field? do the matching foreign records exist? Your code is also vulnerable to SQL injection, you're using a very dangerous foreach() construct, and your actual query calls are needlessly verbose. e.g. clean up your code and maybe things will start working. – Marc B Jan 23 '13 at 15:18
    
@MarcB - this is just a proof of concept at present (hence messy code). Table structure: ` table2 ( ID int(50) NOT NULL AUTO_INCREMENT, StaticCode varchar(100) NOT NULL, Points_ID varchar(50) NOT NULL, PRIMARY KEY (ID), UNIQUE KEY ID (ID) ) ENGINE=InnoDB ` – user1259798 Jan 23 '13 at 15:24

1 Answer 1

up vote 0 down vote

From your code it looks your are using mysql extension. You should use mysqli or pdo_mysql. I believe transactions don't work on mysql.

mysqli would look almost same, but for transaction use mysqli features

/* set autocommit to off */
$mysqli->autocommit(FALSE);

/* Insert some values */
$mysqli->query($insertSQL);

/* commit transaction */
$mysqli->commit();
share|improve this answer
    
Would this explain why it is performing the most part of the INSERT INTO command, but not all? Surely it would either work or not work?What I'm getting is some information being inserted, and some information not getting inserted. (I agree that I will probably upgrade it to mysqli throughout, but just wanted to get my head around the actual problem, too!) :-) – user1259798 Jan 23 '13 at 16:20
    
What I was saying is that "BEGIN" and "COMMIT" in this case have no effect, so yes, some information can be inserted, but not all of them, if there is any problem. – Zdenek Machek Jan 23 '13 at 21:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.