CODEWITHSUNDEEP
phpmysqlsql
Keyfer Mathewson
Keyfer Mathewson

Reputation: 1075

Deleting all rows from table, and resetting auto incrementation

code is below. Seems like when I run this file, it doesn't do anything.

header('Content-Type: application/json');

$DB = new mysqli("localhost", "root", "root", "friendslist");
if($DB->connect_errno) {
    die("Connect failed: ". $DB->connect_error);
}

$DB->query("DELETE * FROM users");
$DB->query("ALTER TABLE users AUTO_INCREMENT = 1");

$DB->close();

    echo json_encode(Array('status' => 'ok'));

Upvotes: 0

Views: 270

Answers (3)

zakaria
zakaria

Reputation: 426

First of all your delete systex is wrong which is generating error for your code.

Secondly, you should use truncate instead of delete as they work in two different approach.

TRUNCATE TABLE tablename; actually drops and recreate the table so its fast and it reset the auto increment seed value to 1.

Whereas, DELETE FROM tablename; deletes all the data in the table. It is not as quick as using the "TRUNCATE TABLE" method and it left auto increment seed value as it was before.

So you can use the following:

if($DB->query("TRUNCATE TABLE users"))
    echo json_encode(Array('status' => 'ok'));
else  echo json_encode(Array('status' => $DB->error));

You will get the problem from the response.

Upvotes: 2

Barmar
Barmar

Reputation: 780869

Your query has incorrect syntax. If you read the documentation, you'll see that the valid syntaxes of DELETE are either:

DELETE FROM users

or:

DELETE users.* FROM users

where .* is optional in the second version. But DELETE * is not valid.

You could also just use TRUNCATE tablename to delete everything from the table and reset the auto increment ID in one step.

You should also check whether queries succeed:

$DB->query("DELETE * FROM users") or die ($DB->error);

would have told you that there was a syntax error. Then I'm sure you would have checked the documentation to see what the correct syntax is, and fixed it right away, rather than going the slow route of posting a question to SO.

Upvotes: 4

ARIF MAHMUD RANA
ARIF MAHMUD RANA

Reputation: 5166

I think you have to remove * from your delete query

header('Content-Type: application/json');

$DB = new mysqli("localhost", "root", "root", "friendslist");
if($DB->connect_errno) {
    die("Connect failed: ". $DB->connect_error);
}

$DB->query("DELETE FROM users");// Remove * from here
$DB->query("ALTER TABLE users AUTO_INCREMENT = 1");

$DB->close();

    echo json_encode(Array('status' => 'ok'));

Upvotes: 0

Related Questions