-1

I am using the following code to know where to put my next post:

// Create connection
$link = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

// Check connection
if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}

$sql = "SELECT * FROM `wp_posts` WHERE ID = (SELECT max(ID) FROM `wp_posts`)";
$res = $link->query($sql);
while( $rs = $res->fetch_object() ) {
            echo 'Last ID is: '. $rs->ID .' <br />';
            $lastID = $rs->ID;
        }

// Close connection
mysqli_close($link);

$ID_of_Next_Post = $lastID + 1;
echo 'Next post will go to ID: '. $ID_of_Next_Post .' <br />';

The issue I'm facing is that if I make a post that goes into, say, ID = 100 and then I delete that post, the next time this code is called $lastID will still equal 100 but the next post will go into 102 (because 101 was taken, even though it has been deleted and is not there anymore).

So how can I change my SQL query to find the NEXT AVAILABLE id so I know where the next post will go? The ID row is set to auto-increment so it's tempting to not set the ID of the post but I need to know what the ID is so I can set interdependencies in other tables of the database.

Machavity
  • 30,841
  • 27
  • 92
  • 100
marcnyc
  • 585
  • 1
  • 4
  • 17
  • 2
    Why do you want to do that? Check the duplicate question chain on https://stackoverflow.com/questions/12969161/make-auto-increment-fill-previously-deleted-number. And what do you mean by "interdependencies"? – Progman Jun 03 '20 at 19:53
  • Maybe you have a great reason for this, but it might be better to just not worry about gaps. Insert the record and see what you got back. There's many reasons beyond a record being deleted that could cause a skip. – Evert Jun 03 '20 at 22:09
  • 1
    I would advise against this and use autoicrement that what its there for. You wont be able to control gaps the keys are to be designed to be handled by the db and your already using mysql and wordpress so would be easy to do that. – c-sharp-and-swiftui-devni Jun 03 '20 at 22:10
  • I am sorry but maybe i didn't explain correctly @Evert - I have NO problem with the gaps, I simply wanted to know how to find out what will be the next ID that would be assigned before it is assigned. In other words, if I add 100, then delete 100, how do I know that the next ID will be 101 and not 100? with my code I still get 100 (after 100 is deleted) – marcnyc Jun 03 '20 at 23:17
  • 2
    The issue is that you can't really know until it's inserted. Even if you use @Tech Kid's answer, there's still no guarantee you'll get that number next. The best practice here is to insert and let mysql tell you what it was. – Evert Jun 03 '20 at 23:29
  • Thank you @Evert - i will rethink some things and do it that way – marcnyc Jun 04 '20 at 11:43

2 Answers2

2

First of all it should be stated, that it's better to just let the auto increment do its job, and leave the gaps there. But, if you insist on filling up every possible gap in the id-s, then this is how you could approach it:

SELECT (CASE WHEN `wpm`.`min_id` = 1 THEN MIN(`u`.`id` + 1) ELSE 1 END) as next_id
FROM `wp_posts` wp
LEFT JOIN `wp_posts` wp2 ON `wp2`.`id` = `wp`.`id`+1
LEFT JOIN (SELECT MIN(id) as `min_id` FROM `wp_posts`) as wpm ON 1
WHERE `wp2`.`id` IS NULL

This will pair up id-s with their subsequent id-s, and return the minimum of those that do not have a subsequent id on the table. Because of the nature of this, 1 being a missing id is a special case, so we have to account for that as well.

MrLumie
  • 247
  • 2
  • 10
0

Maybe you can use this

select auto_increment from information_schema.TABLES 
where TABLE_NAME ='tablename' and TABLE_SCHEMA='database_name';

and if you don't want to use information_schema then you can use

SHOW TABLE STATUS LIKE 'table_name'
Dharman
  • 30,962
  • 25
  • 85
  • 135
Tech Kid
  • 577
  • 2
  • 7
  • 19