How to query by last two characters of data with PDO

Beginners - Learning PHP
#1

Hello,

Can the last two characters “-D” and “-M” be arrayed excluding data?

$kod = $db->prepare("SELECT kod FROM kodlar");
$kod->execute();
$kod_arr = $kod->fetchAll(PDO::FETCH_COLUMN);

[0] => UCE-CT213
[1] => UCE-CT213-D
[2] => UCE-CT213-M
[3] => UCE-CT220L
[4] => UCE-CT220L-D
[5] => UCE-CT220L-M
[6] => UCE-CT220S
[7] => UCE-CT220S-D
[8] => UCE-CT220S-M
[9] => UCE-CT221L
[10] => UCE-CT221L-D
[11] => UCE-CT221L-M
[12] => UCE-DS0290
[13] => UCE-DS0290-D
[14] => UCE-DS0290-M
[15] => UCE-DS0291
[16] => UCE-DS0291-D
[17] => UCE-DS0291-M
[18] => UCE-DSO212
[19] => UCE-DSO212-D
[20] => UCE-DSO212-M
[21] => UCE-DSO275
[22] => UCE-DSO275-D
[23] => UCE-DSO275-M
#2

If you’re using MySQL 8.0 or later, you can use REGEXP_REPLACE:

$kod = $db->prepare("SELECT REGEXP_REPLACE(kod, '-D|-M$', '') as trimmed FROM kodlar");

'-D|-M$' is a regular expression. | means “either of these patterns” and $ means “at the end of the string”.

#3

Thank you for your help
But “-D and -M” removes the suffixes and adds them to the string.
I want to exclude completely so that there is only one of each “kod”

or do i need to use “array_unique()”

#4 
$kod = $db->prepare("SELECT DISTINCT REGEXP_REPLACE(kod, '-D|-M$', '') as trimmed FROM kodlar");

DISTINCT should do it.

1 Like
#5

Thank you very much it worked exactly as I wanted

#6

I’ve realized now
It also removed the similar character in the product code
UCE -D S0290
UCE -D S0290-D
The product code gives “UCESO212” like this, this is a non-existent code.
Should not add last two-character codes to array
How should I fix this problem?

#7

Oops. Change the regular expression to '-D$|-M$'.

1 Like
#8

The problem is fixed.
Thank you so much

Sponsor our Newsletter | Privacy Policy | Terms of Service