query to replace text with sequential numbers

Question

I have an images database, the default sort is the "name" column, and the "description" column content is the exact same in all files in the table. what I need is to replace certain text in the "description" with numbers respectively.

Example of the "description" entry to all items:

"Photo number # more text text"

The query should replace the "#" symbol with sequential numbers, ordered by 'name' column. Where the first row should be Photo number 1, and second Photo number 2.. etc.

I tried some queries with row_number (in phpmyadmin), but I am not sure if it is the right way to do that or what.. as they didn't work.

Server version: 5.7.24-0ubuntu0.16.04.1 - (Ubuntu)

Answer 1

Use a variable to get an auto-incremented value and update description using this value. Or use Rick James's answer and add a sequenced column to your table as a number for each picture.

create table t (name varchar(10), description varchar(100));
insert into t values ('b', '"Photo number # - Gallery name"');
insert into t values ('c', '"Photo number # - Gallery name"');
insert into t values ('a', '"Photo number # - Gallery name"');
insert into t values ('x', '"Photo number # - Gallery name"');

update t
join (select 
 name, @num := @num + 1 as num
 from
 t, (select @num := 0) t
 order by
 name) t2
on t2.name = t.name
set description = replace(description, '#', cast(num as varchar(10)));

select * from t order by name;

name | description 
:--- | :------------------------------
a | "Photo number 1 - Gallery name"
b | "Photo number 2 - Gallery name"
c | "Photo number 3 - Gallery name"
x | "Photo number 4 - Gallery name"

db<>fiddle here

Answer 2

Plan A:

CREATE TABLE foo (
 photo_num INT AUTO_INCREMENT,
 gallery_name VARCHAR(155),
 PRIMARY KEY(photo_num)
);

Then

INSERT INTO foo (gallery_name)
 SELECT gallery_name FROM elsewhere
 ORDER BY gallery_name;