0

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)

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

| improve this answer | |
0

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;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.