MySQL Multi-Part Query Error: Solved - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20) +--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23) +--- Thread: MySQL Multi-Part Query Error: Solved (/showthread.php?tid=26747) |
MySQL Multi-Part Query Error: Solved - El Forum - 01-22-2010 [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); Code: A Database Error Occurred 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 ; Any ideas why this is failing? MySQL Multi-Part Query Error: Solved - El Forum - 01-22-2010 [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? MySQL Multi-Part Query Error: Solved - El Forum - 01-22-2010 [eluser]rogierb[/eluser] You cannot use multiple queries using ; in CI You have to split them up into 3 seperate queries. MySQL Multi-Part Query Error: Solved - El Forum - 01-22-2010 [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? MySQL Multi-Part Query Error: Solved - El Forum - 01-22-2010 [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. MySQL Multi-Part Query Error: Solved - El Forum - 01-22-2010 [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. |