MySQL Multi-Part Query Error: Solved

[eluser]Eric Cope[/eluser]
I have a query where I copy data out of a innoDB table into a temporary MyISAM table for fulltext index searching. I checked that it works in phpMyAdmin. However when I use

Code:

$this->db->query($sql);

, I get the following error:

Code:

A Database Error Occurred

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; INSERT INTO temp1( qID, quoteID, borrowerName, propertyAddress, userName, unde' at line 1

CREATE TEMPORARY TABLE temp1(qID INT,quoteID TEXT,borrowerName TEXT,propertyAddress TEXT, userName TEXT,underwriterName TEXT,FULLTEXT (quoteID,borrowerName,propertyAddress,userName)) ENGINE = MYISAM; INSERT INTO temp1( qID, quoteID, borrowerName, propertyAddress, userName, underwriterName ) SELECT qID, quoteID, borrowerName, propertyAddress, userName, underwriterName FROM Quotes WHERE companyID = 117; SELECT * FROM temp1 WHERE MATCH (quoteID, borrowerName, propertyAddress, userName) AGAINST ( 'Qubrey' ) LIMIT 50;

Here is my Query.

Code:

CREATE TEMPORARY TABLE temp1(qID INT,quoteID TEXT,borrowerName TEXT,propertyAddress TEXT, userName TEXT,underwriterName TEXT,FULLTEXT (quoteID,borrowerName,propertyAddress,userName)) ENGINE = MYISAM ;
INSERT INTO temp1( qID, quoteID, borrowerName, propertyAddress, userName, underwriterName ) SELECT qID, quoteID, borrowerName, propertyAddress, userName, underwriterName FROM Quotes WHERE companyID = 117;
SELECT * FROM temp1 WHERE MATCH (quoteID, borrowerName, propertyAddress, userName) AGAINST ( 'Qubrey' ) LIMIT 50;

Any ideas why this is failing?

[eluser]Eric Cope[/eluser]
I traced it all the way through the CI libraries. I am using binds, and they are appropriately being used. It seems to be an issue between the connection CI is making and the connection phpMyAdmin is making. Any ideas?

[eluser]rogierb[/eluser]
You cannot use multiple queries using ; in CI
You have to split them up into 3 seperate queries.

[eluser]Eric Cope[/eluser]
rogierb, you beat me to it by 7 minutes. I also need to give the temporary table a unique name and drop it at the end because of persistent connections.

Edit: I also added a CREATE TABLE IF NOT EXISTS in case there is an error dropping the table. I may not drop the table, and reuse the data for serial searches.

Thoughts?

[eluser]rogierb[/eluser]
As far as uniqueness goes, maybe use the sessions_id as part of the name?

Dropping could be done using a post-controller hook or if you want to reuse it, dropping can be done using a cronjob or by extending the auth library you use. If there is no more active session, drop the temp table.

[eluser]Eric Cope[/eluser]
I have settled on using the user's userID appended to the word "temp" as the table name. I also changed the INSERT statement to a REPLACE statement and added DISTINCT to the final SELECT statement. I will do some performance testing later, to see if its faster to drop it every time or wait for the persistent link to die.
Thanks for the tips rogierb. I really appreciate it.