CODEWITHSUNDEEP
mysqlsqlselect
Muthu Ganapathy Nathan
Muthu Ganapathy Nathan

Reputation: 3307

Using Tempropary table is not working - MYSQL

My aim is to optimize the select query which sorts by relevance of the word,

And instead of searching the, whole database twice, I here made this query.

$query = "SELECT * INTO #TEMP_TABLE " .
                     "FROM cron_video ".
                     "where id LIKE '%" . $searchString . "%' " .
                     "union all " .
                     "SELECT * " .
                     "FROM #TEMP_TABLE ".
                     "WHERE id LIKE '". $searchString . "%' " ;

But it is giving error as

SQL Error 1: 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 '' at line 1

Upvotes: 0

Views: 60

Answers (2)

xkeshav
xkeshav

Reputation: 54016

Temporary TABLE created on runtime...so write

CREATE TEMPORARY TABLE ...syntax and INSERT INTO consequently...

from MySQL manual

You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different

for eg.

CREATE TEMPORARY TABLE tmp_film (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50),
`last_updated_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY(`id`)
)ENGINE=HEAP DEFAULT CHARSET=latin1;

INSERT INTO tmp_film(`name`) VALUES ('Keshav');

SELECT * FROM tmp_film;

Upvotes: 0

Joe
Joe

Reputation: 931

Use CREATE TEMPORARY TABLE before running your query (defining a structure which fits your needs).

Also, only name your tables with a-zA-Z_- for the sake of consistency.

http://dev.mysql.com/doc/refman/5.1/en/create-table.html

Also, please provide MySQL your query as a whole, including the line-feeds, it'll be easier to debug in the future, i.e. "error on line 6", instead of "error on line 1" (since there will always only be one line fed to mysql.

Upvotes: 1

Related Questions