auto increment not work in PHP ignore method

Question

I insert data into MySQL using PHP ignore method like this:

if (count($_POST['tags']) > 0)
{
    $tags = explode(',', $_POST['tags']);
    $quoted_tags = array_map(
        function ($x)
        {
            return "'$x'";
        },
        $tags);
    $string = implode(',', $quoted_tags);
    SQL::ACTION("DELETE FROM " . NEWS_TAGS . " WHERE article = ? AND name NOT IN (" . $string . ") ", $id);
    $arr_tag = explode(",", $_POST['tags']);
    foreach($arr_tag as $tag)
    {
        $id = $_GET['id'];
        SQL::ACTION("INSERT IGNORE INTO " . NEWS_TAGS . " (article, name, type) VALUES (?, ?, ?)", $id, $tag, "news");
    }
}

This worked But In MySQL table, between each auto increment id is 4 like This :

enter image description here

How do can I fix this problem?

Looks like some of your inserts are failing. Because of that your auto increment field still increments but a new record isn't inserted. — Stijn Bernards, Jan 28 '15 at 19:42
Why do you believe it's a problem? autoincrement guarantees a `unique` Id value, __not__ `consecutive` Id values — Mark Baker, Jan 28 '15 at 19:45
What is `SQL::ACTION` and why aren't you [properly escaping](http://bobby-tables.com/php) your queries? You cannot put arbitrary user data in a query without creating [severe SQL injection bugs](http://bobby-tables.com/). — tadman, Jan 28 '15 at 19:48
You also can use the MAX() function (if you think is useful in that case), described [here](http://stackoverflow.com/questions/5360117/insert-and-set-value-with-max1-problems) — mend3, Jan 28 '15 at 19:49
@MeMoR I'm not sure what you've created there, that looks extremely non-standard and dangerous. If you've written a wrapper around `mysqli` you've missed out on the most important safety feature: using prepared statements, parameterized queries and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. — tadman, Jan 28 '15 at 20:10
@MeMoR - it's perfectly normal, and generally advisable not to try and change that behaviour, particularly if you do have relationships between that table and other tables based on the id value — Mark Baker, Jan 28 '15 at 20:13
@MarkBaker: i send tags valu`1,2,3` and print `var_dump = $_POST['tags']; ` see : `string(5) "1,2,3"` what's `string(5) `?! — MeMoR, Jan 28 '15 at 22:31
`string(5)` is the datatype and length of the value that you're var_dumping.... it's tells you that it's a string with a length of 5 characters.... and by a remarkable coincidence, the variable that you're var_dumping (`$_POST['tags'];`) is a __string__ containing a value of `1,2,3` which is 5 characters in length — Mark Baker, Jan 28 '15 at 22:47
@tadman It looks like his wrapper does use prepared queries. See the `?` placeholders in both queries. — Barmar, Jan 30 '15 at 22:55
@MeMoR Yes. When you delete rows, it doesn't reuse their IDs, so you get gaps. — Barmar, Jan 30 '15 at 23:10

Robert · Answer 1 · 2015-01-28T19:50:46.767

0

ALTER TABLE tbl AUTO_INCREMENT = 1;

In your php myadmin, run this sql statement where "tbl" is the table name

edited Jan 28 '15 at 19:50

answered Jan 28 '15 at 19:44

Robert

362
3
12

score 0 · Answer 2 · answered Jan 28 '15 at 19:45

0

You'll need to alter your table and set an AUTO_INCREMENT of 1, secondarily you should set your id column as the primary key if you haven't done so already.

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html http://dev.mysql.com/doc/refman/5.5/en/optimizing-primary-keys.html

answered Jan 28 '15 at 19:45

Matt Harper

122
8

Do try and link against the most current version of the documentation whenever possible. I know Oracle's site is a train-wreck and often comes up with the wrong version, but the URL is easily tweaked to correct it. MySQL 5.6 is the current stable version. – tadman Jan 31 '15 at 05:02

auto increment not work in PHP ignore method

2 Answers2

Linked