CODEWITHSUNDEEP
phpmysqlmysqlilibmysql
Drfrink
Drfrink

Reputation: 404

PHP mysql query with temp table not working

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

Upvotes: 0

Views: 506

Answers (3)

Drfrink
Drfrink

Reputation: 404

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

Upvotes: 0

user1364100
user1364100

Reputation: 

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

Upvotes: 0

jay temp
jay temp

Reputation: 1205

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)

Upvotes: 1

Related Questions