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

2 Views

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.

Read More

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?

Post Views: 2

Related posts

Leave a Reply

2 comments

  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.

  2. What you need is the mysql function SUBSTRING (MySQL manual). Convert the result of that to integer using CAST (MySQL manual) to be able to sort on it later.

    You SQL query could look like this:

    SELECT post_name, CAST(SUBSTRING(post_name FROM x+1) AS UNSIGNED) AS post_number
FROM wp_posts
WHERE
  post_name LIKE '".$slug."%' AND
  post_type='post'
ORDER BY post_number DESC

    Replace x with the length of your string.

    You should also think about using prepared statements with mysqli. See How can I prevent SQL injection in PHP? for more info on this!