[SOLVED] CREATE TEMPORARY TABLE problem

[mr_badger]

I have this code in which a temporary table needs to be created but I get this error on the page "Access denied for user 'web47-stars'@'%' to database 'web47-stars'

CREATE TEMPORARY TABLE tmp ( topic_id INT(11) NOT NULL DEFAULT 0, postdate datetime NOT NULL default '0000-00-00 00:00:00')"

 

hope someone can help.

 

<?php
require_once 'conn.php';
require_once 'functions.php';
require_once 'http.php';
if (!isset($_GET['f'])) redirect('index.php');
require_once 'header.php';

$forumid = $_GET['f'];
$forum = getForum($forumid);

echo breadcrumb($forumid, "F");
if (isset($_GET['page'])) {
  $page = $_GET['page'];
} else {
  $page = 1;
}
$limit = $admin['pageLimit']['value'];
if ($limit == "") $limit = 25;
$start = ($page - 1) * $admin['pageLimit']['value'];

$sql = "CREATE TEMPORARY TABLE tmp ( " .
       "topic_id INT(11) NOT NULL DEFAULT 0, " .
       "postdate datetime NOT NULL default '0000-00-00 00:00:00')";
mysql_query($sql) 
  or die(mysql_error() . "<br>" . $sql);

$sql = "LOCK TABLES forum_users READ,forum_posts READ";
mysql_query($sql) 
  or die(mysql_error() . "<br>" . $sql);

$sql = "INSERT INTO tmp SELECT topic_id, MAX(date_posted) " .
       "FROM forum_posts " .
       "WHERE forum_id = $forumid " .
       "AND topic_id > 0 " .
       "GROUP BY topic_id";
mysql_query($sql) 
  or die(mysql_error() . "<br>" . $sql);

$sql = "UNLOCK TABLES";
mysql_query($sql) 
  or die(mysql_error()."<br>".$sql);

//die('stop');
$sql = "SELECT SQL_CALC_FOUND_ROWS " .
         "t.id as topic_id, t.subject as t_subject, " .
         "u.name as t_author, count(p.id) as numreplies, " .
         "t.date_posted as t_posted, tmp.postdate as re_posted " .
       "FROM forum_users u " .
       "JOIN forum_posts t " .
       "ON t.author_id = u.id " .
       "LEFT JOIN tmp " .
       "ON t.id = tmp.topic_id " .
       "LEFT JOIN forum_posts p " .
       "ON p.topic_id = t.id " .
       "WHERE t.forum_id = $forumid " .
       "AND t.topic_id = 0 " .
       "GROUP BY t.id " .
       "ORDER BY re_posted DESC " .
       "LIMIT $start, $limit";
$result = mysql_query($sql)
  or die(mysql_error() . "<br>" . $sql);

$numrows = mysql_num_rows($result);
if ($numrows == 0) {
  $msg = "There are currently no posts.  Would you " .
         "like to be the first person to create a thread?";
  $title = "Welcome to " . $forum['name'];
  $dest = "compose.php?forumid=" . $forumid;
  $sev = "Info";
  $message = msgBox($msg,$title,$dest,$sev);
  echo $message;
} else {
  if (isset($_SESSION['user_id'])) {
    echo topicReplyBar(0, $_GET['f'], "right");
  }
  echo "<table class=\"forumtable\" cellspacing=\"0\" ";
  echo "cellpadding=\"2\"><tr>";
  echo "<th class=\"thread\">Thread</th>";
  echo "<th class=\"author\">Author</th>";
  echo "<th class=\"replies\">Replies</th>";
  echo "<th class=\"lastpost\">Last Post</th>";
  echo "</tr>";
  $rowclass = "";
  while ($row = mysql_fetch_array($result)) {
    $rowclass = ($rowclass == "row1"?"row2":"row1");
    if ($row['re_posted'] == "") {
      $lastpost = $row['t_posted'];
    } else {
      $lastpost = $row['re_posted'];
    }
    if ((isset($_SESSION['user_id'])) and
        ($_SESSION['last_login'] < $lastpost)) {
      $newpost = true;
    } else {
      $newpost = false;
    }
    echo "<tr class=\"$rowclass\">";
    echo "<td class=\"thread\">" . ($newpost?NEWPOST." ":"");
    echo "<a href=\"viewtopic.php?t=";
    echo $row['topic_id'] . "\">" . $row['t_subject'] . "</a></td>";
    echo "<td class=\"author\">" . $row['t_author'] . "</td>";
    echo "<td class=\"replies\">" . $row['numreplies'] . "</td>";
    echo "<td class=\"lastpost\">" . $lastpost . "</td>";
    echo "</tr>\n";
  }
  echo "</table>";
  echo paginate($limit);
  echo "<p>" . NEWPOST . " = New Post(s)</p>";
}
$sql = "DROP TABLE tmp";
mysql_query($sql) 
  or die(mysql_error()."<br>".$sql);


?>

[chigley]

Could you post the code of conn.php please?

 

Sounds like your user doesn't have access to the database

[dezkit]

post the conn.php

[mr_badger]

The conn.php works for every other page, but here it is.

 

<?php
define('SQL_HOST',"localhost");
define('SQL_USER',"web47-stars");
define('SQL_PASS',"secret");
define('SQL_DB',"web47-stars");
$conn = mysql_connect(SQL_HOST, SQL_USER, SQL_PASS)
or die('Could not connect to the database; ' . mysql_error());
mysql_select_db(SQL_DB, $conn)
or die('Could not select database; ' . mysql_error());
?>

[BlueSkyIS]

each user has to be given specific privileges to create table. does your user have those privileges?

[mr_badger]

I would have thought they had privilages as this worked perfectly when testing it locally but how do I find out?

[Barand]

SELECT GRANTEE, PRIVILEGE_TYPE FROM `information_schema`.`USER_PRIVILEGES`

WHERE GRANTEE LIKE 'web47-stars%'

[mr_badger]

In my USER PRIVILAGES table in the information scheme I have this:

GRANTEE 'web47-stars'@'%' , TABLE_CATALOG 'NULL', PRIVILEGE_TYPE 'USAGE',  IS_GRANTABLE 'IS_GRANTABLE'.

I dont know if this helps.

[Barand]

You would need a

PRIVILEGE_TYPE = CREATE TEMPORARY TABLES

[mr_badger]

Ok I get what you mean as I can see it on my localhost what it should be but how do I do it through my php admin on my hosting as I cant see anyway of creating this privilage?

 

I don't want to mess things up so would like to know how to do it?

 

Thankyou.

[mr_badger]

can some please help, really need to sort this out.

[Barand]

If you can't see it then maybe you don't have the privilege to grant privileges. It's something you need to take up with your host or Database Administrator.