MySQL/PHP drop table not working

Question

I've got a function that has worked very well for parameterizing MySQL queries, but I need it to perform a DROP TABLE IF EXISTS query and I'm not having any luck. I'm not getting any errors but the table is not being dropped and I'm not sure why.

db_query('DROP TABLE IF EXISTS temp_table', false, false);

Here's the full function in case that helps but, as I mentioned, it works great for insert, update, delete, and selects.

// $query = SQL query, $params = array of parameters (i, s, d, b), $rs = whether or not a resultset is expected, $newid = whether or not to retrieve the new ID value;
// $onedimensionkey = key required to convert array into simple one dimensional array; $admin = if true, use the admin credentials (used only for logins)
function db_query($query, $params, $rs = true, $newid = false, $onedimensionkey = false, $admin = false) {
  (!$admin) ? $link = mysqli_connect(DB_SERVER, DB_USER, DB_PASS, DB_NAME) : $link = mysqli_connect(AUTH_SERVER, AUTH_USER, AUTH_PASS, AUTH_NAME);
  if (!$link) { 
    print 'Error connecting to MySQL Server. Errorcode: ' . mysqli_connect_error(); 
    exit; 
  }

  // Prepare the query and split the parameters array into bound values
  if ($sql_stmt = mysqli_prepare($link, $query)) {
    if ($params) {
      $types = '';
      $new_params = array();
      $params_ref = array();
      // Split the params array into types string and parameters sub-array
      foreach ($params as $param) {
        $types .= $param['type'];
        $new_params[] = $param['value'];
      }
      // Cycle the new parameters array to make it an array by reference
      foreach ($new_params as $key => $parameter) {
        $params_ref[] = &$new_params[$key];
      }
      call_user_func_array('mysqli_stmt_bind_param', array_merge(array($sql_stmt, $types), $params_ref));
    }
  }
  else {
    print 'Error: ' . mysqli_error($link);
    exit();
  }

  // Execute the query
  mysqli_stmt_execute($sql_stmt);

  // Store results
  mysqli_stmt_store_result($sql_stmt);

  // If there are results to retrive, do so
  if ($rs) {
    $results = array();
    $rows = array();
    $row = array();
    stmt_bind_assoc($sql_stmt, $results);
    while (mysqli_stmt_fetch($sql_stmt)) {
      foreach ($results as $key => $value) {
        $row[$key] = $value;
      }
      $rows[] = $row;
    }
    if ($onedimensionkey) {
      $i = 0;
      foreach ($rows as $row) {
        $simplearray[$i] = $row[$onedimensionkey];
        $i++;
      }
      return $simplearray;
    }
    else {
      return $rows;
    }
  }
  // If there are no results but we need the new ID, return it
  elseif ($newid) {
    return mysqli_insert_id($link);
  }

  // Close objects
  mysqli_stmt_close($sql_stmt);
  mysqli_close($link);
}

I might be wrong in this, but from the looks of it, if you run multiple queries you'll end up opening and closing the connection to the database over and over again. Why not just make a simple class for this? — h2ooooooo
– h2ooooooo, Commented Jan 28, 2013 at 22:04
Wouldn't a class still need a separate connection for each query? In truth I don't know much about OOP so I may be completely misguided. — Sean Cunningham
– Sean Cunningham, Commented Jan 28, 2013 at 22:06
Crap...I could have sworn I checked the permissions but that was the problem. Thanks lethal-guitar. — Sean Cunningham
– Sean Cunningham, Commented Jan 28, 2013 at 22:08
It could store the connection as instance variable - the advantage over procedural code would be that you can have several instances, each with its own connection, whereas with a global function, you could only use a global variable, and would be limited to one. — lethal-guitar
– lethal-guitar, Commented Jan 28, 2013 at 22:08

echo_Me · Accepted Answer · 2013-01-28 22:02:29Z

1

try it like that

 db_query('DROP TABLE IF EXISTS `dbName`.`temp_table`') ;

answered Jan 28, 2013 at 22:02

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sean Cunningham Over a year ago

That way I get an access denied error since the www-data account doesn't have rights. Is there a way to use mysql_query with a username/password?

Collectives™ on Stack Overflow

MySQL/PHP drop table not working

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related