När jag kör frågan nedan i phpmyadmin tar den 0.0009 sek att köra.
CREATE TEMPORARY TABLE `test` (
`threads_id` INT( 11 ) NOT NULL ,
`last_modified` DATE NOT NULL ,
`threads_date` DATETIME NOT NULL ,
`threads_last_modified_by_user_id` INT( 11 ) ,
`threads_modified_by_alias` VARCHAR( 100 ) ,
`threads_head` VARCHAR( 255 ) NOT NULL ,
`threads_body` TEXT NOT NULL ,
`threads_abuse` INT( 1 ) NOT NULL ,
`threads_ip` VARCHAR( 100 ) ,
`user_id` INT( 11 ) NOT NULL ,
`user_alias` VARCHAR( 100 ) NOT NULL ,
`user_status` INT( 1 ) NOT NULL
) TYPE = MYISAM ;
INSERT INTO test(
SELECT threads.id AS threads_id, threads.last_modified, threads.date, threads.modified_by, threads.modified_by, threads.head, threads.body, threads.abuse, threads_log.ip, user.ID AS user_id, user.Alias, user.Status
FROM threads, user, threads_log
WHERE threads.msg_owner_id = '26608'
AND threads.user_owner_id = user.ID
AND threads_log.owner_id = threads.id ) ;
UPDATE test,
user SET test.threads_modified_by_alias = user.Alias WHERE test.threads_last_modified_by_user_id = user.ID;
SELECT *
FROM test;
Sen när jag kör den från min websida tar den 0.365 sek att köra (utan denna fråga tar sida 0.069 sek).
$SQL = "CREATE temporary TABLE `test` (
`threads_id` INT( 11 ) NOT NULL ,
`last_modified` DATE NOT NULL,
`threads_date` DATETIME NOT NULL ,
`threads_last_modified_by_user_id` INT( 11 ) ,
`threads_modified_by_alias` VARCHAR( 100 ) ,
`threads_head` VARCHAR( 255 ) NOT NULL ,
`threads_body` TEXT NOT NULL ,
`threads_abuse` INT( 1 ) NOT NULL ,
`threads_ip` VARCHAR(100) ,
`user_id` INT( 11 ) NOT NULL ,
`user_alias` VARCHAR( 100 ) NOT NULL ,
`user_status` INT( 1 ) NOT NULL) TYPE = MYISAM;\ SELECT * FROM test;";
$SQL = "INSERT INTO test(SELECT threads.id AS threads_id, threads.last_modified, threads.date, threads.modified_by, threads.modified_by, threads.head, threads.body, threads.abuse, threads_log.ip, user.ID AS user_id, user.Alias, user.Status FROM threads, user, threads_log WHERE threads.msg_owner_id = '26608' AND threads.user_owner_id = user.ID AND threads_log.owner_id = threads.id);";
mysql_db_query(DB_FORUM,"$SQL",DB_CONN) or die (mysql_error());
$SQL = "UPDATE test,
user SET test.threads_modified_by_alias = user.Alias WHERE test.threads_last_modified_by_user_id = user.ID;";
mysql_db_query(DB_FORUM,"$SQL",DB_CONN) or die (mysql_error());
$SQL = "SELECT * FROM test;";
$res = mysql_db_query(DB_FORUM,"$SQL",DB_CONN) or die (mysql_error());
När jag sedan försöker köra koden som phpmyadmin har skapat får jag bara fel hela tiden "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 test(SELECT threads.id AS threads_id, threads.last_modified, threa' at line 1"
$SQL = 'CREATE temporary TABLE `test` ('
. ' `threads_id` INT( 11 ) NOT NULL ,'
. ' `last_modified` DATE NOT NULL,'
. ' `threads_date` DATETIME NOT NULL ,'
. ' `threads_last_modified_by_user_id` INT( 11 ) ,'
. ' `threads_modified_by_alias` VARCHAR( 100 ) ,'
. ' `threads_head` VARCHAR( 255 ) NOT NULL ,'
. ' `threads_body` TEXT NOT NULL ,'
. ' `threads_abuse` INT( 1 ) NOT NULL ,'
. ' `threads_ip` VARCHAR(100) ,'
. ' `user_id` INT( 11 ) NOT NULL ,'
. ' `user_alias` VARCHAR( 100 ) NOT NULL ,'
. ' `user_status` INT( 1 ) NOT NULL '
. ' ) TYPE = MYISAM ;'
. ' INSERT INTO test(SELECT threads.id AS threads_id, threads.last_modified, threads.date, threads.modified_by, threads.modified_by, threads.head, threads.body, threads.abuse, threads_log.ip, user.ID AS user_id, user.Alias, user.Status FROM threads, user, threads_log WHERE threads.msg_owner_id = \'26608\' AND threads.user_owner_id = user.ID AND threads_log.owner_id = threads.id);'
. ' UPDATE test,'
. ' user SET test.threads_modified_by_alias = user.Alias WHERE test.threads_last_modified_by_user_id = user.ID;'
. ' SELECT * FROM test;';
Min fråga är ju varför det är sån skillnad på att köra den i phpmyadmin och min websida.