0

I tried explaining this but I don't think anyone understood,

I have a lot of titles which are all different, what I'm trying to do, is get rid of every "Lyrics" word in the title, and also, everything that appears before the "-" symbol.

I managed to do the lyrics part with istr_replace

<?php echo str_ireplace('lyrics', '', get_the_title()); ?>

How can I do the second part, what can I apply to the code to make it so?

Example of what I want it to do:

"random title - more random title lyrics" turns to "more random title"

The code applied, would delete the "random title - " on every single title on my website,

2
Contributors
1
Reply
16
Views
3 Years
Discussion Span
Last Post by cereal
0

If these titles are in database table then you could run an update query to remove them, for example if you have a table like this:

CREATE TABLE `lyrics` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8;

insert into lyrics (title) values('random title - more random title lyrics');
insert into lyrics (title) values('wow random title - another more random title lyrics');
insert into lyrics (title) values('not wow random title - wow wow more random title lyrics ex');

Then the select it would look like this:

> select * from lyrics;
+----+------------------------------------------------------------+
| id | title                                                      |
+----+------------------------------------------------------------+
|  1 | random title - more random title lyrics                    |
|  2 | wow random title - another more random title lyrics        |
|  3 | not wow random title - wow wow more random title lyrics ex |
+----+------------------------------------------------------------+

The update query would consists in a subquery that removes the part of string before the - character and the single word lyrics:

UPDATE lyrics, (SELECT sub.id, trim(sub.newtitle_2) AS newtitle FROM (SELECT id, @newtitle_1:= CASE WHEN title REGEXP 'lyrics' THEN REPLACE(title, 'lyrics', '') END, @position:= INSTR(@newtitle_1, '-'), CASE WHEN @position > 0 THEN SUBSTRING(@newtitle_1, @position+1) END AS newtitle_2 FROM lyrics) AS sub) AS sub2 SET lyrics.title = sub2.newtitle WHERE lyrics.id = sub2.id AND sub2.newtitle IS NOT NULL;

The result of this looks like:

select * from lyrics;
+----+-------------------------------+
| id | title                         |
+----+-------------------------------+
|  1 | more random title             |
|  2 | another more random title     |
|  3 | wow wow more random title  ex |
+----+-------------------------------+

As you can see this is almost fine, the last case i.e. the row with id 3 has an extra space because the word ex was originally placed after the word lyrics.

So, mine is just an example, you can try to adjust it according to your table and if you have difficults paste here the structure of the table and some sample data. Also I suggest you to do the tests in a copy of the table, for example:

create table lyrics like your_original_table;
insert into lyrics select * from your_original_table limit 100;

This will create a copy of the first 100 rows. If instead you are interested in the submission step, then paste the form and your insert scripts.

Hope it helps, bye!

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.