Using PHP to select from an SQL table and removing a specific prefix from the results

Question

I've got a wordpress installation where the post slugs are 'r1', 'r2', 'r3', 'bah1', 'bah2', 'bah3', and so on. The initial letter is the category slug and the number is the number of the post in that category.

What I'm trying to create now is: save a post slug (say 'r' or 'bah' to a PHP string, then write a PHP script that selects post_name from wp_posts where only the numbers are output, sorted in descending order to get the highest value of posts first.

What I'm thinking is basically to somehow use the string length in PHP to get the number of characters to deduct from the beginning of the results, but I'm having problems getting it done right, especially when it comes to introducing the PHP string into the SQL search.

So far I only have the code to select all the posts that contain the right slug, but I've been unable to remove the prefix. Here's what I have:

$slug = 'r';
$con = @mysqli_connect("localhost","user","password","database");
$result = mysqli_query($con,"
select post_name from wp_posts               
    where post_name like '".$slug."%'
    and post_type='post'
order by post_name desc");

while ($row = mysqli_fetch_array($result)) {
    echo $row['post_name'] . ' ';
}

At the moment, the output is, predictably:

r9 r8 r7 r63 r62 r61 r60 r6 r59 r58 r57 r56 r55 r54

since SQL doesn't sort "correctly" and sees r6 as a smaller number than r61. That's part of the reason I want to strip 'r' from the results so I can sort them better.

Any thoughts?

Answer 1

You have a simple pattern here: some non-digit chars following by some digits. It's very easy to parse with regular expression like /\D+(\d+)/, removing all preceding non-digit chars by substituting with a backreference and PHP preg_replace function. Example:

<?php
$num = preg_replace('/\D+(\d+)/', '$1', 'r12');
$num1 = preg_replace('/\D+(\d+)/', '$1', 'bah4');
$num2 = preg_replace('/\D+(\d+)/', '$1', 'bah38');


echo $num . "\n";
echo $num1 . "\n";
echo $num2 . "\n";

So you may do this in your while loop like this:

while ($row = mysqli_fetch_array($result)) {
    $stripped[] = preg_replace('/\D+(\d+)/', '$1', $row['post_name']);
}

Then you may use PHP sort function to sort the resulting array.