What are some reasons that I might be getting "0 rows affected" on this query?

Question

Backstory:

I'm trying to create a WordPress theme and am working on the setup part of that theme. I've installed WordPress and the MySQL database on which it is running is named testdb. I have a file setup.php which looks like

<?php
error_reporting(-1);
ini_set('display_errors', 'On');

$con = mysqli_connect('localhost', 'testadmin', 'something'); 
if ($con->connect_error)
{
   die("Connection failed: " . $conn->connect_error);
} 
echo "<p>Connected to localhost successfully</p>";

if ($con->select_db("testdb"))
{
    echo ("<p>Connected to database</p>");
}
else
{
    die($con->error);
}

if ($con->query("CREATE TABLE mems (id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR (50), title VARCHAR (20), bio VARCHAR (500), sord INT, picfn VARCHAR (100))") === TRUE)
{
    echo("<p>Created table for team members.</p>");
}
else
{
    die($con->error);;
}

$con->close();

?>

which is failing on table creation part, as the output is

Connected to localhost successfully

Connected to database

CREATE command denied to user 'testadmin'@'localhost' for table 'mems'

I assumed it was a privileges problem, so I went into the MySQL command line and ran

GRANT ALL on testdb.* to 'testadmin'@'localhost';

which outputs

Query OK, 0 rows affected (0.03 sec)

I checked with SHOW DATABASES; that indeed the database testdb is there, and I checked with SELECT user FROM mysql.user; that the user testadmin is there. I know the password is correct.

So what could be the problem?

try `GRANT ALL PRIVILEGES ON testdb.* TO 'testadmin'@'%' WITH GRANT OPTION;` — , Jul 08 '15 at 23:26
@Dagon That is [not the best idea](http://stackoverflow.com/a/5016587/2370483) — Machavity, Jul 08 '15 at 23:29
no its bloody dangerous, but for debugging its usefull starting point — , Jul 08 '15 at 23:29

score 1 · Answer 1 · edited Jul 08 '15 at 23:29

1

If mems table already exists, it will fail.

There's an IF NOT EXISTS clause that will check to see if it exists already.

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    select_statement

[] are optional

edited Jul 08 '15 at 23:29

Gottlieb Notschnabel

9,408
18
74
116

answered Jul 08 '15 at 23:28

Pete LoGiudice

75
11

What are some reasons that I might be getting "0 rows affected" on this query?

1 Answers1