PHP MySQL запрос с временной таблицей не работает
Я делаю небольшое почтовое приложение через php, javascript, и оно работает по большей части. Я заблудился, когда пытался настроить запрос, чтобы получить письма, которые были удалены и отправлены в корзину. Написанный мною запрос работал нормально, когда я обращался к базе данных напрямую, но не запускался при запуске из PHP. Пожалуйста, помогите мне с этой проблемой, любая помощь будет принята с благодарностью.
Моя структура таблицы электронной почты ниже.
TABLE messages (
'mid' int,
'folderfrom' int, //where message is stored for the user who sent it
'msgfrom' text, //user sending message
'msgto' text, //user getting message
'subject' text,
'msgdate' timestamp,
'msg' text,
'folderto' int, //where message is stored for recieving user.
)
Ниже приведен код, который я использую для построения и выполнения запросов. Я пытался запустить последнее условие в одной строке, но это не сработало.
if ($foldernum == 1) {
$querytorun = "SELECT ms.mid as 'id', ms.folderto as 'folder', ms.msgfrom as 'name', ms.msgto as 'email', ms.subject as 'subject', ms.msgdate as 'date', ms.msg as 'msg' FROM messages as ms WHERE ms.msgto LIKE '{$usercode}' AND ms.folderto = {$foldernum}";
} elseif ($foldernum == 2) {
$querytorun = "SELECT ms.mid as 'id', ms.folderfrom as 'folder', ms.msgfrom as 'name', ms.msgfrom as 'email', ms.subject as 'subject', ms.msgdate as 'date', ms.msg as 'msg' FROM messages as ms WHERE ms.msgfrom LIKE '{$usercode}' AND ms.folderfrom = {$foldernum}";
} else {
$querytorun = "CREATE TEMPORARY TABLE tempfolder('id' int,'folder' int, 'name' text,'email' text,'subject' text,'date' datetime,'msg' text)";
$res = mysql_query($querytorun);
$querytorun = "INSERT INTO tempfolder SELECT mst.mid as 'id', mst.folderto as 'folder', mst.msgfrom as 'name', mst.msgto as 'email', mst.subject as 'subject', mst.msgdate as 'date', mst.msg as 'msg' FROM messages as mst WHERE mst.msgto LIKE '{$usercode}' AND mst.folderto = {$foldernum}";
$res = mysql_query($querytorun);
$querytorun = "INSERT INTO tempfolder SELECT mst.mid as 'id', mst.folderfrom as 'folder', mst.msgto as 'name', mst.msgfrom as 'email', mst.subject as 'subject', mst.msgdate as 'date', mst.msg as 'msg' FROM messages as mst WHERE mst.msgfrom LIKE '{$usercode}' AND mst.folderfrom = {$foldernum}";
$res = mysql_query($querytorun);
$querytorun = "SELECT * FROM tempfolder";
}
$res = mysql_query($querytorun);
while($rs = mysql_fetch_object($res)) {
$arr[] = $rs;
}
echo json_encode($arr);
Решение
Попробуйте использовать обратные кавычки вместо одинарных кавычек.
CREATE TEMPORARY TABLE tempfolder(`id` int,`folder` int, `name` text,`email` text,`subject` text,`date` datetime,`msg` text)
Другие решения
$querytorun="CREATE TEMPORARY TABLE tempfolder( 'mid' int,
'folderfrom' int, //where message is stored for the user who sent it
'msgfrom' text, //user sending message
'msgto' text, //user getting message
'subject' text,
'msgdate' timestamp,
'msg' text
)";
mysql_query($querytorun);
if ($foldernum == 1) {
$querytorun = "SELECT ms.mid as 'id', ms.folderto as 'folder', ms.msgfrom as 'name', ms.msgto as 'email', ms.subject as 'subject', ms.msgdate as 'date', ms.msg as 'msg' FROM messages as ms WHERE ms.msgto LIKE '{$usercode}' AND ms.folderto = {$foldernum}";
} elseif ($foldernum == 2) {
$querytorun = "SELECT ms.mid as 'id', ms.folderfrom as 'folder', ms.msgfrom as 'name', ms.msgfrom as 'email', ms.subject as 'subject', ms.msgdate as 'date', ms.msg as 'msg' FROM messages as ms WHERE ms.msgfrom LIKE '{$usercode}' AND ms.folderfrom = {$foldernum}";
} else {
$querytorun = "CREATE TEMPORARY TABLE tempfolder('id' int,'folder' int, 'name' text,'email' text,'subject' text,'date' datetime,'msg' text)";
$res = mysql_query($querytorun);
$querytorun = "INSERT INTO tempfolder SELECT mst.mid as 'id', mst.folderto as 'folder', mst.msgfrom as 'name', mst.msgto as 'email', mst.subject as 'subject', mst.msgdate as 'date', mst.msg as 'msg' FROM messages as mst WHERE mst.msgto LIKE '{$usercode}' AND mst.folderto = {$foldernum}";
$res = mysql_query($querytorun);
$querytorun = "INSERT INTO tempfolder SELECT mst.mid as 'id', mst.folderfrom as 'folder', mst.msgto as 'name', mst.msgfrom as 'email', mst.subject as 'subject', mst.msgdate as 'date', mst.msg as 'msg' FROM messages as mst WHERE mst.msgfrom LIKE '{$usercode}' AND mst.folderfrom = {$foldernum}";
$res = mysql_query($querytorun);
$querytorun = "SELECT * FROM tempfolder";
}
$res = mysql_query($querytorun);
while($rs = mysql_fetch_object($res)) {
$arr[] = $rs;
}
echo json_encode($arr);
Таким образом, я понял это сам, я закончил настройку как один запрос без временной таблицы. Вместо этого я сделал объединение всех с двумя запросами, которые работали.
$querytorun = "SELECT mst.mid as 'id', mst.folderto as 'folder', mst.msgfrom as 'namefrom', mst.msgto as 'nametoo', mst.subject as 'subject', mst.msgdate as 'date', mst.msg as 'msg' FROM messages as mst WHERE mst.msgto LIKE '{$usercode}' AND mst.folderto = {$foldernum} UNION ALL SELECT mst.mid as 'id', mst.folderfrom as 'folder', mst.msgto as 'nametoo', mst.msgfrom as 'namefrom', mst.subject as 'subject', mst.msgdate as 'date', mst.msg as 'msg' FROM messages as mst WHERE mst.msgfrom LIKE '{$usercode}' AND mst.folderfrom = {$foldernum}";