Using PHP to select from a SQL table and remove a specific prefix from the results
I have a WordPress installation where posts are "r1", "r2", "r3", "bah1", "bah2", "bah3", etc. The initial is the slug category, and the number is the number of the post in that category.
What I'm trying to create now: save the slug post (say 'r' or 'bah' for a PHP string, then write a PHP script that picks post_name from wp_posts where only numbers are output, sorted in descending order to get the highest value first messages.
What I think is basically somehow use the string length in PHP to get the number of characters to subtract from the very beginning of the results, but I am having trouble fixing it, especially when it comes to injecting a PHP string into SQL lookup ...
So far, I only have the code to select all posts containing the correct slug, but I was 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 expected to be
r9 r8 r7 R63 R62 R61 r60 r6 R59 r58 r57 R56 r55 R54
because SQL does not sort "correctly" and sees r6 as less than r61. This is part of the reason why I want to remove the 'r' from the results so that I can sort them better.
Any thoughts?
source to share
You have a simple pattern here: some non-digit characters followed by some digits. It is very easy to parse with a regex like / \ D + (\ d +) /, removing all previous non-digit characters, replacing the backreference and PHP preg_replace . 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 can do it in your while loop like this:
while ($row = mysqli_fetch_array($result)) {
$stripped[] = preg_replace('/\D+(\d+)/', '$1', $row['post_name']);
}
Then you can use PHP's sort function to sort the resulting array.
source to share
You need the mysql function SUBSTRING
( MySQL manual ). Convert the result of this to an integer using CAST
( MySQL manual ) so that you can sort it later.
You SQL query might 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 your string length.
You should also consider using prepared statements with mysqli. See How can I prevent SQL Injection in PHP? for more information on this!
source to share