PHP mysql query with temp table not working

Question

I'm making a small email app via php, javascript and have it working for the most part. Where I'm getting lost is when trying to set up the query to get the emails that was deleted and sent to trash. The query I had written seemed to work fine when I queried the database directly, but fails when run from PHP. Please help me out with this frustrating problem, any help would be greatly appreciated.

My email table structure is below.

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.
)

Below is the code I'm using for building and executing the queries. I've tried running the last else condition in one line but it didn't work.

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);

try using [`mysql_error()`](http://php.net/manual/en/function.mysql-error.php); also, why aren't you using mysqli? Better yet, why aren't using PDO? — Nicholas Summers, Oct 22 '14 at 00:51
I'm extremely new to PHP, this is my first project really. More used to nodejs. I'm looking into PDO right now. — Drfrink, Oct 22 '14 at 01:37
In that case here are a few tips: use PHP 5.4 or later (or if you must, 5.3.7+); don't store passwords, even if you encrypt them (use hashing); use XAMPP, Sublime Text, and Filezilla; Learn and use Git/Github (very important); before you make something, check if some else has already made it; Apache or Nginx doesn't matter; OOP is important; Use plenty of code comments!; PSR-4 FTW; read the php.net comments when looking up functions. — Nicholas Summers, Oct 22 '14 at 01:55

score 1 · Answer 1 · answered Oct 22 '14 at 00:57

1

Try using backticks instead of single quote.

CREATE TEMPORARY TABLE tempfolder(`id` int,`folder` int, `name` text,`email` text,`subject` text,`date` datetime,`msg` text)

answered Oct 22 '14 at 00:57

jay temp

1,207
12
11

Already tried that, I switched to single quote since it didn't work. – Drfrink Oct 22 '14 at 01:35

score 0 · Answer 2 · answered Oct 22 '14 at 02:43

   $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);

no it doesn't....just tried it to check. all you did was add my create table at the top... — Drfrink, Oct 26 '14 at 08:24

score 0 · Accepted Answer · answered Oct 26 '14 at 09:28

So I figured it out myself I ended up setting it up as one query without a temp table. Instead I did a union all with two queries which worked.

$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}";

PHP mysql query with temp table not working

3 Answers3