How can I increment a column when inserting values into a database table without using AUTO INCREMENT

Question

I've got a column inside my database "#", I would like to increment that for each insert statement I do.

$sql = "INSERT INTO card (#,creditCard, expdate, cvv)
            VALUES ('','$creditCardStore','2020-01-01','$cvv')";

I can't alter the table to use AUTO INCREMENT.

I found this on the web but i don't know what to put inside the for(..)

$value = 1;
for(...){
 $sql = 'INSERT ...'; 
 $value++; 
}

Your code is vulnerable to SQL injection. You should use prepared statements. — Dharman, Mar 16 '20 at 11:13
With respect, I will add to @Dharman's point.Payment Card Industry security standards are stringent. You open your company up to very high liability by storing card numbers at all. And storing CVVs is prohibited entirely, you may only gather them for the purpose of validating a single transaction. Consider using a payment processor like Stripe or Braintree. Please, please, reconsider your project. Ask me how I know this when you have an hour or two to spare. — O. Jones, Mar 16 '20 at 11:25
Just define it as an autoincrementing integer, and let the engine handle the rest. — Strawberry, Mar 16 '20 at 14:18

Tim Biegeleisen · Answer 1 · 2020-03-16T12:37:06.420

1

Well you could try inserting the previous max value for # in your table, plus one:

INSERT INTO card (num, creditCard, expdate, cvv)
SELECT MAX(num) + 1, ?, '2020-01-01', ?
FROM card;

Here I am using ? placeholders for the credit card store and verification code, under the assumption that you should be using a PHP prepared statement.

edited Mar 16 '20 at 12:37

answered Mar 16 '20 at 11:03

Tim Biegeleisen

502,043
27
286
360

I tried but it showed this error: Error: INSERT INTO card4 (`#`, ccnum, expdate, seccode) SELECT MAX('#') + 1, '************7777', '2024-01-01', '1234' FROM card Truncated incorrect DOUBLE value: '#' – ZCoder Mar 16 '20 at 11:34
Why are you naming a column `#`? – Tim Biegeleisen Mar 16 '20 at 12:36
that's the name of the column that I want to increment – ZCoder Mar 16 '20 at 12:42
Would `MAX(num)` have to be wrapped in an `IFNULL()` in case the table is empty? – El_Vanja Mar 16 '20 at 15:36

score -1 · Answer 2 · answered Mar 16 '20 at 11:56

-1

How about checking what the last # was, set it as an variable and increase another varible and insert that variable?

Something like this (This is not a valid code, just a description from what I meant above) SELECT # FROM card LIMIT 1 ORDER BY # ASC

n1 = # n2 = # + 1

and then in value, insert n2.

answered Mar 16 '20 at 11:56

E A

11
4

If I am understanding you correctly your suggestion is open to race conditions. – Dharman Mar 16 '20 at 13:28
"How about checking what the last # was, set it as an variable and increase another varible and insert that variable?" - Is a really bad description so I made a dummy text – E A Mar 17 '20 at 11:51

score -2 · Answer 3 · answered Mar 16 '20 at 12:30

-2

Try this it will work, simply add plus one in this column.

$sql = "INSERT INTO card set id=id+1,creditCard='$creditCardStore', expdate=''2020- 
    01-01', cvv='$cvv'";

answered Mar 16 '20 at 12:30

Vikesh Singh

1

**Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) – Dharman Mar 16 '20 at 13:27
You're also mixing up `insert` and `update` syntax. – El_Vanja Mar 16 '20 at 22:21

How can I increment a column when inserting values into a database table without using AUTO INCREMENT

3 Answers3