I have a MySQL database with a table called chapters
.
I have a column called chapter_number
which is an Integer data type and is NOT auto increment as I have another field which uniquely identifies the row.
Upon insertion to this table I would like the value of the chapter_number
column to be the previous row's chapter_number
incremented by 1 (+1).
The purpose of this is to ensure that every time a chapter is added, the right ordering takes place.
How could I achieve this in PHP?
Here is an example of what I mean?
Table structure:
$stmt = $db->prepare('INSERT INTO chapters VALUES (?,?,?,?)');
$stmt->execute(array('','title', '2015-04-03', 'chapter 1 body'));
What would I put for the first parameter ^
SELECT max(chapter_number) + 1 as total FROM table;
This would also allow you to later on have chapters assigned to multiple books by symply adding a where clause
The following example is how this syntax would look
insert into table_one (greeting_column, name_column)
SELECT
'hello',
(SELECT max(chapter_number) + 1 as total FROM table)