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?

Show us what you did so far! What query did you try? How do you do it on the PHP side? — wolfgangwalther, Nov 02 '14 at 22:26
So far I have nothing proper, just: `$slug = 'r'; $con = @mysqli_connect("localhost","myuser","mypass","mydatabase"); $result = mysqli_query($con," select post_name from wp_posts where post_name like '".$slug."%' and post_type='post' ORDER BY post_name desc");` What I'm looking for is to clean up the output to remove $slug from the beginning of each result. — Breki Tomasson, Nov 02 '14 at 22:27
Well, thats a good start. You could add the code to your question, to make it easier to read and the question more valuable! — wolfgangwalther, Nov 02 '14 at 22:30

score 2 · Accepted Answer · answered Nov 02 '14 at 23:02

2

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.

answered Nov 02 '14 at 23:02

a1111exe

641
4
18

This is a great solution, I never considered stripping the string after the SQL query. The problem, of course, is that the SQL sorting algorithm puts everything in the wrong order when I put things in descending order: 9, 8, 7, 6, 5, 4, 3, 2, 18, 17, 16, 15, 14, 13, 12, 11, 10, 1. Still, I can just rsort the array after it's been created this way. Feels a little bit like I'm cheating, but it absolutely works. – Breki Tomasson Nov 02 '14 at 23:14
@BrekiTomasson Why not? There are plenty of tools in PHP for string manipulations. Also, as I heard (fix me please, if it's not right), there is no regular expression substitution in MySQL. – a1111exe Nov 03 '14 at 17:27

score 0 · Answer 2 · edited May 23 '17 at 12:27

0

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!

edited May 23 '17 at 12:27

Community

1
1

answered Nov 02 '14 at 22:37

wolfgangwalther

1,226
7
15

Yeah, that just gives me "Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTEGER) AS post_number FROM wp_posts WHERE post_name LIKE 'r%' AND post_typ' at line 1". Maybe I'm not putting the length of the string from PHP correctly into the query? – Breki Tomasson Nov 02 '14 at 22:59
Sorry, my bad: Use `UNSIGNED` instead of `INTEGER`. I corrected my answer. – wolfgangwalther Nov 02 '14 at 23:03
Sorry, I used the solution offered by @a1111exe above; stripping sorting in PHP after the SQL query had created the array instead of doing it directly in the SQL query. – Breki Tomasson Nov 03 '14 at 06:26
Great that you got it worked out! I would suggest you accept his answer then, when it worked for you! – wolfgangwalther Nov 03 '14 at 06:28

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

2 Answers2